Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
原因
两个比较的字段排序规则或者字符编码不一致,不能进行比较
解决办法
1.调用一个SQL脚本,批量生成SQL执行语句,然后再执行这些生成的SQL语句,修改其中一个字段的字符编码。
将所有数据类型为varchar类型的数据变成utf-8编码和utf8_general_ci排序规则:
SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_general_ci', (CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END), ';')
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'databaseName'
AND DATA_TYPE = 'varchar'
AND
(
CHARACTER_SET_NAME != 'utf8'
OR
COLLATION_NAME != 'utf8_general_ci'
);
**注意**:databaseName改为当前数据库名字,其他不变
2.执行这些生成的SQL语句
ALTER TABLE `user` MODIFY `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `user` MODIFY `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci;
结果: