MySQL编码及与排序规则问题

原创💡 Abner Mai 2021-08-30

MySQL
编码集

约 993 字大约 3 分钟

# MySQL编码及与排序规则问题

# 准备材料：

# 数据库 * 1

CREATE TABLE test_a (
	id INT auto_increment NOT NULL COMMENT 'id',
	user_name varchar(100) NOT NULL,
	password varchar(100) NOT NULL,
	CONSTRAINT test_a_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

1
2
3
4
5
6
7
8
9

# 数据表 * 2

-- 第一个表
CREATE TABLE test_a (
	id INT auto_increment NOT NULL COMMENT 'id',
	user_name varchar(100) NOT NULL,
	password varchar(100) NOT NULL,
	CONSTRAINT test_a_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

-- 第二个表
CREATE TABLE test_b (
	user_name varchar(100) NOT NULL,
	age varchar(100) NOT NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 向两个表中添加数据

INSERT INTO test_a (id, user_name, password) VALUES(1, 'abc', '123456');
INSERT INTO test_b (user_name, age) VALUES('abc', '18');

1
2

# 关联查询

需求：将两张表进行关联查询

查询语句：

SELECT * FROM test_a ta Left Join test_b tb ON ta .user_name = tb.user_name WHERE ta.user_name = 'abc';

执行结果：

SQL 错误 [1267] [HY000]: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

# 错误分析

根据 SQL执行结果分析，出现错误的原因为排序规则有误，不能进行操作
先查看数据库的默认编码集与排序规则（这一步好像没什么作用，但是如果建表的时候没有指定编码集与排序规则，那么就会设置数据库默认的编码集与排序规则，所以这一步还是可以使用起来的）
1. 语句：
```
-- 查看字符集
show variables like '%character%';
-- 查看排序规则
show variables like '%collation%';
```
  1
  2
  3
  4
  结果
  
  字符集
  
  排序规则
再从建表语句分析，发现建表语句是有问题，就是排序规则不一致

# 解决方案

重做表格（如果是新表：test_a或者test_b都可以，却决于系统使用的统一编码集）

-- 先删除原来的表
drop table if exists test_a;
-- 更改字符集后的表结构
CREATE TABLE test_a (
	id INT auto_increment NOT NULL COMMENT 'id',
	user_name varchar(100) NOT NULL,
	password varchar(100) NOT NULL,
	CONSTRAINT test_a_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

1
2
3
4
5
6
7
8
9
10
11
12

修改表的排序规则
1. 仅修改了表排序规则
  1. 步骤：
    修改表的排序规则：
    
    ALTER TABLE test_a CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;
    
    1
    查看修改后的表结构
    
    show create table test_a;
    
    1
    结果：
    
    CREATE TABLE `test_a` ( `id` int NOT NULL AUTO_INCREMENT COMMENT 'id', `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    
    1
    2
    3
    4
    5
    6
    验证结果：
    
    执行*联表查询*语句
    
    结果与开始的一致
    分析：
    
    我的理解：表格在创建时，表格以及表字段都会应用建表时指定的字符集与排序规则，当我们直接执行修改表的排序规则时，并不会应用到字段中，所以才会出现更改了编码之后，仍然报错的问题。
    
    更改了表的编码与排序规则之后，需要将字段编码与排序规则一同更新，否则无效。
    
    表结构对比，会发现没有单独修改字段的排序规则，它仍然使用的还是默认的排序规则，所以再执行还是报错。
2. 修改了表与字段的排序规则
  1. 步骤：
    修改表与表字段的语句
    
    ALTER TABLE test_a CONVERT TO CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;
    
    1
    查看修改后的表结构
    
    show create table test_a;
    
    1
    结果[^3]：
    
    CREATE TABLE `test_a` ( `id` int NOT NULL AUTO_INCREMENT COMMENT 'id', `user_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `password` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    
    1
    2
    3
    4
    5
    6
    结果：
    
    执行联表查询语句
    
    查询结果
    分析
    
    查看表结构可以看出，这一次的修改，连同字段的排序规则也一并更改了，所以test_a与test_b表的编码集与排序规则也就一样了，所以查询没问题。

# 建议

在使用同一个数据库时，应尽量使用相同的编码集与排序规则，避免错误，才能节约时间。