86

再见乱码：5分钟读懂MySQL字符集设置

 6 years ago
source link: http://www.cnblogs.com/chyingp/p/mysql-character-set-collation.html?amp%3Butm_medium=referral
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

一、内容概述

在MySQL的使用过程中，了解字符集、字符序的概念，以及不同设置对数据存储、比较的影响非常重要。不少同学在日常工作中遇到的“乱码”问题，很有可能就是因为对字符集与字符序的理解不到位、设置错误造成的。

本文由浅入深，分别介绍了如下内容：

  1. 字符集、字符序的基本概念及联系
  2. MySQL支持的字符集、字符序设置级，各设置级别之间的联系
  3. server、database、table、column级字符集、字符序的查看及设置
  4. 应该何时设置字符集、字符序

二、字符集、字符序的概念与联系

在数据的存储上，MySQL提供了不同的字符集支持。而在数据的对比操作上，则提供了不同的字符序支持。

MySQL提供了不同级别的设置，包括server级、database级、table级、column级，可以提供非常精准的设置。

什么是字符集、字符序？简单的来说：

  1. 字符集（character set）：定义了字符以及字符的编码。
  2. 字符序（collation）：定义了字符的比较规则。

举个例子：

有四个字符：A、B、a、b，这四个字符的编码分别是A = 0, B = 1, a = 2, b = 3。这里的字符 + 编码就构成了字符集（character set）。

如果我们想比较两个字符的大小呢？比如A、B，或者a、b，最直观的比较方式是采用它们的编码，比如因为0 < 1，所以 A < B。

另外，对于A、a，虽然它们编码不同，但我们觉得大小写字符应该是相等的，也就是说 A == a。

这上面定义了两条比较规则，这些比较规则的集合就是collation。

  1. 同样是大写字符、小写字符，则比较他们的编码大小；
  2. 如果两个字符为大小写关系，则它们相等。

三、MySQL支持的字符集、字符序

MySQL支持多种字符集 与 字符序。

  1. 一个字符集对应至少一种字符序（一般是1对多）。
  2. 两个不同的字符集不能有相同的字符序。
  3. 每个字符集都有默认的字符序。

上面说的比较抽象，我们看下后面几个小节就知道怎么回事了。

1、查看支持的字符集

可以通过以下方式查看MYSQL支持的字符集。

mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset  | Description                 | Default collation   | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |
| dec8     | DEC West European           | dec8_swedish_ci     |      1 |
...省略

mysql> use information_schema;
mysql> select * from CHARACTER_SETS;
+--------------------+----------------------+-----------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                 | MAXLEN |
+--------------------+----------------------+-----------------------------+--------+
| big5               | big5_chinese_ci      | Big5 Traditional Chinese    |      2 |
| dec8               | dec8_swedish_ci      | DEC West European           |      1 |
...省略

当使用SHOW CHARACTER SET查看时，也可以加上WHERE或LIKE限定条件。

例子一：使用WHERE限定条件。

mysql> SHOW CHARACTER SET WHERE Charset="utf8";
+---------+---------------+-------------------+--------+
| Charset | Description   | Default collation | Maxlen |
+---------+---------------+-------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci   |      3 |
+---------+---------------+-------------------+--------+
1 row in set (0.00 sec)

例子二：使用LIKE限定条件。

mysql> SHOW CHARACTER SET LIKE "utf8%";
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.00 sec)

2、查看支持的字符序

类似的，可以通过如下方式查看MYSQL支持的字符序。

方式一：通过SHOW COLLATION进行查看。

可以看到，utf8字符集有超过10种字符序。通过Default的值是否为Yes，判断是否默认的字符序。

mysql> SHOW COLLATION WHERE Charset = 'utf8';
+--------------------------+---------+-----+---------+----------+---------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
...略

方式二：查询information_schema.COLLATIONS。

mysql> USE information_schema;
mysql> SELECT * FROM COLLATIONS WHERE CHARACTER_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 |

3、字符序的命名规范

字符序的命名，以其对应的字符集作为前缀，如下所示。比如字符序utf8_general_ci，标明它是字符集utf8的字符序。

更多规则可以参考 官方文档。

MariaDB [information_schema]> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM COLLATIONS WHERE CHARACTER_SET_NAME="utf8" limit 2; 
+--------------------+-----------------+
| CHARACTER_SET_NAME | COLLATION_NAME  |
+--------------------+-----------------+
| utf8               | utf8_general_ci |
| utf8               | utf8_bin        |
+--------------------+-----------------+
2 rows in set (0.00 sec)

四、server的字符集、字符序

用途：当你创建数据库，且没有指定字符集、字符序时，server字符集、server字符序就会作为该数据库的默认字符集、排序规则。

如何指定：MySQL服务启动时，可通过命令行参数指定。也可以通过配置文件的变量指定。

server默认字符集、字符序：在MySQL编译的时候，通过编译参数指定。

character_set_server、collation_server分别对应server字符集、server字符序。

1、查看server字符集、字符序

分别对应character_set_server、collation_server两个系统变量。

mysql> SHOW VARIABLES LIKE "character_set_server";
mysql> SHOW VARIABLES LIKE "collation_server";

2、启动服务时指定

可以在MySQL服务启动时，指定server字符集、字符序。如不指定，默认的字符序分别为latin1、latin1_swedish_ci

mysqld --character-set-server=latin1 \
       --collation-server=latin1_swedish_ci

单独指定server字符集，此时，server字符序为latin1的默认字符序latin1_swedish_ci。

mysqld --character-set-server=latin1

3、配置文件指定

除了在命令行参数里指定，也可以在配置文件里指定，如下所示。

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

4、运行时修改

例子：运行时修改（重启后会失效，如果想要重启后保持不变，需要写进配置文件里）

mysql> SET character_set_server = utf8 ;

5、编译时指定默认字符集、字符序

character_set_server、collation_server的默认值，可以在MySQL编译时，通过编译选项指定：

cmake . -DDEFAULT_CHARSET=latin1 \
           -DDEFAULT_COLLATION=latin1_german1_ci

五、database的字符集、字符序

用途：指定数据库级别的字符集、字符序。同一个MySQL服务下的数据库，可以分别指定不同的字符集/字符序。

1、设置数据的字符集/字符序

可以在创建、修改数据库的时候，通过CHARACTER SET、COLLATE指定数据库的字符集、排序规则。

创建数据库：

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

修改数据库：

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

例子：创建数据库test_schema，字符集设置为utf8，此时默认的排序规则为utf8_general_ci。

CREATE DATABASE `test_schema` DEFAULT CHARACTER SET utf8;

2、查看数据库的字符集/字符序

有3种方式可以查看数据库的字符集/字符序。

例子一：查看test_schema的字符集、排序规则。（需要切换默认数据库）

mysql> use test_schema;
Database changed
mysql> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+
1 row in set (0.00 sec)

例子二：也可以通过下面命令查看test_schema的字符集、数据库（不需要切换默认数据库）

mysql> SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME  FROM information_schema.SCHEMATA WHERE schema_name="test_schema";
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| test_schema | utf8                       | utf8_general_ci        |
+-------------+----------------------------+------------------------+
1 row in set (0.00 sec)

例子三：也可以通过查看创建数据库的语句，来查看字符集。

mysql> SHOW CREATE DATABASE test_schema;
+-------------+----------------------------------------------------------------------+
| Database    | Create Database                                                      |
+-------------+----------------------------------------------------------------------+
| test_schema | CREATE DATABASE `test_schema` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-------------+----------------------------------------------------------------------+
1 row in set (0.00 sec)

3、database字符集、字符序是怎么确定的

  • 创建数据库时，指定了CHARACTER SET或COLLATE，则以对应的字符集、排序规则为准。
  • 创建数据库时，如果没有指定字符集、排序规则，则以character_set_server、collation_server为准。

六、table的字符集、字符序

创建表、修改表的语法如下，可通过CHARACTER SET、COLLATE设置字符集、字符序。

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

ALTER TABLE tbl_name
    [[DEFAULT] CHARACTER SET charset_name]
    [COLLATE collation_name]

