字符集和排序规则

没有评论

字符集和排序规则

字符集是一组符号和编码，排序规则是一组用于比较字符集中字符的规则。

MySQL 8.0 版本，默认的字符集是 utf8mb4 ，默认的排序规则是 utf8mb4_0900_ai_ci 。

指定字符集和排序规则

字符集不仅影响数据的存储，还影响客户端与服务器端的通信。

CHARACTER SET 和 CHARSET 是同义词。

排序规则的特点

  • 不同字符集的排序规则不同

  • 每个字符集都有一个默认排序规则

  • 排序规则的名称通常以字符集名称开头，然后跟一个后缀，以此区分不同的排序规则，如 utf8mb4_0900_ai_ci

    选择排序规则时，应该对有代表性的数值进行比较，以确定使用哪种排序规则。

排序规则的命名

排序规则的名称通常以字符集名称开头，然后跟一个后缀，以此区分不同的排序规则，如 utf8mb4_0900_ai_ci。

特定于某种语言的排序规则包括语言环境代码或语言名称，如 utf8mb4_tr_0900_ai_ci ，tr 表示土耳其。

  • 字符集和排序规则

  • 排序规则后缀

    • 后缀的含义
      • _ai
        • Accent-insensitive，重音不敏感
      • _as
        • Accent-sensitive，重音敏感
      • _ci
        • Case-insensitive，大小写不敏感
      • _cs
        • Case-sensitive，大小写敏感
      • _ks
        • Kana-sensitive，假名敏感。用于区分日语中片假名和平假名。
      • _bin
        • Binary，二进制
    • 对于未指定重音敏感的非二进制排序规则，由大小写敏感确定
      • 名称中的 _ci _ai ，_cs _as，例如，latin1_general_ci，表示大小写不敏感，且不带重音。
    • 比较规则
      • 对于二进制字符集的二进制排序规则，比较基于数字字节值。
      • 对于非二进制字符集的 _bin 排序规则，比较基于数字字符代码值，该值不同于多字节字符的字节值
  • 对于 Unicode 字符集
    • 排序规则可以包括版本号，表示排序所基于的 Unicode 排序算法 (UCA) 的版本。名称中没有版本号的默认 UCA 版本是 4.0.0
      • utf8mb4_0900_ai_ci：基于 UCA 9.0.0 权重键
      • utf8mb4_unicode_520_ci：基于 UCA 5.2.0 权重键
      • utf8mb4_unicode_ci：基于 UCA 4.0.0 权重键
    • xxx_general_mysql500_ci 排序规则保留原始 xxx_general_ci 排序规则在 MySQL 5.1.24 之前的顺序，并允许对 5.1.24 之前版本创建的表进行升级 
      mysql> show collation where collation like '%mysql%';
+--------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+--------------------------+---------+-----+---------+----------+---------+---------------+
| ucs2_general_mysql500_ci | ucs2    | 159 |         | Yes      |       1 | PAD SPACE     |
| utf8_general_mysql500_ci | utf8    | 223 |         | Yes      |       1 | PAD SPACE     |
+--------------------------+---------+-----+---------+----------+---------+---------------+
  • 查看
    • SHOW 
      mysql> show character set where charset like 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_0900_ai_ci |      4 |
+---------+---------------+--------------------+--------+

mysql> show collation where charset = 'utf8mb4' and 1=1;
+----------------------------+---------+-----+---------+----------+---------+---------------+
| Collation                  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+---------+-----+---------+----------+---------+---------------+
| utf8mb4_0900_ai_ci         | utf8mb4 | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4 | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4 | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4 | 309 |         | Yes      |       1 | NO PAD        |
+----------------------------+---------+-----+---------+----------+---------+---------------+

      Note: 通过 SHOW 语句查询到结果和下面两个信息表的内容完全相同。

    • 信息表

      • INFORMATION_SCHEMA.CHARACTER_SETS
      • INFORMATION_SCHEMA.COLLATIONS

字符集和排序规则的级别

  • 服务器级的字符集和排序规则

    如果未在 CREATE DATABASE 语句中指定数据库字符集和排序规则，则将服务器字符集和排序规则用作默认值。

    • 选项
      • –character-set-server
        • 服务器字符集，默认值是 utf8mb4
      • –collation-server
        • 服务器排序规则，默认值是 utf8mb4_0900_ai_ci
    • 设置方法
      • 启动时在命令行中指定 
        mysqld
