MariaDB字符集和排序规则

共计 9555 个字符，预计需要花费 24 分钟才能阅读完成。

在 MariaDB 中，默认的字符集 character set 为 latin1，默认的排序规则为 latin1_swedish_ci(但不同的发行版可能会不同，例如 Debian)。字符集和排序规则都可以从 server 端一直指定到字段级别，client 连接到 server 时也可以指定。当修改字符集但却没有指定排序规则时，将总是使用字符集的默认排序规则。

字符集和排序规则总是级联向下的，所以当没有为字段指定排序规则时，将查找表的排序规则，同样对于表来说会上查到数据库，对数据库来说会上查到 server 级。因此，可以使用极细粒度的字符集和排序规则来控制控制你的数据。

每种字符集的默认排序规则可使用 SHOW COLLATION 语句查看，例如查找 latin2 字符集的默认排序规则：

SHOW COLLATION LIKE 'latin2%';
+---------------------+---------+----+---------+----------+---------+
| Collation           | Charset | Id | Default | Compiled | Sortlen |
+---------------------+---------+----+---------+----------+---------+
| latin2_czech_cs     | latin2  |  2 |         | Yes      |       4 |
| latin2_general_ci   | latin2  |  9 | Yes     | Yes      |       1 |
| latin2_hungarian_ci | latin2  | 21 |         | Yes      |       1 |
| latin2_croatian_ci  | latin2  | 27 |         | Yes      |       1 |
| latin2_bin          | latin2  | 77 |         | Yes      |       1 |
+---------------------+---------+----+---------+----------+---------+

Server 级别

可以设置系统变量 character_set_server 来改变默认的 server 级的字符集。该变量可以使用 SET 命令在启动时或动态地设置：

SET character_set_server = 'latin2';

类似地，变量 collation_server 用于设置 server 级别的默认排序规则。

SET collation_server = 'latin2_czech_cs';

Database 级别

CREATE DATABASE 和 ALTER DATABASE 语句中包含了可选的字符集、排序规则的设置子句。如果没有设置字符集、排序规则，它们将使用 server 级别的默认值。

CREATE DATABASE czech_slovak_names 
  CHARACTER SET = 'keybcs2'
  COLLATE = 'keybcs2_bin';
ALTER DATABASE czech_slovak_names COLLATE = 'keybcs2_general_ci';

使用下面的语句可以查看数据库所使用的字符集：

SHOW CREATE DATABASE czech_slovak_names;
+--------------------+--------------------------------------------------------------------------------+
| Database           | Create Database                                                                |
+--------------------+--------------------------------------------------------------------------------+
| czech_slovak_names | CREATE DATABASE `czech_slovak_names` /*!40100 DEFAULT CHARACTER SET keybcs2 */ |
+--------------------+--------------------------------------------------------------------------------+

或者，使用下面的语句可以查看各数据库采用的字符集和排序规则：

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | czech_slovak_names | keybcs2                    | keybcs2_general_ci     | NULL     |
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | latin1                     | latin1_swedish_ci      | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+

可以仅指定排序规则，由于每种排序规则都对应于一种字符集，因此会同时设置排序规则所对应的字符集。

CREATE DATABASE danish_names COLLATE 'utf8_danish_ci';

SHOW CREATE DATABASE danish_names;
+--------------+----------------------------------------------------------------------------------------------+
| Database     | Create Database                                                                              |
+--------------+----------------------------------------------------------------------------------------------+
| danish_names | CREATE DATABASE `danish_names` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_danish_ci */ |
+--------------+----------------------------------------------------------------------------------------------+

尽管可以动态地设置系统变量 character_set_database 和 collation_database，但它们用于确定数据库所使用的默认字符集和排序规则，应该尽量仅在 server 端进行设置。

Table 级别

CREATE TABLE 和 ALTER TABLE 语句支持可选的字符集、排序规则设置子句，它们是 MariaDB 和 MySQL 对标准 SQL 语句的扩展。

CREATE TABLE english_names (id INT, name VARCHAR(40)) 
  CHARACTER SET 'utf8' 
  COLLATE 'utf8_icelandic_ci';

如果既没有指定字符集也没有指定排序规则，则采用数据库的默认值。如果仅设置了字符集，将采用字符集的默认排序规则。如果仅设置了排序规则，则排序规则相关联的字符集也会被设置。

ALTER TABLE table_name
 CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

如果没有指定排序规则，将使用字符集默认的排序规则。

对于 VARCHAR 或 TEXT 类型的字段，为了保证新字段足够大以能够存储原字段的大量字符，CONVERT TO CHARACTER SET 可能会改变数据类型。

