mysql 中有两个表:
tableA表:id字段类型为varchar
tableB表:id字段为int
执行以下语句:
select a.*,b.* from tableA a left tableB b on a.id=b.id
会发现查出来的数据 错乱,没有按照a.id=b.id 进行拼接。
解决办法有如下三种,即对字段类型进行转换后做on:
select a.*,b.* from tableA a left join tableB b on a.id1=CONCAT(b.id,'')
select a.*,b.* from tableA a left join tableB b on a.id1 =CAST(b.id as char)
select a.*,b.* from dir_test a left join dir_tag b on a.id1 =convert(b.id,char)
执行第一条语句时,发现没问题。
执行第二条,第三条时,报错:
Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='
提示on的两个字段编码不同,因为int类型字段没有编码,执行cast或者convert后会转换为mysql的默认编码,默认编码可以通过如下查询:
show variables where Variable_name like 'collation%';
如果在不修改默认编码的情况下进行查询时,可以通过如下:
select a.*,b.* from tableA a left join tableB b on a.id1 COLLATE utf8mb4_0900_ai_ci =convert(b.id,char)
select a.*,b.* from tableA a left join tableB b on a.id1 =convert(b.id,char) COLLATE utf8mb4_general_ci
select a.*,b.* from tableA a left join tableB b on a.id1 =CAST(b.id as char) COLLATE utf8mb4_general_ci
select a.*,b.* from tableA a left join tableB b on a.id1 COLLATE utf8mb4_general_ci =CAST(b.id as char)