mysqld --character-set-server=utf8mb4
mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_0900_ai_ci

        Note: 如果未明确指定，会使用默认值。

      • 修改配置文件 /etc/my.cnf

        [mysqld]
character-set-server       = utf8mb4
collation-server           = utf8mb4_0900_ai_ci
      • 动态设置 
        mysql> set global character_set_server=utf8mb4;
mysql> set global collation_server=utf8mb4_0900_ai_ci;
      • 源码重新编译 
        cmake . -DDEFAULT_CHARSET=latin1
cmake . -DDEFAULT_CHARSET=latin1 -DDEFAULT_COLLATION=latin1_german1_ci
  • 数据库级的字符集和排序规则
    • 系统变量 
      USE db_name;
SELECT @@character_set_database, @@collation_database;

      Note: 这两个变量是没有默认值的，但是查询时显示数据库字符集是 utf8，不知道是从哪里继承过来的。也许是操作系统。

      • character_set_database
        • 数据库字符集。如果未指定，则使用服务器字符集。
        • 对于不包含 CHARACTER SET 子句的 LOAD DATA 语句，服务器使用此系统变量指示的字符集来解释文件中的信息
        • 对于存储例程 (存储过程和函数) ，如果声明中不包含字符集或排序规则，会使用数据库级的字符集和排序规则
      • collation_database
        • 数据库排序规则。如果未指定，则使用服务器排序规则。
    • 查看默认字符集
      • SHOW 
        mysql> show character set;
      • 信息表
        • INFORMATION_SCHEMA.SCHEMATA 
          mysql> select * from information_schema.schemata;
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| def          | mysql              | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | information_schema | utf8                       | utf8_general_ci        |     NULL | NO                 |
| def          | performance_schema | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | sys                | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | test               | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
        • INFORMATION_SCHEMA.CHARACTER_SETS 
          mysql> select * from information_schema.character_sets where character_set_name='utf8mb4';
+--------------------+----------------------+---------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION   | MAXLEN |
+--------------------+----------------------+---------------+--------+
| utf8mb4            | utf8mb4_0900_ai_ci   | UTF-8 Unicode |      4 |
+--------------------+----------------------+---------------+--------+
    • 设置方法
      • 语法 
        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]

        Note: 可以用 SCHEMA 代替 DATABASE，这两个词在 MySQL 中是同义词。

        • CHARACTER SET
        • COLLATE
      • 示例 
        mysql> create database zabbix character set utf8 collate=utf8_general_ci;
mysql> alter database zabbix character set utf8mb4 collate utf8mb4_0900_ai_ci;
    • 选择数据库字符集和排序规则的过程
      • 如果字符集和排序规则已指定，使用指定的
      • 如果字符集或排序规则中有一个未指定，根据已指定的那个选择一个默认值。每个字符集都对应一个默认的排序规则
      • 如果字符集和排序规则都未指定，则使用服务器级的字符集和排序规则
  • 表级的字符集和排序规则
    • 设置方法
      • 语法 
        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]
        • CHARACTER SET
        • COLLATE
      • 示例 
        mysql> create table t1(id int) character set utf8 collate utf8_general_ci;
    • 选择表级字符集和排序规则的过程
      • 如果字符集和排序规则都未指定，则使用数据库级的字符集和排序规则
  • 列级字符集和排序规则

    每个字符列 (CHAR, VARCHAR, TEXT, 其他同义词的列)，都有一个列字符集和排序规则。

    如果使用 ALTER TABLE 将列从一个字符集转换为另一个字符集，MySQL 会尝试映射数据值，但是如果字符集不兼容，则可能会丢失数据。

    • 设置方法
      • 语法 
        col_name {CHAR | VARCHAR | TEXT} (col_length)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]

col_name {ENUM | SET} (val_list)
    [CHARACTER SET charset_name]
    [COLLATE collation_name]
        • CHARACTER SET
        • COLLATE
      • 示例 
        mysql> create table t2(id int, name varchar(10) character set utf8mb4 collate utf8mb4_0900_ai_ci) character
