MySQL中怎样设置字符集

这期内容当中小编将会给大家带来有关MySQL中怎样设置字符集，文章内容丰富且以专业的角度为大家分析和叙述，阅读完这篇文章希望大家可以有所收获。

一、内容概述

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

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

1. 鸿蒙官方战略合作共建——HarmonyOS技术社区

2. 字符集、字符序的基本概念及联系

3. MySQL 支持的字符集、字符序设置级，各设置级别之间的联系

4. server、database、table、column 级字符集、字符序的查看及设置

5. 应该何时设置字符集、字符序

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

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

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

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

1. 鸿蒙官方战略合作共建——HarmonyOS技术社区

2. 字符集（character set）：定义了字符以及字符的编码。

3. 字符序（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. 鸿蒙官方战略合作共建——HarmonyOS技术社区

2. 同样是大写字符、小写字符，则比较他们的编码大小；

3. 如果两个字符为大小写关系，则它们相等。

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

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

1. 鸿蒙官方战略合作共建——HarmonyOS技术社区

2. 一个字符集对应至少一种字符序（一般是 1 对多）。

3. 两个不同的字符集不能有相同的字符序。

4. 每个字符集都有默认的字符序。

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

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_ser    ver分别对应 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

用途：指定数据库级别的字符集、字符序。同一个 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> 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)

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

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

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

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

创建数据库的时候进行配置。

mysql server 启动的时候进行配置。

从源码编译 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中怎样设置字符集了，如果刚好有类似的疑惑，不妨参照上述分析进行理解。如果想知道更多相关知识，欢迎关注亿速云行业资讯频道。