`Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='` 问题解决

2021-09-18T10:57:00

laravel 默认的数据库排序编码 app/config.php:

'mysql' => [
...
    'collation' => 'utf8_unicode_ci',
...
]

ci 表示 case insensitive,即大小写不敏感,与之对应的是 cs case sensitive。utf8 为字符集编码。

新版本变成了 utf8mb4_unicode_ci,但不影响它使用 unicode 而非 general 的事实(印象中创建的数据库都是带 general 的)。

百度两者的区别,之前的说法是,general 排序更快,但对于中英文以外的字符匹配准确度没有 unicode 高,结论是基本没有区别。现在看到的说法是建议使用 unicode 排序,因为现在对性能提升基本可以忽略。

UPDATE: utf8mb4/utf8mb4_unicode_ci is now the preferred character set/collation method. utf8_general_ci is advised against, as the performance improvement is negligible. See https://stackoverflow.com/a/766996/1432614

这就是为什么 laravel 直接使用 utf8mb4_unicode_ci

回到问题,不合法的校验排序规则混合,基本面出现在操作符 = 时。查看报错详情,是 leftJoin 多张表报的错。

主要有两种方式去解决这个问题

修改数据库表、列的排序规则

mysql 数据库有一个 information_schema 数据库,里面的 TABLESCOLUMNS 分别记录了所有表和列的详细信息,可以通过以下 sql 语句查询目标数据库(比如:saas)排序规则不是 utf8_unicode_ci 的表与列信息:

SELECT * FROM `TABLES` WHERE TABLE_COLLATION <> 'utf8_unicode_ci' AND TABLE_SCHEMA = 'saas';
SELECT * FROM `COLUMNS` WHERE COLLATION_NAME <> 'utf8_unicode_ci' AND TABLE_SCHEMA = 'saas';

对比数据库发现,原来的数据库中大部分表和列(字符串类型)都是 utf8_unicode_ci,所以现在的目标是修改其他几张新创建的表和列的排序规则。

尝试直接修改 information_schema 记录值:

UPDATE `TABLES` SET TABLE_COLLATION = 'utf8_unicode_ci' WHERE TABLE_SCHEMA = 'saas';
UPDATE `COLUMNS` SET COLLATION_NAME = 'utf8_unicode_ci' WHERE TABLE_SCHEMA = 'saas' AND DATA_TYPE = 'varchar';

结果失败了,提示没有权限。使用 root 本地修改也没有权限,猜测该数据库是只读的,并不能直接修改记录来达到修改相关表的目的。

找到一个 批量修改 mysql 数据库库表、列的排序规则 的教程,里面通过查询 information_schema 作为子查询,字符连接成需要的指令 sql 并输出,然后复制出来批量执行即可。

因为我的表较少,就没有去尝试,直接查询出记录,然后去修改对应的表与列(使用 navicat 操作界面)。

需要注意的是,如果在修改了所有表与列的排序规则后,仍然报相同的错误,可以考虑两个方面的因素:

一、相关表旧的数据编码格式、排序规则是之前的
二、修改的数据库与实际使用的数据库不一致

第一个因素,可以用以下的指令修改:

alter table 表名 convert to character set utf8 collate utf8_unicode_ci

这个指令并没有看出修改数据的部分,执行指令也并未有修改行。来源于 把表数据的编码规则也统一纠正过来,不确定是否有效。

我主要出现的问题是第二个因素。使用 Postman 测试接口,在修改了表和字段的排序规则后,接口返回没有变化,弄了半天才发现,测试的是本地的接口,连接的数据库当然也是本地的 OωO 。

修改代码

修改代码

主要就是在冲突的地方加上:COLLATE utf8_unicode_ci

当前页面是本站的「Baidu MIP」版。发表评论请点击:完整版 »