set utf8 collate utf8_general_ci;
mysql> alter table t2 modify name varchar(10) character set utf8 collate utf8_general_ci;
    • 选择列级字符集和排序规则的过程
      • 如果字符集和排序规则都未指定，则使用表级的字符集和排序规则
  • 字符串文字的字符集和排序规则

    每个字符串文字都有一个字符集和排序规则。

    • 系统变量
      • character_set_connection
        • 连接字符集
        • 对于简单的 SELECT ‘string’ 语句，字符串文字将连接的字符集和排序规则作为默认字符集和排序规则
      • collation_connection
        • 连接排序规则
    • 设置方法
      • 语法 
        [_charset_name]'string' [COLLATE collation_name]

        Note: _charset_name 表达式，称为引入程序，它告诉解析器字符串所使用的字符集。引入程序在这里不会更改字符串，只是传递一个信号。

        • _charset_name
        • COLLATE
      • 示例 
        SELECT 'abc';
SELECT _latin1'abc';
SELECT _binary'abc';
SELECT _utf8mb4'abc' COLLATE utf8mb4_danish_ci;
    • 选择字符串文字字符集和排序规则的过程
      • 如果字符集未指定而排序规则指定了，则使用连接字符集 (character_set_connection) 作为字符集
      • 如果字符集和排序规则都未指定，则使用连接字符集和排序规则作为字符集和排序规则

国家字符集

  • 标准 SQL 将 NCHAR 或 NATIONAL CHAR 定义为指示 CHAR 列应使用某些预定义字符集的方式。MySQL 使用 utf8 作为此预定义字符集 
    CHAR(10) CHARACTER SET utf8
NATIONAL CHARACTER(10)
NCHAR(10)

    Note: 以上三种表示方法是等效的。

  • 示例

    • 在列中指定 
      VARCHAR(10) CHARACTER SET utf8  -- 好记
NATIONAL VARCHAR(10)
NVARCHAR(10)  -- 好记
NCHAR VARCHAR(10)
NATIONAL CHARACTER VARYING(10)
NATIONAL CHAR VARYING(10)

      Note: 这么多格式，记住两个好记的就可以了。

    • 字符串中指定

      SELECT N'some text';  -- 好记
SELECT n'some text';
SELECT _utf8'some text';

字符集引入程序

  • 字符串文字，十六进制文字或位值文字可以具有可选的字符集和排序规则

  • 语法

    [_charset_name] literal [COLLATE collation_name]
    • 对于字符串文字，允许在引入程序和字符串直接留有空格

    • 对于字符集文字，引入程序指示以下字符串的字符集，但不更改解析器在字符串内执行转义处理的方式。解析器始终根据 character_set_connection 给定的字符集解释转义符。

    • 可以使用 _binary 引入程序将字符串文字指定为二进制字符串。十六进制文字和位值文字默认情况下是二进制字符串，因此允许 _binary ，但通常不必要

      mysql> SET @v1 = X'000D' | X'0BC0';
mysql> SET @v2 = _binary X'000D' | X'0BC0';
mysql> SELECT HEX(@v1), HEX(@v2);
+----------+----------+
| HEX(@v1) | HEX(@v2) |
+----------+----------+
| BCD      | 0BCD     |
+----------+----------+
      • 对于两个位操作，显示的结果看起来都相似，但是不带 _binary 的结果是 BIGINT 值，而带 _binary 的结果是二进制字符串。 由于结果类型的不同，显示的值也有所不同：数字结果不显示高阶0位数

字符集和排序规则分配的示例

  • 表和列的定义 
    CREATE TABLE t1
(
    c1 CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci
) DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;

    Note: CHARACTER SET latin1 和 COLLATE latin1_german1_ci 这两个子句都是可选的。

  • 数据库、表和列的定义

    CREATE DATABASE d1
    DEFAULT CHARACTER SET latin2 COLLATE latin2_czech_ci;
USE d1;
CREATE TABLE t1
(
    c1 CHAR(10)
);

元数据的字符集 (UTF-8)

元数据的表现形式必须符合以下要求

为了满足这两个要求，MySQL 将元数据存储在 Unicode 字符集 (UTF-8) 。

USER(), CURRENT_USER(), SESSION_USER(), SYSTEM_USER(), DATABASE() 和 VERSION() 这些函数的返回值，默认字符集都是 UTF-8。

  • 所有元数据必须使用相同的字符集

    否则，INFORMATION_SCHEMA 中表的 SHOW 语句和 SELECT 语句都将无法正常工作，因为这些操作结果的同一列中的不同行将使用不同的字符集。

  • 元数据必须包含所有语言的所有字符

    否则，用户将无法使用自己的语言命名列和表。