1、创建table并指定字符集/字符序

例子如下，指定字符集为utf8，字符序则采用默认的。

CREATE TABLE `test_schema`.`test_table` (
  `id` INT NOT NULL COMMENT '',
  PRIMARY KEY (`id`)  COMMENT '')
DEFAULT CHARACTER SET = utf8;

2、查看table的字符集/字符序

同样，有3种方式可以查看table的字符集/字符序。

方式一：通过SHOW TABLE STATUS查看table状态，注意Collation为utf8_general_ci，对应的字符集为utf8。

MariaDB [blog]> SHOW TABLE STATUS FROM test_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-09 16:10:42
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

方式二：查看information_schema.TABLES的信息。

mysql> USE test_schema;
mysql> SELECT TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = "test_schema" AND TABLE_NAME = "test_table";
+-----------------+
| TABLE_COLLATION |
+-----------------+
| utf8_general_ci |
+-----------------+

方式三：通过SHOW CREATE TABLE确认。

mysql> SHOW CREATE TABLE test_table;
+------------+----------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                   |
+------------+----------------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3、table字符集、字符序如何确定

假设CHARACTER SET、COLLATE的值分别是charset_name、collation_name。如果创建table时：

  • 明确了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均未明确，则采用数据库的字符集、字符序设置。

七、column的字符集、排序

类型为CHAR、VARCHAR、TEXT的列，可以指定字符集/字符序，语法如下：

col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

1、新增column并指定字符集/排序规则

例子如下：（创建table类似）

mysql> ALTER TABLE test_table ADD COLUMN char_column VARCHAR(25) CHARACTER SET utf8;

2、查看column的字符集/字符序

例子如下：

mysql> SELECT CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA="test_schema" AND TABLE_NAME="test_table" AND COLUMN_NAME="char_column";
+--------------------+-----------------+
| CHARACTER_SET_NAME | COLLATION_NAME  |
+--------------------+-----------------+
| utf8               | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

3、column字符集/排序规则确定

假设CHARACTER SET、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的字符集、字符序为准。

八、选择：何时设置字符集、字符序

一般来说，可以在三个地方进行配置：

  1. 创建数据库的时候进行配置。
  2. mysql server启动的时候进行配置。
  3. 从源码编译mysql的时候，通过编译参数进行配置

1、方式一：创建数据库的时候进行配置

这种方式比较灵活，也比较保险，它不依赖于默认的字符集/字符序。当你创建数据库的时候指定字符集/字符序，后续创建table、column的时候，如果不特殊指定，会继承对应数据库的字符集/字符序。

CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

2、方式二：mysql server启动的时候进行配置

可以添加以下配置，这样mysql server启动的时候，会对character-set-server、collation-server进行配置。

当你通过mysql client创建database/table/column，且没有显示声明字符集/字符序，那么就会用character-set-server/collation-server作为默认的字符集/字符序。

另外，client、server连接时的字符集/字符序，还是需要通过SET NAMES进行设置。

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

3、方式三：从源码编译mysql的时候，通过编译参数进行设置

编译的时候如果指定了-DDEFAULT_CHARSET和-DDEFAULT_COLLATION，那么：

  • 创建database、table时，会将其作为默认的字符集/字符序。
  • client连接server时，会将其作为默认的字符集/字符序。（不用单独SET NAMES）
shell> cmake . -DDEFAULT_CHARSET=utf8 \
           -DDEFAULT_COLLATION=utf8_general_ci

九、写在后面

本文较为详细地介绍了MySQL中字符集、字符序相关的内容，这部分内容主要针对的是数据的存储与比较。其实还有很重要的一部分内容还没涉及：针对连接的字符集、字符序设置。

由于连接的字符集、字符序设置不当导致的乱码问题也非常多，这部分内容展开来讲内容也不少，放在下一篇文章进行讲解。

篇幅所限，有些内容没有细讲，感兴趣的同学欢迎交流，或者查看官方文档。如有错漏，敬请指出。

十、相关链接

10.1 Character Set Support
https://dev.mysql.com/doc/refman/5.7/en/charset.html


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK