翻译：MariaDB字符集和排序规则

2023-01-10 14:10:13

本文为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/

在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   |

| ö    |

+------+

工作方式如下：

客户端使用cp850发送查询语句。
服务端解析查询语句时，将把'ö'从@@character_set_client (cp850)转换为@@character_set_connection (utf8)的文本字符。
服务端对该文本字符应用排序规则"utf8_germal2_ci"。
服务端使用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是一个客户端选项，而非服务端选项。

码农公寓