系统变量

  • character_set_system

    • 元数据字符集
  • character_set_results
    • SELECT 语句返回结果的字符集。默认为 utf8mb4
    • 如果想让元数据返回不同的字符集结果，需要使用 SET NAMES 语句强制服务器进行字符集转换
    • 从服务器返回到客户端的错误消息将与元数据一样自动转换为客户端字符集
    • 使用函数在单个语句中进行比较或赋值时，MySQL 会自动进行转换
    • 变量值为 NULL 时，不执行任何转换，并且服务器使用原始字符集 (character_set_system) 返回元数据

连接字符集和排序规则

系统变量

  • character_set_server, collation_server
    • 服务器级别字符集和排序规则
  • character_set_database, collation_database
    • 数据库级别字符集和排序规则
  • character_set_client
    • 客户端在发送语句时使用的字符集。

    • 某些字符集在客户端是不允许的

      • ucs2, utf16, utf16le, utf32

      • 其他不允许这些字符集的语句

        --default-character-set=charset_name 选项
SET NAMES 'charset_name'
SET CHARACTER SET 'charset_name'
  • character_set_connection,collation_connection
    • 连接字符集和排序规则。
    • 服务器在接收到客户端发送的语句时，转换为此字符集和排序规则
  • character_set_results
    • 结果字符集。

    • 服务器将查询结果返回客户端之前，会转换为此字符集，包括数据、结果元数据 (列名) 和错误消息

    • 如果不想对结果进行转换，可以将此变量设置为 NULL 或 binary

      SET character_set_results = NULL;
SET character_set_results = binary;

查看相关变量

  • SHOW 
    mysql> show variables like 'character_set%';
+--------------------------+----------------------------------------------------------+
| Variable_name            | Value                                                    |
+--------------------------+----------------------------------------------------------+
| character_set_client     | utf8mb4                                                  |
| character_set_connection | utf8mb4                                                  |
| character_set_database   | utf8                                                     |
| character_set_filesystem | binary                                                   |
| character_set_results    | utf8mb4                                                  |
| character_set_server     | utf8mb4                                                  |
| character_set_system     | utf8                                                     |
| character_sets_dir       | /opt/mysql-8.0.23-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------+
mysql> show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8_general_ci    |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
  • performance_schema.session_variables 表 
    mysql> SELECT * FROM performance_schema.session_variables
WHERE VARIABLE_NAME IN (
'character_set_client', 'character_set_connection',
'character_set_results', 'collation_connection'
) ORDER BY VARIABLE_NAME;
+--------------------------+--------------------+
| VARIABLE_NAME            | VARIABLE_VALUE     |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_results    | utf8mb4            |
| collation_connection     | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+

配置客户端的连接字符集

  • 使用规则
    • 在没有其他信息的情况下，每个客户端都使用内置的默认字符集，通常是 utf8mb4。

    • 每个客户端都可以基于操作系统设置 (例如 Unix 系统上的 LANG 或 LC_ALL 语言环境设置环境变量的值或 Windows 系统上的代码页设置) 自动检测要使用的字符集。

      • 如果没有完全匹配，则 OS 字符集将映射到最接近的 MySQL 字符集。如果客户端不支持匹配的字符集，它将使用编译后的默认值
    • 选项
      • default-character-set 
        [mysql]
default-character-set=koi8r
        • 使用户可以显式指定字符集，以覆盖客户端以其他方式确定的默认设置。

        • 如果启用了自动重连，可以通过 charset 命令设置字符集

          mysql> charset koi8r

          Note: charset 命令发出 SET NAMES 语句，并且还更改了连接断开后 mysql 重新连接时使用的默认字符集。

  • 配置方法

    • SET NAMES 
      SET NAMES 'charset_name' [COLLATE 'collation_name']

      Note: 设置连接字符集或排序规则后，会隐式的设置对应的连接排序规则或字符集，所以连接字符集和排序规则中，只要显式的设置一个就够了。

      • 这个语句设置了一组字符集。它同时指定了客户端发送 SQL 语句到服务器所使用的字符集，以及服务器返回结果到客户端所使用的字符集

      • 等同于

        SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET character_set_connection = charset_name;
    • SET CHARACTER SET 
      SET CHARACTER SET 'charset_name'
      • 这个语句与 SET NAMES 类似，但它将连接字符集和排序规则 (character_set_connection 和 collation_connection) 设置为数据库级的字符集和排序规则 (character_set_database 和 collation_database)

      • 等同于

        SET character_set_client = charset_name;
SET character_set_results = charset_name;
SET collation_connection = @@collation_database;