例如，某 TEXT 类型的字段存储 ascii 字符时由于每个字符仅占用一个字节，因此该字段可以存储 65,535 个字符。如果该字段转换为 UTF8，由于每个字符需要 3 个字节，该字段的数据类型将被转换为 MEDIUMTEXT 类型以便能够存储所有原字段的字符。

CONVERT TO CHARACTER SET binary 将分别转换 CHAR、VARCHAR 和 TEXT 字段为 BINARY、VARBINARY 和 BLOB，并且之后将不再具有字符集属性，或者可以在以后使用 CONVERT TO CHARACTER SET 语句来改变该行为。

为了避免 CONVERT TO CHARACTER SET 子句改变数据类型，可以在单独的字段上使用 MODIFY。例如：

ALTER TABLE table_name MODIFY ascii_text_column TEXT CHARACTER SET utf8;
ALTER TABLE table_name MODIFY ascii_varchar_column VARCHAR(M) CHARACTER SET utf8;

Column 级别

同样可以为字段类型为 CHAR、TEXT 或 VARCHAR 的字段设置字符集和排序规则。可以使用 CREATE TABLE 和 ALTER TABLE 语句进行设置——不像 table 级别的设置，column 级别的设置是标准 SQL 所支持的。

CREATE TABLE european_names (croatian_names VARCHAR(40) COLLATE 'cp1250_croatian_ci',
  greek_names VARCHAR(40) CHARACTER SET 'greek');

如果既没有指定字符集也没有指定排序规则，将使用表的默认值。如果仅设置了字符集，排序规则将使用字符集的默认排序规则，如果仅设置了排序规则，则其对应的字符集也会被设置。

当使用 ALTER TABLE 改变字段的字符集时，需要确保字符集可以和已有数据兼容。MariaDB 将尽可能地一一映射转换字符数据，但无法转换的数���可能会乱码丢失。可以使用 SHOW CREATE TABLE 语句，或者查询 INFORMATION_SCHEMA 数据库来查看字段的字符集和排序规则所采用的值。

SHOW CREATE TABLE european_names\G
*************************** 1. row ***************************
       Table: european_names
