如何查看 MySQL Table Character Set and Collation

MySQL在数据库启动和创建Database的时候都都可以指定Character和Collation，在MySQL创建Table的时候是如何继承Character和Collation的属性的？

查看MySQL创建Table的DDL语句中没有显示指定Collation，这时需要我们查看MySQL的配置文件和INFORMATION_SCHEMA CHARACTER_SETS表进行判断。

MySQL通过以下方式选择表字符集和排序规则

• 如果创建表的时候显示指定了CHARACTER SET和COLLATE，就会使用显示指定的参数，参考SQL语句如下：

CREATE TABLE tbl_name (column_list)
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]]

• 如果创建表的时候只指定了 CHARACTER SET，表就会继承 CHARACTER SET 和他默认的 Collation，查看默认的字符集对应的排序规则，可以通过以下方式两种方式之一进行查看。

1.通过 SHOW CHARACTER SET 语句查看,Dafault Collation 列的数值默认字符集对应 的排序规则。

MySQL [(none)]> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
……
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
……
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.001 sec)

2.通过 INFORMATION_SCHEMA 进行查询，具体SQL 语句如下：

MySQL [(none)]> select CHARACTER_SET_NAME,DEFAULT_COLLATE_NAME  from INFORMATION_SCHEMA.CHARACTER_SETS;
+--------------------+----------------------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME |
+--------------------+----------------------+
| big5               | big5_chinese_ci      |
……
| greek              | greek_general_ci     |
| cp1250             | cp1250_general_ci    |
| gbk                | gbk_chinese_ci       |
| latin5             | latin5_turkish_ci    |
| armscii8           | armscii8_general_ci  |
| utf8               | utf8_general_ci      |
| ucs2               | ucs2_general_ci      |
| cp866              | cp866_general_ci     |
| keybcs2            | keybcs2_general_ci   |
| macce              | macce_general_ci     |
| macroman           | macroman_general_ci  |
| cp852              | cp852_general_ci     |
| latin7             | latin7_general_ci    |
| utf8mb4            | utf8mb4_general_ci   |
……
| gb18030            | gb18030_chinese_ci   |
+--------------------+----------------------+
41 rows in set (0.001 sec)

• 如果只指定了COLLATE，那么CHARACTER SET会使用与之对应的属性，例如如果COLLATE指定的是utf8mb4_general_ci ，那么CHARACTER SET便是utf8mb4
• 如果 CHARACTER SET 和 COLLATE 都没有显示声明，会继承Database 级别的参数属性。

[1]https://dev.mysql.com/doc/refman/5.7/en/charset-table.html

如果您有其他问题，欢迎您联系火山引擎技术支持服务