连接字符集错误处理

  • 连接时的错误处理
    • 如果使用了客户端不支持的字符集或客户端无法识别的字符集，会报错

    • 如果使用了客户端可以识别但服务器无法识别的字符集，则服务器将退回到默认字符集和排序规则

      进一步的情况是，服务器使用可识别的字符集，但是服务器端不知道客户端使用的排序规则，则服务器将退回到默认字符集和排序规则。

  • 运行时的错误处理

    运行时可以通过 SET NAMES 或 SET CHARACTER SET 动态设置字符集。

    • 如果使用了客户端不支持的字符集或客户端无法识别的字符集，会报错

应用字符集和排序规则

建库时指定字符集

CREATE DATABASE mydb
  CHARACTER SET latin1
  COLLATE latin1_swedish_ci;
  • 如果要更改数据库字符集或排序规则，数据库中的存储例程 (存储过程和函数) 需要重建，以便它们使用新的默认值
  • 启动后需要使用 SET NAMES 设置连接字符集

启动时设置

[mysqld]
character-set-server=latin1
collation-server=latin1_swedish_ci
  • 启动后需要使用 SET NAMES 设置连接字符集

源码构建，在配置时指定

cmake . -DDEFAULT_CHARSET=latin1 \
  -DDEFAULT_COLLATION=latin1_swedish_ci
  • 不需要使用 SET NAMES 配置连接

web 中的设置

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

错误消息的字符集

用于构造错误消息的字符集

  • 消息模板使用 UTF-8 (utf8mb3)
  • 消息模板中的参数将替换为适用于特定错误发生的值
    • 表或列名之类的标识符在内部使用 UTF-8，因此可以照原样复制
    • 非二进制字符串值从其字符集转换为 UTF-8
    • 对于二进制字符串，在 0x20 到 0x7E 范围内的字节，将按原样复制。对于超出该范围的字节，使用 \x 十六进制编码

用于处理错误消息的字符集

  • 如果服务器将错误消息写入错误日志，它将按构造方式以 UTF-8 格式写入，而不转换为另一个字符集
  • 如果服务器将错误消息发送到客户端程序，则服务器会将错误消息从 UTF-8 转换为 character_set_results 系统变量指定的字符集
    • 如果变量值为 NULL 或二进制，则不会进行任何转换。
    • 如果变量值为 utf8mb3 或 utf8mb4 ，也不会进行任何转换，因为这些字符集的库包含了消息构造中使用的所有 UTF-8 字符
    • 如果无法在此变量中表示字符，则在转换过程中可能会发生一些编码。编码使用 Unicode 代码点值
      • 基本多语言面 (BMP) 范围 (0x0000 to 0xFFFF) 中的字符使用 \nnnn 表示法编写
      • BMP 范围之外的字符 (0x10000 to 0x10FFFF) 使用 +nnnnnn 表示法写入

列字符集转换

条件

  • 如果列具有二进制数据类型 (BINARY，VARBINARY，BLOB) ，则必须使用单个字符集 (将列转换为的字符集) 对它包含的所有值进行编码。如果使用二进制列将信息存储在多个字符集中，则 MySQL 无法知道哪些值使用哪个字符集，并且无法正确转换数据
  • 如果该列具有非二进制数据类型 (CHAR，VARCHAR，TEXT) ，则其内容应以列字符集编码。如果内容使用不同的字符集编码，则可以先将该列转换为使用二进制数据类型，然后再转换为非二进制列

设置

  • 更改单列的字符集 
    ALTER TABLE t MODIFY col1 VARCHAR(50) CHARACTER SET greek;
    • 可以由 VINARY(50) 转换为 CHAR(50)，但是结果值的末尾会填充 0x00 字节，可以使用 TRIM() 函数删掉 
      UPDATE t SET col1 = TRIM(TRAILING 0x00 FROM col1);
  • 更改表中所有列的字符集
    • 语法 
      ALTER TABLE ... CONVERT TO CHARACTER SET charset
    • 示例 
      mysql> alter table t2 convert to character set utf8mb4;

配置字符集

选项

  • –character-set-server
    • 服务器级字符集
  • –collation-server
    • 服务器级排序规则
  • –character-sets-dir
    • 字符集目录

查看

  • SHOW 
    mysql> show collation;
  • INFORMATION_SCHEMA.COLLATIONS 表

配置

  • 启动时设置

  • 编辑配置文件 /etc/my.cnf

    [client]
