mysql字符集与排序规则
MySQL的字符集包括字符集（charset）和排序规则（collation）两个概念，其中字符集定义了存储字符串的方式，排序规则定义了比较字符串的方式。
[TOC]
字符集
- MySQL 5.7 默认字符集为 Latin1，它不支持中文，若要支持中文需将数据库的字符集修改为gbk或者utf8
- MySQL 5.7 及以前，默认字符集为 latin1、默认排序规则为 latin1_swedish_ci，utf8 默认指向的是 utf8mb3
- MySQL 8.0 及以后，默认字符集为 utf8mb4、默认排序规则为 utf8mb4_general_ci，utf8 默认指向的是 utf8mb4
- MySQL 对于字符集的支持细化到四个层次：
实例(server)
数据库(database)
数据表(table)及字段(column)
连接(connection)
排序规则
- 排序规则：是指对指定字符集下不同字符的比较规则，它有以下特征：
它和字符集（CHARSET）相关
每种字符集都有多种它支持的排序规则
每种字符集都会默认指定一种排序规则为默认值。
- 排序规则的影响范围：凡是涉及到字符类型比较或排序的地方，都和排序规则有关，具体如下
影响 ORDER BY 语句查询的结果顺序
影响 WHERE条件中大于小于号的筛选结果
影响 DISTINCT、GROUP BY、HAVING 语句的查询结果
如果索引列是字符类型，还会影响索引的创建
- 排序规则的设置可以分为：MySQL实例级别、库级别、表级别、字段级别、SQL指定
- 排序规则的优先级顺序是：SQL指定 > 字段级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。也就是说，如果SQL语句中指定了排序规则，则以其指定为准，否则以字段级别设置为准，如果字段级别没有指定，则继承表级别的设置，以此类推。
如何确认
- 查看当前mysql支持的字符集和排序规则：
show charset; - 查看实例字符集：
show variables like 'character_set_server'; - 查看实例排序规则：
show variables like 'collation_server'; - 查看指定库的字符集：
show create database 库名; - 查看当前库的字符集：
use 库名; show variables like 'character_set_database'; - 查看当前库的排序规则：
use 库名; show variables like 'collation_database'; - 查看表的字符集：
show create table 表名; - 查看字段字符集：
show full columns from 表名; - 查看连接字符集：
show variables like 'character_set_connection'; - 查看连接的排序规则：
show variables like 'collation_connection';
如何修改
- 修改实例的字符集和排序规则：先修改
/etc/my.cnf再重启mysql服务以生效，示例为 utf8mb4
[client]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4
[mysqldump]
default-character-set = utf8mb4
- 修改库的字符集，示例为 utf8mb4：
alter database 库名 character set utf8mb4; - 修改表的字符集，示例为 utf8mb4：
ALTER TABLE 表名 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; - 修改表的字符集，示例为 utf8mb4：
ALTER TABLE 表名 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; - 修改字段的排序规则，示例为 xxx：
alter table 表名 modify column '字段名' varchar(30) character set utf8mb4_unicode_ci not null; - SQL语句指定排序规则，示例为 utf8_bin：
select * from tb03 where name='user' collate utf8_bin; - 修改连接的字符集，执行
SET NAMES xxx;相当于同时设置3个客户端的字符集，示例为 latin1
mysql> SET NAMES 'latin1';
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 | #.生效的地方
| character_set_connection | latin1 | #.生效的地方
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | latin1 | #.生效的地方
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
+--------------------------+----------------------------+
批量修改
- 批量修改某个db下所有表的字符集，示例为 utf8mb4
select
table_schema,table_name,table_rows,table_collation
,concat('alter table ','`',table_schema,'`.`',table_name,'`',' convert to character set utf8mb4 collate utf8mb4_unicode_ci;') as command
from information_schema.tables
where table_schema = 'testdb' and table_collation <> 'utf8mb4_unicode_ci' order by table_name;
#.脚本执行效果如下
+--------------+------------+------------+-----------------+------------------------------------------------------------------------------------------+
| table_schema | table_name | table_rows | table_collation | command |
+--------------+------------+------------+-----------------+------------------------------------------------------------------------------------------+
| testdb | Tb01 | 0 | utf8mb4_bin | alter table `testdb`.`Tb01` convert to character set utf8mb4 collate utf8mb4_unicode_ci; |
+--------------+------------+------------+-----------------+------------------------------------------------------------------------------------------+
如何避免乱码
- 登录 mysql 先执行
use xxx;切换到指定库，再执行show variables like 'character%';
mysql> use 库名;
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 | #.客户端使用的字符集
| character_set_connection | utf8 | #.连接数据库时的字符集
| character_set_database | utf8 | #.当前库的字符集,或默认创建数据库的编码格式
| character_set_filesystem | binary | #.文件系统的编码格式,不会影响乱码
| character_set_results | utf8 | #.mysql给客户端返回时使用的编码格式
| character_set_server | utf8 | #.mysql安装时指定的默认编码格式
| character_set_system | utf8mb4 | #.数据库系统使用的编码格式,优先级最低,不会影响乱码
+--------------------------+----------------------------+
- 客户端的设置：character_set_client、character_set_connection、character_set_results
- 服务端的设置：character_set_system、character_set_server、character_set_database
- 服务器端的参数优先级：charactersetdatabase > charactersetserver > charactersetsystem
- 以上参数必须保证除了 character_set_filesystem 外的参数都统一，才不会出现乱码的情况
- 但由于 character_set_system 优先级最低，因此影响不大。修改了客户端字符集、character_set_database、character_set_server 基本上就不会出现乱码了
其他涉及字符集的地方
- mysql登录的时候，指定 --default-character-set=latin1 可对客户端指定字符集
[root@localhost ~]# mysql -uroot -p -h 127.0.0.1 --default-character-set=latin1 -e"show variables like 'character%';"
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 | #.生效的地方
| character_set_connection | latin1 | #.生效的地方
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | latin1 | #.生效的地方
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
+--------------------------+----------------------------+
- mysqldump备份的时候，指定 --default-character-set=utf8mb4 可按照设定的字符集来备份数据
mysqldump --single-transaction --master-data=2 --default-character-set=utf8mb4 --databases 库名 > data.2023.sql