`Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='` 问题解决
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
数据库,里面的 TABLES
与 COLUMNS
分别记录了所有表和列的详细信息,可以通过以下 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