MYSQL（一） - 字符集相关

2017/10/02 共 1739 字 5 分钟阅读
DATABASE mysql 字符集

Table of Content

AI 摘要: MySQL的字符集支持包括字符集和字符校对，可以在服务器、数据库、表和列级别指定。字符集和字符校对影响数据存储和通信，通过SHOW CHARACTER SET可以查看支持的字符集。配置MySQL字符集需要考虑环境，比如文件编码、终端窗口配置和Web环境脚本处理等。

MySQL 包括字符集支持，可以使用各种字符集(character)存储数据，并根据各种字符校对进行比较(collation)。可以在服务器，数据库，表和列级别指定字符集。

1. 讨论内容

什么是字符集 Character 和 Collations 字符校对？
如何指定字符集和字符校对

2. 什么是字符集和字符校对，哪些地方涉及到？

字符集问题不仅影响数据存储，还影响客户端程序和 MySQL 服务器之间的通信。我们通过SHOW CHARACTER SET；，可以查看到 Mysql 服务支持的字符集：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
...
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
...
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.01 sec)

那么到底什么是字符集 Character 以及字符校对 Collation？

2-1. 字符集是一组符号和编码

比如符号：A,B,a,b，其编码：A=0,B=1,a=2,b=3，A是一个符号，数字0是对A的编码，并且所有四个字母和它们的编码的组合组成了一个字符集。

2-2. 字符校对是一组用于比较字符集中字符的规则

比如我们要比较字符 A 和字符 B 大小，编码0表示 A 和编码1表示 B，因为 0 小于 1，我们说 A 小于 B，我们刚刚完成的是对我们的字符集应用字符校对。字符校对是一组规则（在这种情况下只有一个规则）：“比较编码大小。“我们将所有可能的归类中最简单的称为二进制字符校对Collation。

给定的字符集始终至少有一个字符校对，大多数字符集都有几个字符校对。要列出字符集的显示字符校对，可以：

查看INFORMATION_SCHEMA COLLATIONS表
基于SHOW COLLATION语句
- SHOW COLLATION WHERE Charset = 'utf8mb4';
- SHOW COLLATION LIKE '%utf8%';

2-3. 字符集在这些地方用到

要有效地使用这些功能，必须知道可用的字符集和字符校对（show character set），如何更改默认值以及它们如何影响字符串运算符和函数的行为。

使用各种字符集存储字符串；
使用各种字符校对比较字符串；
混合使用，在同一服务器，同一数据库甚至同一个表中混合使用不同字符集或字符校对的字符串；
在任何级别启用字符集和排序规范；

2-4. 字符集和字符校对的设置级别

四个级别：服务器，数据库，表和列。

3. 客户端程序连接字符集相关

一个连接是当它连接到服务器，开始在其内与服务器交互会话的客户端程序发出什么。客户端通过会话连接发送 SQL 语句，例如查询。服务器通过连接将响应（例如结果集或错误消息）发送回客户端。

查看所有字符集和整理系统变量：

1
2
3
// 会话级别字符集 & 字符集校对变量
SHOW SESSION VARIABLES LIKE 'character\_set\_%';
SHOW SESSION VARIABLES LIKE 'collation\_%';

3-1. 客户端程序连接字符集配置

字符集问题不仅影响数据存储，还影响客户端程序和 MySQL 服务器之间的通信。如果您希望客户端程序使用与默认字符集不同的字符集与服务器通信，则需要指明哪一个。

3-1-1. 通过 SET 操作（断开后重连需要重新 SET）

要使用 utf8 字符集，请在连接到服务器后发出以下语句：SET NAMES 'charset_name' [COLLATE 'collation_name']

1
SET NAMES 'utf8';

3-1-2. 通过 –default-character-set 设定

客户端都支持一个--default-character-set选项，该选项允许用户显式指定字符集以覆盖客户端否则确定的任何默认值
通过在my.cnf配置：

1
2
[mysql]
default-character-set=koi8r

3-1-3. 通过 charset 操作（断开后重连无需重新设置）

1
2
mysql> charset koi8r
Charset changed

3-2. 常见字符连接错误

服务端不被允许：ERROR 1231 (42000)
客户端无法识别的字符集：ERROR 2019 (HY000)

4. 服务端应用程序字符集和字符校对配置

客户端/服务器通信中配置应用程序使用的字符集和与字符集相关。默认 MySQL 字符集和字符校对为（latin1， latin1_swedish_ci）存储数据的应用程序，不需要特殊配置。

如果要改变默认的字符集以及字符校对，可以：

指定每个数据库的字符设置；（数据库级别的字符设定）
在服务器启动时指定字符设置；（服务级别的字符设定，影响后续应用程序）
从源构建 MySQL，配置时指定字符设置；（所有应用程序的默认设置，无需在服务器启动时指定它们）

4-1. 数据库字符集设定

1
2
3
4
// sql语句
CREATE DATABASE mydb
  CHARACTER SET utf8
  COLLATE utf8_general_ci;

使用数据库的应用程序还应在每次连接时配置与服务器的连接。这可以通过SET NAMES 'utf8' 在连接后执行语句来完成。无论连接方法如何（mysql 客户端，PHP 脚本等），都可以使用该语句。

4-2. Mysql 服务启动字符集设定

1
2
3
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

这些设置适用于服务器范围，并应用为任何应用程序创建的数据库的默认设置，以及在这些数据库中创建的表。

4-3. 编译 Mysql 服务设定

1
2
cmake . -DDEFAULT_CHARSET=utf8 \
  -DDEFAULT_COLLATION=utf8_general_ci

5. 小结

字符集为一组符号和编码的组合，字符校对是一组用于比较字符集中字符的规则，一个字符集可能存在多个字符校对规则；

无论如何配置 MySQL 字符集以供应用程序使用，还必须考虑这些应用程序执行的环境。例如:

如果将使用从在编辑器中创建的文件中获取的 UTF-8 文本发送 SQL 语句，则应编辑该文件，并将环境的语言环境设置为 UTF-8，以便文件编码正确，以便操作系统正确处理它。
如果使用 mysql 客户端从终端窗口中，窗口必须配置为使用 UTF-8 或字符可能无法正常显示。
对于在 Web 环境中执行的脚本，脚本必须正确处理与 MySQL 服务器交互的字符编码，并且必须生成正确指示编码的页面，以便浏览器知道如何显示页面内容。