MySQL 字符集相关

为了支持各个国家的不同语言，MySQL 从4.0 版本开始支持了很多种字符集，且每种字符集支持了 N 多种排序规则。我们可以在建表的时候指定字符集的排序规则，不指定时会有一个默认规则。

字符集和排序规则总是一起使用的，每个排序规则都是针对某个特性的字符集，和其他字符集没有关系。

只有基于字符串的值才有字符集的概念。其他类型的值，像 int,float,bool 字符集的配置只是一种声明。

每个 database 有自己的字符集和排序规则默认值，每张表可以指定自己的字符集和排序规则，同一张表的不同字段也可以指定自己的字符集和排序规则。这是一个逐层继承的默认设置。

查看 MySQL 支持的字符集

show charset 命令可以查看当前版本支持哪些字符集

mysql> show charset;

+----------+---------------------------------+---------------------+--------+

| Charset  | Description                     | Default collation   | Maxlen |

+----------+---------------------------------+---------------------+--------+

| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |

| ascii    | US ASCII                        | ascii_general_ci    |      1 |

| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |

| binary   | Binary pseudo charset           | binary              |      1 |

Charset：支持的字符集

Default collation：默认排序规则。常用的规则（ _ci 结尾表示大小写不敏感；_cs 结尾表示大小写敏感；_bin 表示二进制的比较 ）

Maxlen：最大支持的字节数

show collation 查看字符集支持的哪些排序规则

mysql> show collation like 'gbk%';

+----------------+---------+----+---------+----------+---------+---------------+

| Collation      | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |

+----------------+---------+----+---------+----------+---------+---------------+

| gbk_bin        | gbk     | 87 |         | Yes      |       1 | PAD SPACE     |

| gbk_chinese_ci | gbk     | 28 | Yes     | Yes      |       1 | PAD SPACE     |

+----------------+---------+----+---------+----------+---------+---------------+

2 rows in set (0.02 sec)

配置字符集和排序规则

在 my.cnf 文件中配置默认字符集

> cat my.cnf

[client]  # 客户端连接时的字符集

default-character-set = utf8mb4

[mysql]  # mysql shell 登录时的字符集

default-character-set = utf8mb4

[mysqld]  # 服务端字符集

character-set-server = utf8mb4

collation-server = utf8mb4_general_ci  # 排序规则

在 session 会话中配置字符集

执行 status 命令查看当前会话的字符集

mysql> status

--------------

...

Server characterset:	utf8mb4

Db     characterset:	utf8mb4

Client characterset:	utf8mb4

Conn.  characterset:	utf8mb4

...

修改 当前会话的字符集

mysql> set NAMES "utf8";

Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> status

--------------

...

Server characterset:	utf8mb4

Db     characterset:	utf8mb4

Client characterset:	utf8mb3  # 只能修改会话参数，而没有修改服务端。

Conn.  characterset:	utf8mb3

...

建表的时候配置字符集

a 列指定了 gbk 字符集，排序规则默认

b 列指定了 utf8 字符集，排序规则是 utf8mb4_general_ci

c 列字符集和排序规则都是表 t4 的默认值 （CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci）

这是一个逐层继承的默认设置，但通常不需要这么配置。

mysql> CREATE TABLE t4 (

    a VARCHAR(2) CHARSET GBK,

    b VARCHAR(4) CHARSET UTF8 COLLATE UTF8_GENERAL_CI,

    c VARCHAR(6)

);

mysql> show create table t4 \G;

Create Table: CREATE TABLE t4 (

  a varchar(2) CHARACTER SET gbk COLLATE gbk_chinese_ci DEFAULT NULL,

  b varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

  c varchar(6) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

1. 隐式转换之字符集

只有字符集相同的字段才能做比较运算，如果字符集不同，MySQL 会先将其准换成同一个字符集再进行比较。（如果两个字符集不兼容的话则报错）

接上面的例子 表t4：

mysql> show full columns from t4;

+-------+------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| Field | Type       | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |

+-------+------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

| a     | varchar(2) | gbk_chinese_ci  | YES  |     | NULL    |       | select,insert,update,references |         |

| b     | varchar(4) | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |

| c     | varchar(6) | utf8_unicode_ci | YES  |     | NULL    |       | select,insert,update,references |         |

+-------+------------+-----------------+------+-----+---------+-------+---------------------------------+---------+

插入一些测试数据：

insert into t4 select "跑步","跑步","ff";

insert into t4 select "aa","aa","cc";

insert into t4 select "aa","bb","dd";

mysql> select * from t4;

+--------+--------+------+

| a      | b      | c    |

+--------+--------+------+

| 跑步    | 跑步   | ff   |

| aa     | aa     | cc   |

| aa     | bb     | dd   |

+--------+--------+------+

a, b 字段比较测试：

mysql> select t4.*, if(a=b,"a==b","a!=b") ret from t4 where c="ff";

+--------+--------+------+------+

| a      | b      | c    | ret  |

+--------+--------+------+------+

| 跑步   | 跑步    | ff   | a==b |

+--------+--------+------+------+

a, b 列是不同的字符集。 gbk 存中文字符 占两个字节，utf8 是三个字节，存储方式不同，显然不能直接比较。但测试结果返回 True。这是因为优化器帮我们隐式类型转换了。

可以 trace 验证一下：（ trace 分析器可以追踪到优化器具体是如何执行 sql，和选择执行计划的 ）

mysql> set optimizer_trace='enabled=on',end_markers_in_json=on;  # 开启trace分析器会对 MySQL 性能有所影响，建议只在 优化分析问题时临时开启。

mysql> select * from information_schema.optimizer_trace \G; # 查看分析结果

*************************** 1. row ***************************

                            QUERY: select t4.*, if(a=b,"a==b","a!=b") ret from t4 where c="ff"

                            TRACE: {

  "steps": [

    {

      "join_preparation": {

        "select#": 1,

        "steps": [

          {

            "expanded_query": "/* select#1 */ select `t4`.`a` AS `a`,`t4`.`b` AS `b`,`t4`.`c` AS `c`,if((convert(`t4`.`a` using utf8) = `t4`.`b`),'a==b','a!=b') AS `ret` from `t4` where (`t4`.`c` = 'ff')"

          }

        ] /* steps */

      } /* join_preparation */

    },

    {

      "join_optimization": {

        "select#": 1,

        "steps": [

          {

            "condition_processing": {

              "condition": "WHERE",

              "original_condition": "(`t4`.`c` = 'ff')",

...

优化器用 convert() 函数，把 gbk 转成了 utf8，然后再对比：

expanded_query": "/* select#1 */ select `t4`.`a` AS `a`,`t4`.`b` AS `b`,`t4`.`c` AS `c`,if((convert(`t4`.`a` using utf8) = `t4`.`b`)

2. 字符集使用时的一些其他问题

join 关联查询时，被关联的列，如果是不同的字符集，那么关联的列会有索引失效的情况。

影响大小写字符的比较 utf8 默认的排序规则是 _ci 时，也就是大小写不敏感，那么查询的时候 select * from t where c1="a"，会同时返回 c1="a" 和 c1="A"

和预期的结果不一致。敏感还是不敏感要视业务需求而定。

在大小写不敏感的列上创建唯一索引时，可能会失败，因为可能会出现重复的列值，建索引时 Btree 需要先对列值比较排序。

不同的字符集校对规则之间的转换会带来额外的系统开销，性能会有下降。

巴特西

MySQL 字符集相关

查看 MySQL 支持的字符集

配置字符集和排序规则

1. 隐式转换之字符集

2. 字符集使用时的一些其他问题

最新文章

热门文章