带你5分钟读懂MySQL字符集设置
一、内容概述
在MySQL的使用过程中，了解字符集、字符序的概念，以及不同设置对数据存储、比较的影响非常重要。不少同学在日常工作中遇到的“乱码”问题，很有可能就是因为对字符集与字符序的理解不到位、设置错误造成的。
本文由浅入深，分别介绍了如下内容：
- 字符集、字符序的基本概念及联系
- MySQL支持的字符集、字符序设置级，各设置级别之间的联系
- server、database、table、column级字符集、字符序的查看及设置
- 应该何时设置字符集、字符序
二、字符集、字符序的概念与联系
在数据的存储上，MySQL提供了不同的字符集支持。而在数据的对比操作上，则提供了不同的字符序支持。
MySQL提供了不同级别的设置，包括server级、database级、table级、column级，可以提供非常精准的设置。
什么是字符集、字符序？简单的来说：
- 字符集（characterset）：定义了字符以及字符的编码。
- 字符序（collation）：定义了字符的比较规则。
举个例子：
有四个字符：A、B、a、b，这四个字符的编码分别是A=0,B=1,a=2,b=3。这里的字符+编码就构成了字符集（characterset）。
如果我们想比较两个字符的大小呢？比如A、B，或者a、b，最直观的比较方式是采用它们的编码，比如因为0<1，所以A 另外，对于A、a，虽然它们编码不同，但我们觉得大小写字符应该是相等的，也就是说A==a。 这上面定义了两条比较规则，这些比较规则的集合就是collation。 MySQL支持多种字符集与字符序。 上面说的比较抽象，我们看下后面几个小节就知道怎么回事了。 1、查看支持的字符集 可以通过以下方式查看MYSQL支持的字符集。 方式一： 方式二： 当使用SHOWCHARACTERSET查看时，也可以加上WHERE或LIKE限定条件。 例子一：使用WHERE限定条件。 例子二：使用LIKE限定条件。 2、查看支持的字符序 类似的，可以通过如下方式查看MYSQL支持的字符序。 方式一：通过SHOWCOLLATION进行查看。 可以看到，utf8字符集有超过10种字符序。通过Default的值是否为Yes，判断是否默认的字符序。 方式二：查询information_schema.COLLATIONS。 3、字符序的命名规范 字符序的命名，以其对应的字符集作为前缀，如下所示。比如字符序utf8_general_ci，标明它是字符集utf8的字符序。 更多规则可以参考官方文档。 用途：当你创建数据库，且没有指定字符集、字符序时，server字符集、server字符序就会作为该数据库的默认字符集、排序规则。 如何指定：MySQL服务启动时，可通过命令行参数指定。也可以通过配置文件的变量指定。 server默认字符集、字符序：在MySQL编译的时候，通过编译参数指定。 character_set_server、collation_server分别对应server字符集、server字符序。 1、查看server字符集、字符序 分别对应character_set_server、collation_server两个系统变量。 2、启动服务时指定 可以在MySQL服务启动时，指定server字符集、字符序。如不指定，默认的字符序分别为latin1、latin1_swedish_ci 单独指定server字符集，此时，server字符序为latin1的默认字符序latin1_swedish_ci。 3、配置文件指定 除了在命令行参数里指定，也可以在配置文件里指定，如下所示。 4、运行时修改 例子：运行时修改（重启后会失效，如果想要重启后保持不变，需要写进配置文件里） 5、编译时指定默认字符集、字符序 character_set_server、collation_server的默认值，可以在MySQL编译时，通过编译选项指定： 用途：指定数据库级别的字符集、字符序。同一个MySQL服务下的数据库，可以分别指定不同的字符集/字符序。 1、设置数据的字符集/字符序 可以在创建、修改数据库的时候，通过CHARACTERSET、COLLATE指定数据库的字符集、排序规则。 创建数据库： 修改数据库： 例子：创建数据库test_schema，字符集设置为utf8，此时默认的排序规则为utf8_general_ci。 2、查看数据库的字符集/字符序 有3种方式可以查看数据库的字符集/字符序。 例子一：查看test_schema的字符集、排序规则。（需要切换默认数据库） 例子二：也可以通过下面命令查看test_schema的字符集、数据库（不需要切换默认数据库） 例子三：也可以通过查看创建数据库的语句，来查看字符集。 3、database字符集、字符序是怎么确定的 创建数据库时，指定了CHARACTERSET或COLLATE，则以对应的字符集、排序规则为准。 创建表、修改表的语法如下，可通过CHARACTERSET、COLLATE设置字符集、字符序。 1、创建table并指定字符集/字符序 例子如下，指定字符集为utf8，字符序则采用默认的。 2、查看table的字符集/字符序 同样，有3种方式可以查看table的字符集/字符序。 方式一：通过SHOWTABLESTATUS查看table状态，注意Collation为utf8_general_ci，对应的字符集为utf8。 方式二：查看information_schema.TABLES的信息。 方式三：通过SHOWCREATETABLE确认。 3、table字符集、字符序如何确定 假设CHARACTERSET、COLLATE的值分别是charset_name、collation_name。如果创建table时： 明确了charset_name、collation_name，则采用charset_name、collation_name。 只明确了collation_name，但charset_name未明确，则字符序采用collation_name，字符集采用collation_name关联的字符集。 charset_name、collation_name均未明确，则采用数据库的字符集、字符序设置。 类型为CHAR、VARCHAR、TEXT的列，可以指定字符集/字符序，语法如下： 1、新增column并指定字符集/排序规则 例子如下：（创建table类似） 2、查看column的字符集/字符序 例子如下： 3、column字符集/排序规则确定 假设CHARACTERSET、COLLATE的值分别是charset_name、collation_name： 如果charset_name、collation_name均明确，则字符集、字符序以charset_name、collation_name为准。 只明确了charset_name，collation_name未明确，则字符集为charset_name，字符序为charset_name的默认字符序。 只明确了collation_name，charset_name未明确，则字符序为collation_name，字符集为collation_name关联的字符集。 charset_name、collation_name均未明确，则以table的字符集、字符序为准。 一般来说，可以在三个地方进行配置： 创建数据库的时候进行配置。 mysqlserver启动的时候进行配置。 从源码编译mysql的时候，通过编译参数进行配置 1、方式一：创建数据库的时候进行配置 这种方式比较灵活，也比较保险，它不依赖于默认的字符集/字符序。当你创建数据库的时候指定字符集/字符序，后续创建table、column的时候，如果不特殊指定，会继承对应数据库的字符集/字符序。 2、方式二：mysqlserver启动的时候进行配置 可以添加以下配置，这样mysqlserver启动的时候，会对character-set-server、collation-server进行配置。 当你通过mysqlclient创建database/table/column，且没有显示声明字符集/字符序，那么就会用character-set-server/collation-server作为默认的字符集/字符序。 另外，client、server连接时的字符集/字符序，还是需要通过SETNAMES进行设置。 3、方式三：从源码编译mysql的时候，通过编译参数进行设置 编译的时候如果指定了-DDEFAULT_CHARSET和-DDEFAULT_COLLATION，那么： 创建database、table时，会将其作为默认的字符集/字符序。 client连接server时，会将其作为默认的字符集/字符序。（不用单独SETNAMES） 本文较为详细地介绍了MySQL中字符集、字符序相关的内容，这部分内容主要针对的是数据的存储与比较。其实还有很重要的一部分内容还没涉及：针对连接的字符集、字符序设置。 由于连接的字符集、字符序设置不当导致的乱码问题也非常多，这部分内容展开来讲内容也不少，放在下一篇文章进行讲解。 10.1CharacterSetSupport https://dev.mysql.com/doc/refman/5.7/en/charset.html 总结 以上所述是小编给大家介绍的带你5分钟读懂MySQL字符集设置，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！
三、MySQL支持的字符集、字符序
mysql>SHOWCHARACTERSET;
+----------+-----------------------------+---------------------+--------+
|Charset|Description|Defaultcollation|Maxlen|
+----------+-----------------------------+---------------------+--------+
|big5|Big5TraditionalChinese|big5_chinese_ci|2|
|dec8|DECWestEuropean|dec8_swedish_ci|1|
...省略
mysql>useinformation_schema;
mysql>select*fromCHARACTER_SETS;
+--------------------+----------------------+-----------------------------+--------+
|CHARACTER_SET_NAME|DEFAULT_COLLATE_NAME|DESCRIPTION|MAXLEN|
+--------------------+----------------------+-----------------------------+--------+
|big5|big5_chinese_ci|Big5TraditionalChinese|2|
|dec8|dec8_swedish_ci|DECWestEuropean|1|
...省略
mysql>SHOWCHARACTERSETWHERECharset="utf8";
+---------+---------------+-------------------+--------+
|Charset|Description|Defaultcollation|Maxlen|
+---------+---------------+-------------------+--------+
|utf8|UTF-8Unicode|utf8_general_ci|3|
+---------+---------------+-------------------+--------+
1rowinset(0.00sec)
mysql>SHOWCHARACTERSETLIKE"utf8%";
+---------+---------------+--------------------+--------+
|Charset|Description|Defaultcollation|Maxlen|
+---------+---------------+--------------------+--------+
|utf8|UTF-8Unicode|utf8_general_ci|3|
|utf8mb4|UTF-8Unicode|utf8mb4_general_ci|4|
+---------+---------------+--------------------+--------+
2rowsinset(0.00sec)
mysql>SHOWCOLLATIONWHERECharset='utf8';
+--------------------------+---------+-----+---------+----------+---------+
|Collation|Charset|Id|Default|Compiled|Sortlen|
+--------------------------+---------+-----+---------+----------+---------+
|utf8_general_ci|utf8|33|Yes|Yes|1|
|utf8_bin|utf8|83||Yes|1|
...略
mysql>USEinformation_schema;
mysql>SELECT*FROMCOLLATIONSWHERECHARACTER_SET_NAME="utf8";
+--------------------------+--------------------+-----+------------+-------------+---------+
|COLLATION_NAME|CHARACTER_SET_NAME|ID|IS_DEFAULT|IS_COMPILED|SORTLEN|
+--------------------------+--------------------+-----+------------+-------------+---------+
|utf8_general_ci|utf8|33|Yes|Yes|1|
|utf8_bin|utf8|83||Yes|1|
|utf8_unicode_ci|utf8|192||Yes|8|
MariaDB[information_schema]>SELECTCHARACTER_SET_NAME,COLLATION_NAMEFROMCOLLATIONSWHERECHARACTER_SET_NAME="utf8"limit2;
+--------------------+-----------------+
|CHARACTER_SET_NAME|COLLATION_NAME|
+--------------------+-----------------+
|utf8|utf8_general_ci|
|utf8|utf8_bin|
+--------------------+-----------------+
2rowsinset(0.00sec)
四、server的字符集、字符序
mysql>SHOWVARIABLESLIKE"character_set_server";
mysql>SHOWVARIABLESLIKE"collation_server";
mysqld--character-set-server=latin1\
--collation-server=latin1_swedish_ci
mysqld--character-set-server=latin1
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server=utf8_unicode_ci
init-connect='SETNAMESutf8'
character-set-server=utf8
mysql>SETcharacter_set_server=utf8;
cmake.-DDEFAULT_CHARSET=latin1\
-DDEFAULT_COLLATION=latin1_german1_ci
五、database的字符集、字符序
CREATEDATABASEdb_name
[[DEFAULT]CHARACTERSETcharset_name]
[[DEFAULT]COLLATEcollation_name]
ALTERDATABASEdb_name
[[DEFAULT]CHARACTERSETcharset_name]
[[DEFAULT]COLLATEcollation_name]
CREATEDATABASE`test_schema`DEFAULTCHARACTERSETutf8;
mysql>usetest_schema;
Databasechanged
mysql>SELECT@@character_set_database,@@collation_database;
+--------------------------+----------------------+
|@@character_set_database|@@collation_database|
+--------------------------+----------------------+
|utf8|utf8_general_ci|
+--------------------------+----------------------+
1rowinset(0.00sec)
mysql>SELECTSCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME,DEFAULT_COLLATION_NAMEFROMinformation_schema.SCHEMATAWHEREschema_name="test_schema";
+-------------+----------------------------+------------------------+
|SCHEMA_NAME|DEFAULT_CHARACTER_SET_NAME|DEFAULT_COLLATION_NAME|
+-------------+----------------------------+------------------------+
|test_schema|utf8|utf8_general_ci|
+-------------+----------------------------+------------------------+
1rowinset(0.00sec)
mysql>SHOWCREATEDATABASEtest_schema;
+-------------+----------------------------------------------------------------------+
|Database|CreateDatabase|
+-------------+----------------------------------------------------------------------+
|test_schema|CREATEDATABASE`test_schema`/*!40100DEFAULTCHARACTERSETutf8*/|
+-------------+----------------------------------------------------------------------+
1rowinset(0.00sec)
创建数据库时，如果没有指定字符集、排序规则，则以character_set_server、collation_server为准。
六、table的字符集、字符序
CREATETABLEtbl_name(column_list)
[[DEFAULT]CHARACTERSETcharset_name]
[COLLATEcollation_name]]
ALTERTABLEtbl_name
[[DEFAULT]CHARACTERSETcharset_name]
[COLLATEcollation_name]
CREATETABLE`test_schema`.`test_table`(
`id`INTNOTNULLCOMMENT'',
PRIMARYKEY(`id`)COMMENT'')
DEFAULTCHARACTERSET=utf8;
MariaDB[blog]>SHOWTABLESTATUSFROMtest_schema\G;
***************************1.row***************************
Name:test_table
Engine:InnoDB
Version:10
Row_format:Compact
Rows:0
Avg_row_length:0
Data_length:16384
Max_data_length:0
Index_length:0
Data_free:11534336
Auto_increment:NULL
Create_time:2018-01-0916:10:42
Update_time:NULL
Check_time:NULL
Collation:utf8_general_ci
Checksum:NULL
Create_options:
Comment:
1rowinset(0.00sec)
mysql>USEtest_schema;
mysql>SELECTTABLE_COLLATIONFROMinformation_schema.TABLESWHERETABLE_SCHEMA="test_schema"ANDTABLE_NAME="test_table";
+-----------------+
|TABLE_COLLATION|
+-----------------+
|utf8_general_ci|
+-----------------+
mysql>SHOWCREATETABLEtest_table;
+------------+----------------------------------------------------------------------------------------------------------------+
|Table|CreateTable|
+------------+----------------------------------------------------------------------------------------------------------------+
|test_table|CREATETABLE`test_table`(
`id`int(11)NOTNULL,
PRIMARYKEY(`id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8|
+------------+----------------------------------------------------------------------------------------------------------------+
1rowinset(0.00sec)
只明确了charset_name，但collation_name未明确，则字符集采用charset_name，字符序采用charset_name对应的默认字符序。
七、column的字符集、排序
col_name{CHAR|VARCHAR|TEXT}(col_length)
[CHARACTERSETcharset_name]
[COLLATEcollation_name]
mysql>ALTERTABLEtest_tableADDCOLUMNchar_columnVARCHAR(25)CHARACTERSETutf8;
mysql>SELECTCHARACTER_SET_NAME,COLLATION_NAMEFROMinformation_schema.COLUMNSWHERETABLE_SCHEMA="test_schema"ANDTABLE_NAME="test_table"ANDCOLUMN_NAME="char_column";
+--------------------+-----------------+
|CHARACTER_SET_NAME|COLLATION_NAME|
+--------------------+-----------------+
|utf8|utf8_general_ci|
+--------------------+-----------------+
1rowinset(0.00sec)
八、选择：何时设置字符集、字符序
CREATEDATABASEmydb
DEFAULTCHARACTERSETutf8
DEFAULTCOLLATEutf8_general_ci;
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
shell>cmake.-DDEFAULT_CHARSET=utf8\
-DDEFAULT_COLLATION=utf8_general_ci
九、写在后面
十、相关链接