mysql字符集相关总结
这边笔记主要记录一下对mysql字符集相关知识的学习
字符集
一般我们使用如下语句创建一张表，创建表的时候指定了charset为utf8编码。
CREATE TABLE `test` ( `c1` int UNSIGNED NOT NULL AUTO_INCREMENT, `c2` varchar(255), `c3` char(11), `c4` varchar(255), PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
但其实mysql也支持除了utf8之外的其他字符集，可以使用命令 show charset 查看当前mysql支持的所有字符集。
例如下图就是我的mysql支持的所有字符集。
可以从上图中看到，第四列表示该字符集最大长度。我们建表时常用的 utf8 在mysql里面实际最大长度是 3 个字符。一般认知中utf8是用1~4个字符来表示具体的内容的，但mysql里面的 utf8 实际是 utf8mb3 的别名，正宗的的最大用4个字符表示的在mysql中是 utf8mb4 。
注意：
utf8mb4 utf8mb4
比较规则
在上面那张图中，第三列 Default collation 就是这些字符集默认的比较规则。例如acsii字符集的默认比较规则是 ascii_general_ci ，utf8字符集的默认比较规则是 utf8_general_ci 。
其实每个字符集有很多种比较规则，例如我们看一下acsii字符集有哪些比较规则，如下图所示
mysql> show collation like 'ascii%'; +------------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +------------------+---------+----+---------+----------+---------+ | ascii_general_ci | ascii | 11 | Yes | | 0 | | ascii_bin | ascii | 65 | | | 0 | +------------------+---------+----+---------+----------+---------+
ascii_general_ci 表示是一种通用的比较，其中 _ci 表示是不区分大小写（case insensitive）。(反之 _cs 就表示大小写敏感)
ascii_bin 表示是二进制比较
再看一下utf8、utf8mb4字符集有哪些比较规则，如下图所示
mysql> show collation like 'utf8%'; +--------------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------------+---------+-----+---------+----------+---------+ | utf8_general_ci | utf8 | 33 | Yes | Yes | 1 | | utf8_bin | utf8 | 83 | | Yes | 1 | | utf8_unicode_ci | utf8 | 192 | | Yes | 8 | | utf8_icelandic_ci | utf8 | 193 | | Yes | 8 | | utf8_latvian_ci | utf8 | 194 | | Yes | 8 | | utf8_romanian_ci | utf8 | 195 | | Yes | 8 | | utf8_slovenian_ci | utf8 | 196 | | Yes | 8 | | utf8_polish_ci | utf8 | 197 | | Yes | 8 | | utf8_estonian_ci | utf8 | 198 | | Yes | 8 | | utf8_spanish_ci | utf8 | 199 | | Yes | 8 | | utf8_swedish_ci | utf8 | 200 | | Yes | 8 | | utf8_turkish_ci | utf8 | 201 | | Yes | 8 | | utf8_czech_ci | utf8 | 202 | | Yes | 8 | | utf8_danish_ci | utf8 | 203 | | Yes | 8 | | utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 | | utf8_slovak_ci | utf8 | 205 | | Yes | 8 | | utf8_spanish2_ci | utf8 | 206 | | Yes | 8 | | utf8_roman_ci | utf8 | 207 | | Yes | 8 | | utf8_persian_ci | utf8 | 208 | | Yes | 8 | | utf8_esperanto_ci | utf8 | 209 | | Yes | 8 | | utf8_hungarian_ci | utf8 | 210 | | Yes | 8 | | utf8_sinhala_ci | utf8 | 211 | | Yes | 8 | | utf8_german2_ci | utf8 | 212 | | Yes | 8 | | utf8_croatian_ci | utf8 | 213 | | Yes | 8 | | utf8_unicode_520_ci | utf8 | 214 | | Yes | 8 | | utf8_vietnamese_ci | utf8 | 215 | | Yes | 8 | | utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 | | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | | utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | | utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | | utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | | utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | | utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | | utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | | utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | | utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | | utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | | utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | | utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | | utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | | utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | | utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | | utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | | utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | | utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | +--------------------------+---------+-----+---------+----------+---------+
使用不同的比较规则对数据进行比较，结果可能是不一样的。
字符集和比较规则是对应的，不管是只修改字符集还是只修改比较规则，另一方都会相应地自动变化。
四个级别的字符集和比较规则
上文提到了字符集和比较规则，实际上在mysql里面有不同级别的字符集和比较规则:
- 服务器级别
- 数据库级别
- 表级别
- 列级别
服务器级别的字符集可以看参数 character_set_server ，服务器级别的比较规则可以看参数 collation_server
mysql> show variables like 'character_set_server'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | character_set_server | utf8 | +----------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'collation_server'; +------------------+-----------------+ | Variable_name | Value | +------------------+-----------------+ | collation_server | utf8_general_ci | +------------------+-----------------+ 1 row in set (0.00 sec)
如果要查看数据库级别的字符集和比较规则，那么可以先 use xxxdb 切换到具体的数据库，然后执行 show variables like 'character_set_database' 和 show variables like 'collation_database' 来查看该数据库的字符集和比较规则。
Database changed mysql> show variables like 'character_set_database'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | character_set_database | utf8 | +------------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like 'collation_database'; +--------------------+-----------------+ | Variable_name | Value | +--------------------+-----------------+ | collation_database | utf8_general_ci | +--------------------+-----------------+ 1 row in set (0.00 sec)
如果要查看某个表的字符集和比较规则，或者表中的某个列的字符集和比较规则，那么可以使用 show create table xxxtb 来查看具体的建表语句，里面就有使用的字符集和比较规则。
如果创建数据库的时候没有指定字符集和比较规则，则会使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则
三个系统变量
mysql服务器可以对应多个客户端，而这些客户端的编码方式可能不同，但存在数据库里的数据的字符集是一定的，mysql就要针对不同的客户端进行编码转换处理，然后返回结果给客户端。
在mysql里面，有三个 SESSION级别 的系统变量，可以进行上述操作：
- character_set_client : 服务器解码客户端请求时使用的字符集
- character_set_connection : 服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
- character_set_results : 服务器向客户端返回数据时使用的字符集
mysql服务器会为每个客户端都维护一份session级别的这三个变量。
不过在实际生产环境中，为了避免麻烦，公司一般都会有规范规定客户端默认都使用utf8字符集。
