Mysql常见问题记录

1、连表查询时，提示Illegal mix of collations (utf8mb4_

ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

问题原因:两张表的排序规则不一致。执行的SQL如下：

select a.id from A as a left join B as b on a.id=b.id;

使用：show table status like '表名'; 查看。注意''不能忘记
A表：

B表：

解决办法：语句后面添加COLLATE utf8mb4_unicode_ci;

SELECT a.id FROM A AS a 
LEFT JOIN B as b on a.id=b.id COLLATE utf8mb4_unicode_ci;