Mysql | 字符集和校对规则

筑梦调音师
205 阅读7分钟

问题发生

项目发布后出现告警，查看错误日志：Illegal mix of collations (utf8_general_ci,IMPLICIT), (utf8mb4_general_ci,COERCIBLE), (utf8mb4_general_ci,COERCIBLE) for operation ' IN '

问题分析

初步分析

遇到问题不用慌，先简单分析下业务场景和影响范围，再考虑是否要回滚

  1. 首先分析错误日志在代码中的情况，确认该错误是由于查询数据库异常影响的
  2. 接着分析刚刚发布的内容：消费其他业务方的消息，然后查询消息在数据库是否存在
  3. 再分析业务场景：因为是刚消费新的消息，业务还没推广，对用户没有影响
  4. 决策：可以先不回滚，继续排查问题

详细分析

业务逻辑

查询SQL

select * from e where e in ("MY🇲🇾 II","Ineligible");

表结构

CREATE TABLE `e` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `identifier` varchar(255) NOT NULL DEFAULT '' COMMENT '',
  `created_at` int(11) NOT NULL DEFAULT '0',
  `updated_at` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uq_identifier` (`identifier`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

遇事不决问GPT

问题发生在执行 SQL 查询时，涉及到字符集和校对规则的冲突

根本原因

  1. 表的字符集和校对规则：

    1. 表 e 中的 identifier 字段使用了 utf8 字符集和 utf8_general_ci 校对规则，这是从表结构的默认字符集 DEFAULT CHARSET=utf8 推断出来的。

  2. 查询中的字符串字面量：

    1. 查询中的字符串 " MY🇲🇾 II" 包含了一个四字节的 Unicode 字符（国旗表情），这需要 utf8mb4 字符集来存储。由于这个字符串不符合 utf8 字符集（只支持最多三字节的 Unicode 字符），MySQL 自动使用了 utf8mb4 字符集和 utf8mb4_general_ci 校对规则来处理这个字符串。
    2. 当 MySQL 尝试将 utf8mb4_general_ci 校对的字符串与 utf8_general_ci 校对的列进行比较时，发生了校对规则不匹配的错误。

解决方案

  1. 修改表的字符集和校对规则：
    1. 将 identifier 字段的字符集和校对规则从 utf8 改为 utf8mb4。这不仅解决当前的问题，还能确保字段可以存储任何 Unicode 字符，包括表情符号等。
    2. SQL 迁移命令
ALTER TABLE e MODIFY identifier VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  1. 在查询中显式指定校对规则：
    1. 如果不想改变表结构，可以在查询中显式指定使用 utf8_general_ci 校对规则。但注意，这种方法不支持四字节字符，如你的国旗表情，可能会导致数据损失或查询错误。
    2. SQL 查询调整：
SELECT * FROM e WHERE identifier IN ('MY🇲🇾 II' COLLATE utf8_general_ci, 'Ineligible' COLLATE utf8_general_ci);
  1. 推荐方案：

推荐将 identifier 字段及整个表的字符集升级到 utf8mb4，这是因为 utf8mb4 是完整的 UTF-8 支持，能处理任何 Unicode 字符。这样可以避免未来因字符集限制导致的问题，同时提高数据库的国际化和兼容性。

知识拓展

在数据库系统中，特别是在MySQL中，字符集和校对规则（Collation）是处理文本数据的两个基本概念。它们对于数据的存储、检索以及比较操作都至关重要。

字符集（Character Set）

字符集定义了一组字符以及每个字符对应的编码。字符集决定了数据库可以存储哪些字符以及如何存储这些字符。例如，ASCII 字符集只包括英文字符和一些特殊字符，而 UTF-8 字符集则可以包含几乎所有国际语言的字符，包括表情符号等。 MySQL中常见的字符集包括：

  • latin1：西欧编码，支持大多数西欧语言。
  • utf8：一个字节到三字节的变长字符编码，兼容ASCII，能编码多种语言文本。
  • utf8mb4：utf8的超集，支持最多四个字节的UTF-8编码，可以存储任何Unicode字符，包括表情符号。

校对规则（Collation）

校对规则定义了字符集内字符的比较和排序规则。校对规则不仅涉及字符的字面比较，还可能包括对大小写敏感性、重音符号的处理等方面的规定。这使得在不同语言和区域环境中的文本比较成为可能。

例如，对于英文字符，a 和 A 在不区分大小写的校对规则下是相等的，但在区分大小写的校对规则下则不相等。

MySQL中常见的校对规则包括：

  • utf8_general_ci：utf8 字符集的不区分大小写的校对规则。
  • utf8_bin：utf8 字符集的二进制校对规则，严格基于字符的二进制值进行比较。
  • utf8_unicode_ci：utf8 字符集的基于Unicode权威标准的不区分大小写的校对规则，比 utf8_general_ci 更精确，但性能可能略低。
  • utf8mb4_0900_ai_ci：utf8mb4 字符集的Unicode 9.0标准的不区分大小写的校对规则。

使用场景

  • 数据存储：根据应用场景选择合适的字符集确保可以存储所有必要的字符。
  • 数据检索：使用合适的校对规则可以确保查询结果的正确性，例如在不区分大小写的环境中搜索数据。
  • 国际化：多语言应用需选择支持广泛语言的字符集和校对规则，如 utf8mb4。

设置和查看

在MySQL中，你可以在创建数据库或表时指定默认的字符集和校对规则，也可以为单独的列指定。查询当前的字符集和校对规则设置可以使用 SHOW VARIABLES LIKE 'character_set%' 和 SHOW VARIABLES LIKE 'collation%' 命令。 正确理解和使用字符集与校对规则对于开发支持国际化的应用程序、处理多种语言数据非常关键，也有助于提高数据库查询的准确性和效率。

字符集和校对规则区别

一个字符集可以使用不同的校对规则（collations）。在数据库中，字符集和校对规则是两个相互关联但独立的概念：

字符集（Character Set）

字符集决定了数据库可以存储哪些字符以及如何存储这些字符。它定义了一组字符以及每个字符对应的编码。字符集是必须的，因为它告诉数据库如何解释存储在表中的字节数据。

校对规则（Collation）

校对规则则定义了如何比较字符集中的字符，包括如何处理字符的大小写敏感性、重音符号、以及特定语言的字符排序规则等。校对规则是可选的，如果没有明确指定，数据库将使用字符集的默认校对规则。

示例

以 utf8mb4 字符集为例，它支持多种校对规则，这些校对规则影响着字符串比较和排序的行为。例如：

  • utf8mb4_general_ci：一种通用的、不区分大小写的校对规则。
  • utf8mb4_unicode_ci：基于Unicode标准的不区分大小写的校对，提供对多种语言的更好支持。
  • utf8mb4_bin：二进制校对规则，严格按照字符的二进制值进行比较，区分大小写。

选择适当的校对规则

选择哪种校对规则取决于具体需求。例如，如果你的应用需要在排序和比较时区分大小写，则可能会选择一个二进制的校对规则（如 utf8mb4_bin）。如果你的应用主要处理英文数据，并且不需要区分大小写，则可能会选择 utf8mb4_general_ci。如果你的数据库需要支持多种语言并提供较为准确的文字排序，可能会选择 utf8mb4_unicode_ci。

  • 创建数据库时指定校对规则：
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • 创建表时指定校对规则：
CREATE TABLE mytable (
    id INT,
    text VARCHAR(100) COLLATE utf8mb4_bin
) CHARACTER SET utf8mb4;

总之，一个字符集可以有多个校对规则，允许不同的数据列以不同的方式处理字符串比较和排序，以满足不同的应用需求。这种灵活性是数据库设计中的一个重要方面，可以帮助优化数据操作和提高应用性能。

avatar
文章
阅读
粉丝