2021-2-18：请你说说MySQL的字符集与排序规则对开发有哪些影响？ (3)

就算设置这两个变量，也是无效的：

mysql> set character_set_database = 'utf8'; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'character_set_database'; +------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | character_set_database | utf8mb4 | +------------------------+---------+ 1 row in set (0.09 sec)

修改数据库的字符集还有排序规则的方式：

mysql> alter database test_db character set = 'utf8'; Query OK, 1 row affected (0.01 sec) mysql> show variables like 'character_set_database'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | utf8 | +------------------------+-------+ 1 row in set (0.08 sec)

这个更新只会对新建的表如果没指定字符集和排序规则的生效，并不会更新老表的字符集还有排序规则。

表级别

可以在创建时指定字符集合排序规则，不指定的话，用数据库的字符集还有排序规则，也可以修改字符集和排序规则。

mysql> create table test (name varchar(32)) character set utf8mb4 collate utf8mb4_bin; Query OK, 0 rows affected (0.04 sec) mysql> show create table test; +-------+---------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `name` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+---------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.09 sec) mysql> alter table test character set = 'utf8'; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test; +-------+--------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+--------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.06 sec)

可以看出，仅仅是表的字符集还有排序规则变了，对于已有字段，并没有改变编码和排序规则。

列级别

可以在创建表的时候，指定不同的列有不同的字符集和排序规则，也可以修改列的字符集和排序规则：

mysql> create table test (name varchar(32) character set utf8 collate utf8_bin) character set utf8mb4 collate utf8mb4_bin; Query OK, 0 rows affected (0.03 sec) mysql> show create table test; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.09 sec) mysql> alter table test modify column name varchar(32) COLLATE latin1_bin; Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `name` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.09 sec) MySQL客户端字符编码问题