character-sets-dir=/usr/local/mysql/share/mysql/charsets
  • 重新编译

    对于 Unicode 字符集，可以使用 LDML 表示法定义排序规则，而无需重新编译。

  • 如果字符集是动态字符集，但是没有针对它的配置文件，则应该从新的 MySQL 发行版中安装字符集的配置文件

  • 如果字符集的索引文件 (Index.xml) 不包含该字符集的名称，则报错

    Character set 'charset_name' is not a compiled character set and is not
specified in the '/usr/share/mysql/charsets/Index.xml' file
    • 索引文件默认路径为：basedir/share/charsets/Index.xml

    • 要解决此问题，应该获取一个新的索引文件或将缺少的字符集的名称手动添加到当前文件中

      [client]
default-character-set=charset_name
      • 通常这是不必要的

        但是，如果 character_set_system 与 character_set_server 或 character_set_client 不同，并且手动输入了字符 (作为数据库对象标识符，列值或两者兼有) ，则这些字符可能在客户端的输出中显示不正确，或者输出本身的格式不正确。这时，可以设置 –default-character-set=system_character_set。

字符集的限制

  • 标识符使用 utf8 存储在 mysql 数据库表 (用户，数据库等) 中，但是标识符只能在基本多语言面 (BMP) 中包含字符。标识符中不允许使用补充字符

  • ucs2，utf16，utf16le 和 utf32 字符集具有以下限制

    • 它们都不能用作客户端字符集
    • 当前无法使用 LOAD DATA 加载使用这些字符集的数据文件
    • 无法在使用任何这些字符集的列上创建 FULLTEXT 索引。但是，可以在没有索引的列上执行 IN BOOLEAN MODE 搜索
  • REGEXP 和 RLIKE 运算符以字节方式工作，因此它们不是多字节、安全的，并且使用多字节字符集可能会产生意外的结果。另外，这些运算符按字节值比较字符，即使给定的排序规则将它们视为相等，重音字符也可能不相等

MySQL 服务器的语言环境支持

系统变量

  • lc_time_names

    • 控制用于显示日期和月份名称及缩写的语言 (语言环境)

    • 区域设置名称具有 IANA 列出的语言和区域子标签，例如，’ja_JP’ 或 ‘pt_BR’。默认值是 ‘en_US’，与系统语言环境无关。

    • 该变量的可用值可能与操作系统不同

      locale 列表：https://dev.mysql.com/doc/refman/8.0/en/locale-support.html

      • 常用的 locale：zh_CN、en_US

受影响的函数

  • DATE_FORMAT(), DAYNAME() 和 MONTHNAME() 
    mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.09 sec)

mysql> SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| en_US           |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT DAYNAME('2010-01-01'), MONTHNAME('2010-01-01');
+-----------------------+-------------------------+
| DAYNAME('2010-01-01') | MONTHNAME('2010-01-01') |
+-----------------------+-------------------------+
| Friday                | January                 |
+-----------------------+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2010-01-01','%W %a %M %b');
+-----------------------------------------+
| DATE_FORMAT('2010-01-01','%W %a %M %b') |
+-----------------------------------------+
| Friday Fri January Jan                  |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> SET lc_time_names = 'es_MX';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| es_MX           |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT DAYNAME('2010-01-01'), MONTHNAME('2010-01-01');
+-----------------------+-------------------------+
| DAYNAME('2010-01-01') | MONTHNAME('2010-01-01') |
+-----------------------+-------------------------+
| viernes               | enero                   |
+-----------------------+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_FORMAT('2010-01-01','%W %a %M %b');
+-----------------------------------------+
| DATE_FORMAT('2010-01-01','%W %a %M %b') |
+-----------------------------------------+
| viernes vie enero ene                   |
+-----------------------------------------+
1 row in set (0.00 sec)
  • 每个受影响的函数的日期或月份名称从 utf8 转换为 character_set_connection 系统变量指示的字符集

不受影响的函数

  • STR_TO_DATE() 或 GET_FORMAT()

  • FORMAT()

    该变量的值不影响 FORMAT() 函数的结果，但是此函数采用可选的第三个参数，该参数允许指定语言环境以用于结果数字的小数点，千位分隔符以及分隔符之间的分组。允许的语言环境值与该系统变量的值相同。

发表于：MySQL
2021-05-20
# MySQL# 字符集和排序规则
复制链接
英仔
版权声明：本站原创文章，由 英仔 2021-05-20发表，共计16008字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
MySQL 配置文件
mysqladmin-服务器管理程序
mysql 系统模式
mysqlpump-数据库备份程序
评论（没有评论）