Create Table: CREATE TABLE `european_names` (`croatian_names` varchar(40) CHARACTER SET cp1250 COLLATE cp1250_croatian_ci DEFAULT NULL,
  `greek_names` varchar(40) CHARACTER SET greek DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE 'european%'\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: danish_names
              TABLE_NAME: european_names
             COLUMN_NAME: croatian_names
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 40
  CHARACTER_OCTET_LENGTH: 40
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: cp1250
          COLLATION_NAME: cp1250_croatian_ci
             COLUMN_TYPE: varchar(40)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
*************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: danish_names
              TABLE_NAME: european_names
             COLUMN_NAME: greek_names
        ORDINAL_POSITION: 2
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: YES
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 40
  CHARACTER_OCTET_LENGTH: 40
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: greek
          COLLATION_NAME: greek_general_ci
             COLUMN_TYPE: varchar(40)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:

文件名

从 MariaDB 5.1 开始，系统变量 character_set_filesystem 可用来控制对给定字符串的文件名解析。它会影响的下面语句和函数：

• SELECT INTO DUMPFILE
• SELECT INTO OUTFILE
• LOAD DATA INFILE
• LOAD XML
• LOAD_FILE()

字面符号 (译者注：可理解为常量、普通字符串或文本字符)

默认情况下，字符集和排序规则通过系统变量 character_set_connection 和 collation_connection 来决定使用的字面符号。但是可以显式地指定它们：

[_charset_name]'string' [COLLATE collation_name]

对于没有字符集 introducer 的字符集来说，它的字符串符号由系统变量 character_set_connection 决定。

该查询：

SELECT CHARSET('a'), @@character_set_connection;

总是会为两列返回相同的字符集名称。

character_set_client 和 character_set_connection 一般会被设置为相同的值 (例如在三次握手期间，或使用 SET NAMES 进行了设置)。但允许设置为不同值。

示例

设置 @@character_set_client 和 @@character_set_connection 为不同的值时可能很有用处：

示例 1：

假设我们在 utf8 的数据库中创建下面的表：

CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO t1 VALUES ('oe'),('ö');

现在使用 ”mysql.exe” 连接，它会使用 DOS 的字符集 (西欧的机器上是 cp850)，如果想要根据德国电话簿规则获取等于 ”ö” 的所有记录。

使用下面的语句：

SET @@character_set_client=cp850, @@character_set_connection=utf8; 
SELECT a FROM t1 WHERE a='ö' COLLATE utf8_german2_ci;

它将返回：

+------+
| a    |
+------+
| oe   |
| ö    |
+------+

工作方式如下：

1. 客户端使用 cp850 发送查询语句。
2. 服务端解析查询语句时，将把 ’ö’ 从 @@character_set_client (cp850) 转换为 @@character_set_connection (utf8) 的文本字符。
3. 服务端对该文本字符应用排序规则 ”utf8_germal2_ci”。
4. 服务端使用 utf8_german2_ci 进行字符比较。

注意，如果重写为如下脚本：

SET NAMES cp850;
SELECT a FROM t1 WHERE a='ö' COLLATE utf8_german2_ci;

将报错：

ERROR 1253 (42000): COLLATION 'utf8_german2_ci' is not valid for CHARACTER SET 'cp850'

因为：

• 在第 2 步中，将不会转换为 utf8 的文本字符，而是转换为 cp850 的文本字符。
• 在第 3 步中，服务端无法对 cp850 的字符串应用排序规则 utf8_german2_ci。

示例 2:

继续假设我们的数据库为 utf8，并使用西欧机器上的 ”mysql.exe” 进行连接。

我们这样做：

SET @@character_set_client=cp850, @@character_set_connection=utf8;
CREATE TABLE t2 AS SELECT 'ö';

这将会创建一张包含 VARCHAR(1) CHARACTER SET utf8 字段类型的表。

注意，如果查询重写为：

SET NAMES cp850;
CREATE TABLE t2 AS SELECT 'ö';

创建的表中的字段将为 VARCHAR(1) CHARACTER SET cp850 类型，这可能不是我们所期望的。

N

同样, 可以使用前缀 N 或 n 来转换文本字符为国际字符集 (MariaDB 中为 utf8)。

例如：

SELECT _latin2 'Müller';
+-----------+
| MĂźller   |
+-----------+
| MĂźller   |
+-----------+
SELECT CHARSET(N'a string');
+----------------------+
| CHARSET(N'a string') |
+----------------------+
| utf8                 |
+----------------------+
SELECT 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci';
+---------------------------------------------------+
| 'Mueller' = 'Müller' COLLATE 'latin1_german2_ci'  |
+---------------------------------------------------+
|                                                 1 |
+---------------------------------------------------+

存储过程和视图

当创建存储过程或视图时，其内出现的文本字符默认使用系统变量 character_set_connection 和 collation_connection 指定的字符集和排序规则。可以使用 SHOW CREATE 语句获取所使用的值。要改变已存在存储过程、视图中的文本字符的字符集，需要删除存储程序然后重建。

对于存储过程的参数和返回值，可以通过 CHARACTER SET 和 COLLATE 子句来指定其使用的字符集和排序规则。在 MariaDB 5.5 之前不支持指定排序规则。

下面的示例中展示了创建存储程序时所使用的字符集和排序规则。

SET @@local.character_set_connection='latin1';

DELIMITER ||
CREATE PROCEDURE `test`.`x`()
BEGIN
    SELECT CHARSET('x');
END;
||
Query OK, 0 rows affected (0.00 sec)

DELIMITER ;
SET @@local.character_set_connection='utf8';

CALL `test`.`x`();
+--------------+
| CHARSET('x') |
+--------------+
| latin1       |
+--------------+

下面的示例中展示了如何指定函数的参数和返回值的字符集和排序规则：

CREATE FUNCTION `test`.`y`(`str` TEXT CHARACTER SET utf8 COLLATE utf8_bin)
    RETURNS TEXT CHARACTER SET latin1 COLLATE latin1_bin
BEGIN
    SET @param_coll = COLLATION(`str`);
    RETURN `str`;
END;

-- 返回值的排序规则:
SELECT COLLATION(`test`.`y`('Hello, planet!'));
+-----------------------------------------+
| COLLATION(`test`.`y`('Hello, planet!')) |
+-----------------------------------------+
| latin1_bin                              |
+-----------------------------------------+

-- 参数的排序规则：
SELECT @param_coll;
+-------------+
| @param_coll |
+-------------+
| utf8_bin    |
+-------------+

示例：更改默认的字符集为 UTF-8

要改变默认的字符集 latin1 为 UTF-8，需要在配置文件 my.cnf 中进行如下设置：

[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
...

注意，选项 default-character-set 是一个客户端选项，而非服务端选项。

本文为 mariadb 官方手册：SETTING CHARACTER SETS AND COLLATIONS 的译文。

原文：https://mariadb.com/kb/en/setting-character-sets-and-collations/
我提交到 MariaDB 官方手册的译文：https://mariadb.com/kb/zh-cn/setting-character-sets-and-collations/

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-09/147119.htm