MySQL表排序规则不同错误问题分析
作者:彭东稳 时间:2024-01-25 08:12:30
MySQL多表join时报错如下:[Err]1267 – Illegal mix of collations(utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=
就是说两个表的排序规则(COLLATION)不同,无法完成比较。COLLATION是用在排序,大小比较上,一个字符集有一个或多种COLLATION,并且以_ci(大小写不敏感)、_cs(大小写敏感)或_bin(二进制)结束。在做比较时,应该确保两个表的字符排序相同。一般建表的时候不指定,可以走默认的,全是默认的就没什么问题了。
下面来模拟一下各种场景,表结构如下(utf8默认排序规则为utf8_general_ci):
mysql> show create table test.cs\G
*************************** 1. row ***************************
Table: cs
Create Table: CREATE TABLE `cs` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
查看表默认排序规则集
mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.tables where table_name='cs';
+--------------+------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION |
+--------------+------------+-----------------+
| test | cs | utf8_general_ci |
+--------------+------------+-----------------+
1 row in set (0.00 sec)
查看列排序规则集
mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.COLUMNS where TABLE_NAME='cs';
+--------------+------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+--------------+------------+-------------+-----------------+
| test | cs | id | NULL |
| test | cs | name | utf8_general_ci |
+--------------+------------+-------------+-----------------+
2 rows in set (0.00 sec)
从utf8升级为utf8mb4是不支持online ddl的,如下:
mysql> ALTER TABLE cs CONVERT TO CHARACTER SET utf8mb4,ALGORITHM=INPLACE,LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
从utf8.utf8_general_ci变更为utf8.utf8_unicode_ci是不支持online ddl的,如下:
mysql> ALTER TABLE cs CONVERT TO CHARACTER SET utf8 collate utf8_unicode_ci,ALGORITHM=INPLACE,LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
如果使用下面这种方式修改字符集,你会发现,只更改了表级的,没有更改列级的。
mysql> ALTER TABLE cs CHARACTER SET utf8 collate utf8_unicode_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.tables where table_name='cs';
+--------------+------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION |
+--------------+------------+-----------------+
| test | cs | utf8_unicode_ci |
+--------------+------------+-----------------+
1 row in set (0.00 sec)
mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.COLUMNS where TABLE_NAME='cs';
+--------------+------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+--------------+------------+-------------+-----------------+
| test | cs | id | NULL |
| test | cs | name | utf8_general_ci |
+--------------+------------+-------------+-----------------+
2 rows in set (0.00 sec)
所以真正改字符集的时候别忘了加上CONVERT TO,如下:
mysql> ALTER TABLE cs CONVERT TO CHARACTER SET utf8 collate utf8_unicode_ci;
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.COLUMNS where TABLE_NAME='cs';
+--------------+------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+--------------+------------+-------------+-----------------+
| test | cs | id | NULL |
| test | cs | name | utf8_unicode_ci |
+--------------+------------+-------------+-----------------+
2 rows in set (0.00 sec)
要仅仅改变一个表的默认字符集,应使用此语句:
mysql> ALTER TABLE cs default CHARACTER SET utf8 collate utf8_general_ci,ALGORITHM=INPLACE,LOCK=NONE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION from information_schema.tables where table_name='cs';
+--------------+------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION |
+--------------+------------+-----------------+
| test | cs | utf8_general_ci |
+--------------+------------+-----------------+
1 row in set (0.00 sec)
mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.COLUMNS where TABLE_NAME='cs';
+--------------+------------+-------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+--------------+------------+-------------+-----------------+
| test | cs | id | NULL |
| test | cs | name | utf8_unicode_ci |
+--------------+------------+-------------+-----------------+
2 rows in set (0.00 sec)
可以发现列字符集没有改变,并且只有新的列才会默认继承表的字符集(utf8.utf8_general_ci)。
来源:http://www.ywnds.com/?p=11374
标签:mysql,排序规则
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
教你设计大型Oracle数据库
2024-01-28 06:51:36
GDB调试Mysql实战之源码编译安装
2024-01-28 00:37:42
用SQL批量插入数据的代码
2024-01-15 04:49:30
段正淳的css笔记(4)css代码的简写
2007-11-01 22:03:00
Django DRF认证组件流程实现原理详解
2021-01-03 08:48:49
![](https://img.aspxhome.com/file/2023/2/77572_0s.jpg)
MySQL 5.7.18 免安装版配置教程
2024-01-16 06:12:51
![](https://img.aspxhome.com/file/2023/5/85805_0s.png)
IE8"开发人员工具"使用详解
2009-03-26 12:51:00
![](https://img.aspxhome.com/file/UploadPic/up/2009032613125389.gif)
python有证书的加密解密实现方法
2023-02-10 08:07:30
Django 导出 Excel 代码的实例详解
2021-04-01 05:56:41
![](https://img.aspxhome.com/file/2023/3/125153_0s.png)
Python3几个常见问题的处理方法
2022-08-04 10:48:56
MYSQL输入密码后闪退现象的解决方法
2024-01-19 15:06:08
![](https://img.aspxhome.com/file/2023/6/124546_0s.png)
mysql慢查询的分析方法
2010-08-03 14:51:00
一文带你熟悉Go语言中的分支结构
2024-03-07 19:52:49
![](https://img.aspxhome.com/file/2023/6/105156_0s.png)
原生javascript运动函数的封装示例【匀速、抛物线、多属性的运动等】
2024-04-25 13:15:33
js实现网页加载loading的显示
2007-11-06 12:11:00
![](https://img.aspxhome.com/file/UploadPic/200711/6/2007116122626422s.jpg)
基于scrapy实现的简单蜘蛛采集程序
2023-09-22 03:58:02
python wsgiref源码解析
2023-10-20 10:56:27
![](https://img.aspxhome.com/file/2023/3/77653_0s.png)
谈切图优化加速图片显示—淘宝实例
2008-01-30 12:24:00
![](https://img.aspxhome.com/file/UploadPic/20081/30/200813012349773s.gif)
如何高效地访问记录集?
2009-11-22 19:25:00
Bootstrap table的使用方法
2023-09-10 13:34:47
![](https://img.aspxhome.com/file/2023/4/56254_0s.jpg)