Illegal mix of collations

最新推荐文章于 2024-07-02 19:13:07 发布

原创最新推荐文章于 2024-07-02 19:13:07 发布 · 2.4k 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #collation #字符集 #illegal

mysql 同时被 2 个专栏收录

4 篇文章

订阅专栏

工作中的坑

3 篇文章

订阅专栏

本文探讨了在MySQL中遇到的关联查询时出现的字符集不匹配错误，即`utf8mb4_unicode_ci`和`utf8mb4_general_ci`的混合使用问题。错误表明不同排序规则导致无法进行等值比较。排序规则（collations）用于规定字符比较的规则，解决多语言环境下数据的排序和比较。当不同表或字段的排序规则不一致时，会导致查询失败。解决方法是统一数据库、表或字段的排序规则，通过查询和修改数据库或表的默认排序规则来达到一致。此外，还提供了查询和修改排序规则的SQL语句，以及查看数据库表死锁记录的方法。

1、背景

在做关联查询时候出现问题

select * from A where ref_b_id = (select id from b where user_id='11');

将会提示你如下错误

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='.md

2、分析

由上面字面意思就是 utf8mb4_unicode_ci 和 utf8mb4_general_ci 混合使用，同时也说明不能混合使用

collations关键字是排序规则，a和A同时存在，顺序应该是aA还是Aa，或者说a==A? 不区分大小吗？

1、为啥需要存在collations? 它解决什么问题？

		第一点世界上有很多种语言，语言差异很大，比如中文和日元有相似地方，但是不一样，
		所有不同字符集去编码对应语言，比如现在已经编码好保存到数据库了，我们现在需要查询，
		数据库怎么知道那条数据算第一条数据，数据库肯定是对所有数据进行比较，
		最常见就是ASCII的字符代表数字大小，还有一些情况我们就是认为区分大小（默认是不区分）a A是不一样，
		这些字符的比较工作需要有评判规则，而这些规则就是collations所做的事情。

不同字符比较规则，自然就不能关联比较了，也就不能用等号，日语的某些汉字和中文汉字虽然展示是一样但是实际编码规则是不一样

3、解决方法

解决方式：让两个表统一一下collations

3.1、怎么统一collations

如何查询表使用哪个collations
```
SHOW FULL COLUMNS FROM 数据库名.表名
```
- 可以看出目前是collation的是utf8mb4_general_ci

如何查看当前数据库schema的collation?

SHOW VARIABLES LIKE 'collation_database';

如何修改当前数据库默认collation？例如修改为 utf8mb4_unicode_ci
- ```
ALTER DATABASE COLLATE utf8mb4_unicode_ci;
```

如何修改当前表默认collation? 例如修改为 utf8mb4_unicode_ci

ALTER TABLE t_application_ocr_result  COLLATE utf8mb4_unicode_ci;

如何修改某个表的字段默认collation 例如修改user_id为 utf8mb4_unicode_ci

ALTER TABLE t_application_ocr_result  MODIFY  user_id varchar(28) COLLATE utf8mb4_unicode_ci;

查看数据库表死锁记录

SHOW ENGINE INNODB STATUS; 
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3、参考

如何使得mysql不区分大小写？：How can I make SQL case sensitive string comparison on MySQL?
Mysql的collation是什么？https://www.quora.com/What-is-collation-in-MySQL
MySQL查看、修改字符集及Collation