数据库基础体系 · 第 83/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

MySQL 数据类型、字符集与排序规则：精度、编码和索引影响

在 MySQL 中，列定义并不只是“允许存什么值”的声明。一个列的数据类型决定值如何解释、占用多少空间以及如何进行算术运算；字符集决定字符如何编码为字节；排序规则决定字符如何比较、排序和参与唯一性判断。三者最终都会影响：

• 输入值是否能无损保存；
• 表达式计算是否精确；
• =、ORDER BY、LIKE 和 GROUP BY 的语义；
• 唯一索引是否允许两个看似不同的字符串同时存在；
• 索引能否被使用，以及索引需要多大；
• 连接、复制、导入导出和跨系统交换数据时是否发生转换。

可以先用一个简化模型理解它们之间的关系：

应用字符
  --连接字符集编码-->
传输字节
  --列字符集转换-->
列中保存的字节
  --列排序规则解释-->
比较键（用于 =、ORDER BY、索引查找）

数值类型通常直接决定值域与精度；字符串类型则需要同时考虑“字符”和“字节”两个维度。

---

一、先区分三个概念：数据类型、字符集、排序规则

1. 数据类型决定值的表示与运算

例如：

price DECIMAL(10, 2)
count BIGINT UNSIGNED
created_at DATETIME(6)
payload JSON

这里：

• DECIMAL(10, 2) 表示精确数值，最多 10 位有效十进制数字，其中 2 位在小数点后；
• BIGINT UNSIGNED 表示无符号 64 位整数；
• DATETIME(6) 保存最多 6 位小数秒；
• JSON 按 JSON 文档规则验证和存储数据。

类型还影响隐式转换。例如把字符串列与数字常量比较时，MySQL 可能需要把字符串转换为数值；如果字符串中含有非数字内容，这种转换可能产生警告、错误或意外匹配。

2. 字符集决定字符如何编码

字符集是“字符到字节序列”的映射。例如 utf8mb4 能表示完整 Unicode 范围，一个字符最多使用 4 个字节；latin1 的可表示范围则小得多。

字符集不等于“语言”，也不等于“排序规则”：

字符集：如何编码和解码字符
排序规则：这些字符如何比较、排序、折叠大小写或重音

同一个字符集可以有多种排序规则，例如：

utf8mb4_bin
utf8mb4_0900_ai_ci
utf8mb4_0900_as_cs

3. 排序规则决定比较语义

排序规则通常由名称中的后缀表达部分行为：

• ai：accent-insensitive，通常不区分重音；
• as：accent-sensitive，区分重音；
• ci：case-insensitive，不区分大小写；
• cs：case-sensitive，区分大小写；
• bin：按编码后的二进制值比较。

因此，在某个不区分大小写的排序规则下：

'a' = 'A'

可能为真；在二进制或大小写敏感排序规则下则可能为假。

这不只是 ORDER BY 的表现。排序规则会参与：

• =、<>、<、>;
• GROUP BY 和 DISTINCT；
• UNIQUE 索引；
• 前缀索引的等价判断；
• 部分 LIKE 匹配；
• 字符串函数和表达式的结果类型推导。

---

二、数值类型：范围、精度与溢出行为

2.1 整数类型的范围是存储约束，不是显示格式

MySQL 的整数类型包括：

类型	有符号范围	无符号范围	存储大小
TINYINT	-128 到 127	0 到 255	1 字节
SMALLINT	-32768 到 32767	0 到 65535	2 字节
MEDIUMINT	-8388608 到 8388607	0 到 16777215	3 字节
INT	-2³¹ 到 2³¹-1	0 到 2³²-1	4 字节
BIGINT	-2⁶³ 到 2⁶³-1	0 到 2⁶⁴-1	8 字节

UNSIGNED 扩大的是非负值范围，并不是“让负数自动变成正数”。

旧版本中常见的写法：

id INT(11)

其中 11 不是存储 11 位数字，也不改变取值范围。它曾经与显示宽度有关，但显示宽度不应作为容量设计依据；在 MySQL 8.x 中这类显示宽度语义已经被弃用。需要固定宽度展示时，应在应用层格式化，或使用 ZEROFILL 等兼容性明确的机制，但不应把它误认为数值范围。

BOOLEAN 和 BOOL 在 MySQL 中是 TINYINT(1) 的别名。它们不会自动形成严格的布尔值域，除非通过约束或应用逻辑限制为 0 和 1。

2.2 DECIMAL 与浮点类型解决的是不同问题

DECIMAL(M,D) 保存精确的十进制数：

• M 是总有效数字位数；
• D 是小数位数；
• 整数位数为 M-D；
• M 最大为 65，D 不能大于 M，具体可用范围还受版本语义约束。

例如：

CREATE TABLE account_entry (
    id BIGINT UNSIGNED PRIMARY KEY,
    amount DECIMAL(12, 2) NOT NULL
) ENGINE = InnoDB;

DECIMAL(12,2) 最多保存 10 位整数和 2 位小数，例如：

9999999999.99

但它不能保存 3 位小数而不发生舍入或错误。例如：

INSERT INTO account_entry VALUES (1, 12.345);

在严格 SQL 模式下，超出定义精度通常会报错；非严格模式下可能发生截断并产生 warning。生产环境应检查 SQL 模式和 warning，而不能只看语句是否返回成功。

浮点类型 FLOAT 和 DOUBLE 使用近似二进制浮点表示。许多十进制小数无法被有限长度的二进制小数精确表示，因此：

SELECT 0.1 + 0.2 = 0.3;

不能被当作可靠的财务精度测试。浮点类型适合测量值、科学计算或对误差有明确容忍度的场景；金额、税率累计、账务余额通常应使用 DECIMAL 或以最小货币单位保存的整数。

需要注意：DECIMAL 的“精确”是相对于其定义精度和小数位而言。它不能表示超出列定义的数字，也不意味着任意表达式都不会发生类型转换或舍入。

2.3 溢出是数据契约问题

下面的列只能保存 0 到 255：

CREATE TABLE demo_unsigned (
    value TINYINT UNSIGNED NOT NULL
) ENGINE = InnoDB;

在严格模式下：

INSERT INTO demo_unsigned VALUES (256);

通常会因超出范围而失败。非严格模式可能将值截断到边界并产生 warning。两种模式都不是“自动修复业务数据”；正确做法是让列类型范围覆盖业务不变量，并在应用和数据库两端处理非法输入。

---

三、时间类型：是否带时区，以及精度保存在哪里

MySQL 常见时间类型的关键差异如下：

类型	时区转换	小数秒
DATE	无	无
TIME	无	可指定精度
DATETIME	无	可指定精度
TIMESTAMP	按会话时区转换	可指定精度

3.1 DATETIME 保存字面时间，TIMESTAMP 会进行时区转换

DATETIME 保存类似：

2025-01-01 12:00:00

它本身不携带时区，也不会因为会话时区变化而转换。

TIMESTAMP 的典型行为是：

1. 客户端以当前会话时区发送时间；
2. MySQL 将其转换为内部表示；
3. 查询时再从内部表示转换为当前会话时区；
4. 不同会话时区可能看到不同的墙上时间。

因此：

• 表示“全球同一时刻”的事件，常使用 UTC 语义的 TIMESTAMP，或由应用统一保存 UTC 的 DATETIME；
• 表示“某地日历上的预约时间”，不能只依靠 TIMESTAMP，还需要保存业务时区或使用明确的时区模型。

3.2 小数秒是列定义的一部分

CREATE TABLE event_log (
    id BIGINT UNSIGNED PRIMARY KEY,
    occurred_at DATETIME(6) NOT NULL
) ENGINE = InnoDB;

DATETIME(6) 最多保存微秒级小数秒。若列定义为 DATETIME，输入中的小数秒不会因为客户端显示出来就自动被保存。

精度设计应同时考虑：

• 业务是否需要微秒；
• 索引和排序是否需要区分同一秒内的事件；
• 数据源是否真的提供该精度；
• 日志、复制和导出格式是否能保留它。

---

四、字符串类型：字符长度、字节长度和实际占用

4.1 CHAR 与 VARCHAR

CHAR(N) 和 VARCHAR(N) 的 N 对非二进制字符串通常按字符数理解，但实际存储和索引空间仍取决于字符集编码。

例如：

CREATE TABLE user_profile (
    nickname VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL
) ENGINE = InnoDB;

这里最多 100 个字符，但若每个字符最多占 4 个字节，列内容本身最多可能需要约 400 字节，另加长度信息和行格式开销。

两者的主要语义差异：

• CHAR(N) 是定长语义，读取时通常会去除末尾填充空格；
• VARCHAR(N) 是变长语义，保存实际长度并带长度信息；
• VARCHAR 并不意味着一定节省空间，具体还受行格式、平均长度、更新模式和存储引擎影响。

CHAR 适合长度稳定的短值，例如某些固定格式代码；VARCHAR 适合长度差异明显的文本。

4.2 TEXT 不是“无限长度字符串”

TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT 具有不同最大长度级别，但实际还受：

• 字符集的最大字节数；
• 行格式；
• 最大数据包；
• 内存和临时表；
• 存储引擎限制；
• SQL 语句或客户端协议限制。

TEXT 列可以建立前缀索引，但不能像普通 VARCHAR 一样直接定义完整长度的普通索引。若业务需要高选择性检索、唯一性或排序，通常应重新评估列类型，或把可检索的规范化值存入独立列。

4.3 二进制字符串不能按文本字符串理解

BINARY、VARBINARY 和 BLOB 保存字节，不进行字符集解码。适合：

• 哈希摘要；
• 加密密文；
• 协议数据；
• 不应被文本规则解释的标识。

例如 SHA-256 摘要可以保存为：

digest BINARY(32) NOT NULL

而不是保存 64 个十六进制字符。后者可读性更好，但占用更多空间。

BINARY 和 VARBINARY 的比较按字节进行，不适用大小写折叠、重音忽略等文本排序规则。二进制值末尾的 0x00 也不能等同于文本中的空格。

---

五、字符集的作用域与字符数据流

MySQL 的字符集和排序规则存在多个作用域：

1. 服务器默认值；
2. 数据库默认值；
3. 表默认值；
4. 列定义；
5. 连接和客户端会话；
6. 字符串字面量或表达式显式指定的值。

后者覆盖前者。例如：

CREATE DATABASE app
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

CREATE TABLE app.user_account (
    username VARCHAR(100)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_0900_as_cs
        NOT NULL
) ENGINE = InnoDB;

数据库默认值只会作为新表的默认值；表默认值只会作为未显式声明列的默认值。它们不会自动修改已经存在的列。

5.1 推荐明确使用 utf8mb4

在 MySQL 语境中，utf8 历史上通常指 utf8mb3，最多支持 3 字节 UTF-8 子集，不能表示所有 Unicode 字符，例如许多 Emoji。需要完整 Unicode 时，应显式使用：

CHARACTER SET utf8mb4

不要把“UTF-8”这个通用名称与 MySQL 的 utf8 别名混为一谈。utf8mb3 在现代 MySQL 中已被弃用，版本升级时还应关注相关兼容性变化。

5.2 连接字符集决定输入输出如何转换

一个典型的数据流是：

客户端字节
  -> character_set_client 解码
  -> MySQL 内部字符串
  -> 转换到列字符集
  -> 存储

查询结果则大致反向转换到结果字符集。

可以查看当前连接设置：

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

常见变量包括：

• character_set_client：客户端发送语句使用的字符集；
• character_set_connection：字符串字面量解析和连接表达式使用的字符集；
• character_set_results：结果返回给客户端使用的字符集；
• collation_connection：连接表达式的默认排序规则。

SET NAMES utf8mb4 会同时设置一组连接字符集相关变量。更稳妥的生产方式是使用驱动程序明确配置连接字符集，并验证驱动没有在连接建立后覆盖设置。

如果客户端把 UTF-8 字节错误声明为 latin1，服务器看到的不是“编码不完整”，而是另一组字符；错误数据一旦存入列，后续再改变连接设置不会自动恢复原始字节。

---

六、排序规则：比较键、大小写、重音与尾随空格

6.1 排序规则不是简单的 ASCII 排序

对文本进行比较时，排序规则可以把原始字符串映射为用于比较的权重序列。抽象地表示：

compare(s1, s2, collation)
  = compare(weight(s1), weight(s2))

在不区分大小写的排序规则中，A 和 a 可能映射到相同或等价权重；在不区分重音的排序规则中，带重音字符也可能与基础字符等价。

这解释了一个容易忽略的事实：

CREATE TABLE names (
    name VARCHAR(50)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_0900_ai_ci
        NOT NULL,
    UNIQUE KEY uk_name (name)
) ENGINE = InnoDB;

在该排序规则的等价规则下，'Alice' 和 'alice' 可能被认为相同，因此第二条记录可能违反唯一索引。唯一索引保证的是“按该列排序规则定义的唯一”，不是按原始字节唯一。

若业务要求大小写敏感，应该选择明确的大小写敏感排序规则，例如：

name VARCHAR(50)
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_as_cs

若业务要求完全按编码字节区分，可考虑 utf8mb4_bin，但它的排序顺序是编码值顺序，并不等于所有语言环境下的自然语言排序。

6.2 尾随空格不是所有场景都相同

非二进制字符串排序规则可能具有 PAD SPACE 或 NO PAD 属性：

• PAD SPACE：比较时通常忽略末尾空格；
• NO PAD：末尾空格参与比较。

因此，不能简单地说“字符串比较总是忽略尾随空格”。还需要结合：

• 列是 CHAR 还是 VARCHAR；
• 使用的排序规则；
• 是否是二进制字符串；
• MySQL 版本和具体类型语义。

可以检查排序规则属性：

SELECT COLLATION_NAME, PAD_ATTRIBUTE
FROM INFORMATION_SCHEMA.COLLATIONS
WHERE COLLATION_NAME IN (
    'utf8mb4_0900_ai_ci',
    'utf8mb4_bin'
);

在数据模型中，如果尾随空格具有业务意义，不应依赖默认排序规则的细节来表达这种意义，而应保存规范化后的明确值或使用二进制语义。

6.3 排序规则冲突会导致错误或隐式转换

例如两个字符串表达式使用不兼容的字符集或排序规则时，MySQL 可能报：

Illegal mix of collations

也可能根据表达式的推导规则选择一个排序规则。表达式的排序规则通常受以下因素影响：

• 列的显式排序规则；
• 字符串字面量的排序规则；
• 连接排序规则；
• 字符串是否通过 COLLATE 显式指定；
• 表达式来源的“强制性”或 coercibility。

诊断时可直接查看表达式的字符集和排序规则：

SELECT
    CHARSET('abc'),
    COLLATION('abc'),
    CHARSET(_utf8mb4'abc' COLLATE utf8mb4_bin),
    COLLATION(_utf8mb4'abc' COLLATE utf8mb4_bin);

显式转换可以解决局部语义问题：

SELECT *
FROM user_account
WHERE username COLLATE utf8mb4_bin = _utf8mb4'Alice';

但对索引列使用表达式或运行时转换，可能使普通索引不能按原始列方式访问。更好的做法通常是统一列定义和参数绑定的字符集、排序规则。

---

七、字符集与索引：字节上限、比较语义和访问路径

7.1 InnoDB 索引长度按字节计算

VARCHAR(255) 并不等于索引最多占用 255 字节。

对于 utf8mb4，单个字符最多 4 个字节，因此最坏情况下：

255 个字符 × 4 字节/字符 = 1020 字节

这还没有计算索引记录的额外开销，也没有考虑排序规则权重、索引其他列和主键长度。

现代 InnoDB 表使用 DYNAMIC 或相关现代行格式时，单个索引键长度上限通常为 3072 字节；较旧的 COMPACT 或 REDUNDANT 行格式常见上限为 767 字节。实际限制还受页大小、行格式、列数量和版本配置影响，不能只根据字符数推算。

可以检查表和列定义：

SHOW CREATE TABLE user_account\G

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME,
    CHARACTER_MAXIMUM_LENGTH,
    CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'user_account';

CHARACTER_MAXIMUM_LENGTH 是字符数；CHARACTER_OCTET_LENGTH 是最大字节数。两者不同正是多字节字符集影响索引设计的表现。

7.2 前缀索引按字符或字节需要特别核对

例如：

CREATE TABLE article (
    id BIGINT UNSIGNED PRIMARY KEY,
    title VARCHAR(500)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_0900_ai_ci
        NOT NULL,
    INDEX ix_title (title(100))
) ENGINE = InnoDB;

title(100) 表示只索引标题的前缀，但设计时必须确认语法和版本对字符列前缀长度的解释，并用实际建表结果验证索引定义。前缀索引的核心代价是：索引只看前缀，后续字符不能帮助索引区分值。

因此：

"database-design-a"
"database-design-b"

若前缀只覆盖 "database-design-"，它们在索引中会产生相同前缀。查询仍可能使用索引定位候选行，但必须回表检查完整值；如果前缀选择性很低，性能会变差。

对 UNIQUE KEY 使用前缀索引尤其危险：

UNIQUE KEY uk_title (title(100))

它保证的是“前 100 个字符按该排序规则唯一”，而不是完整字符串唯一。两个前缀相同但后续不同的标题也不能同时存在。

7.3 排序规则会影响唯一索引和索引选择性

在不区分大小写、重音不敏感的排序规则下，多个不同字节字符串可能属于同一比较等价类。例如：

Alice
alice

可能等价。于是：

• 唯一索引可容纳的值减少；
• 统计分布与字节去重结果不同；
• 前缀索引的选择性可能低于预期；
• ORDER BY 的顺序不一定反映原始字节顺序。

索引的查找条件不是简单的“字节前缀匹配”，而是由列排序规则定义的字符串比较。优化器还会结合统计信息、条件选择性、索引顺序和回表代价决定是否使用索引。

7.4 索引能用，不等于结果无需回表

考虑：

SELECT id, username
FROM user_account
WHERE username = 'Alice';

若 username 上有同排序规则的普通索引，优化器可以使用索引查找等价范围。但在大小写不敏感排序规则下，索引可能找到与 Alice 等价的多个候选值。是否需要回表，取决于查询是否覆盖索引、列值是否已能在索引记录中完成判断，以及存储引擎执行计划。

可以用：

EXPLAIN
SELECT id, username
FROM user_account
WHERE username = 'Alice';

重点查看：

• key 是否选择了预期索引；
• type 是否为 const、ref、range 等可用访问方式；
• rows 估计扫描行数；
• Extra 是否出现 Using index 等信息。

不要仅凭“列上有索引”判断查询一定高效。

---

八、字符串比较与查询条件中的隐式转换

8.1 同一列上的字面量最好保持相同字符语义

以下查询通常最容易获得稳定的语义：

SELECT id
FROM user_account
WHERE username = ?;

应用通过参数绑定传入字符串，并让驱动连接使用 utf8mb4。相比把值拼接到 SQL 中，参数绑定还可以避免 SQL 注入和不必要的字面量解析差异。

如果显式使用不同排序规则：

SELECT id
FROM user_account
WHERE username COLLATE utf8mb4_bin = _utf8mb4'Alice';

这表示本次比较采用二进制排序规则，而不是列原有排序规则。它可能改变结果集合，并且可能让普通索引无法直接按原排序规则完成查找；是否仍能通过索引优化，需要用 EXPLAIN 和实际版本验证。

8.2 数值列不要用字符串语义比较

假设：

CREATE TABLE product (
    id BIGINT UNSIGNED PRIMARY KEY,
    product_no VARCHAR(32) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    KEY ix_product_no (product_no),
    KEY ix_amount (amount)
) ENGINE = InnoDB;

下面两个条件的业务含义不同：

WHERE product_no = 100
WHERE amount = '100.00'

product_no 是字符串标识，100 可能触发字符串到数值或数值到字符串的转换；如果列中存在 '100A'、'0100' 等值，结果可能与直觉不同，并可能产生 warning。amount 是精确数值，字符串字面量若能转换为数值，通常会按数值比较，但应用仍应绑定正确类型并避免依赖隐式转换。

对于索引列，隐式转换可能使条件变成对列逐行转换，从而降低索引使用能力。诊断步骤是：

EXPLAIN FORMAT=TRADITIONAL
SELECT *
FROM product
WHERE product_no = 100;

然后与绑定字符串参数或显式字符串字面量的执行计划和结果比较。

---

九、其他常见数据类型与本主题的边界

9.1 ENUM 和 SET

ENUM 保存一个预定义成员，SET 保存零个或多个成员。它们不是普通字符串的简单别名：

status ENUM('pending', 'paid', 'cancelled') NOT NULL

优点是值域集中在列定义中；代价是新增或调整成员涉及 DDL、发布顺序和跨版本兼容性。不要把 ENUM 的内部数值位置当作稳定业务编码，也不要用数字字面量随意写入，否则容易产生按枚举序号解释的歧义。

如果值域会经常变化，或者需要多语言显示、权限元数据和生命周期管理，独立字典表通常更容易演进。

9.2 JSON

JSON 类型会验证输入是否为合法 JSON，并支持 JSON 操作函数。它适合结构半固定、字段演进较频繁或需要保留原始文档的场景，但不能替代所有关系模型设计。

如果经常按某个 JSON 路径过滤或排序，应考虑把该值提取为生成列并建立索引：

CREATE TABLE order_doc (
    id BIGINT UNSIGNED PRIMARY KEY,
    doc JSON NOT NULL,
    customer_id BIGINT
        GENERATED ALWAYS AS (
            CAST(JSON_UNQUOTE(JSON_EXTRACT(doc, '$.customer_id')) AS UNSIGNED)
        ) STORED,
    KEY ix_customer_id (customer_id)
) ENGINE = InnoDB;

这里的关键不是“JSON 自动可索引”，而是将稳定的查询键显式物化为具有确定数据类型的列，再建立普通索引。生成表达式必须满足 MySQL 对生成列和索引的限制，实际建表后应检查 SHOW CREATE TABLE。

9.3 空间类型

空间类型如 POINT、GEOMETRY 有自己的坐标参考系、几何有效性和空间索引语义，不能按普通文本或数字类型理解。空间索引解决的是空间关系的候选筛选，最终仍可能需要精确几何判断。若业务涉及地理距离和坐标，必须同时明确 SRID、坐标单位和函数语义。

---

十、完整示例：同一字段在字符集、排序规则和索引下的不同结果

下面的示例使用 InnoDB，表操作默认在事务中执行；CREATE TABLE 属于 DDL，具体提交和隐式提交行为应按 MySQL 版本的 DDL 语义处理。

DROP TABLE IF EXISTS account_name;

CREATE TABLE account_name (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name_ci VARCHAR(50)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_0900_ai_ci
        NOT NULL,
    name_cs VARCHAR(50)
        CHARACTER SET utf8mb4
        COLLATE utf8mb4_0900_as_cs
        NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_name_ci (name_ci),
    KEY ix_name_cs (name_cs)
) ENGINE = InnoDB;

插入第一行：

INSERT INTO account_name (name_ci, name_cs)
VALUES ('Alice', 'Alice');

再插入：

INSERT INTO account_name (name_ci, name_cs)
VALUES ('alice', 'alice');

可能出现的结果是：

• name_ci 的唯一索引拒绝第二行，因为该排序规则不区分大小写；
• 如果删除 uk_name_ci，name_cs 则可能允许两行，因为它区分大小写。

验证比较语义：

SELECT
    'Alice' COLLATE utf8mb4_0900_ai_ci =
    'alice' COLLATE utf8mb4_0900_ai_ci AS ci_equal,
    'Alice' COLLATE utf8mb4_0900_as_cs =
    'alice' COLLATE utf8mb4_0900_as_cs AS cs_equal;

预期通常是：

ci_equal = 1
cs_equal = 0

但“预期”仍应以实际服务器版本和排序规则定义为准，不应只从名称猜测所有 Unicode 字符的等价关系。

检查索引和列元数据：

SHOW CREATE TABLE account_name\G

SELECT
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME,
    CHARACTER_MAXIMUM_LENGTH,
    CHARACTER_OCTET_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'account_name';

最后查看查询计划：

EXPLAIN
SELECT id
FROM account_name
WHERE name_cs = 'Alice';

这里字符串字面量会参与排序规则推导。实际应用中，最好由驱动以参数方式传递，并统一连接字符集，避免客户端默认配置改变比较语义。

---

十一、如何诊断“乱码、查不到、重复或索引失效”

1. 先确认四层元数据

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SHOW CREATE DATABASE your_db;
SHOW CREATE TABLE your_table\G

然后检查具体列：

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME,
    IS_NULLABLE,
    COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_db'
  AND TABLE_NAME = 'your_table';

重点确认：

• 客户端连接字符集是否为 utf8mb4；
• 数据库默认值是否只是默认值，还是列已经显式指定了其他值；
• 发生问题的列是否使用了不同排序规则；
• 类型是否实际为 TEXT、VARCHAR、VARBINARY 或其他类型；
• 数值列是否带有 UNSIGNED 和预期精度。

2. 区分“存入错误”和“查询比较错误”

如果数据显示为乱码，先判断原始字节是否已经错误保存。可以检查：

SELECT
    name,
    HEX(name),
    CHARSET(name),
    COLLATION(name)
FROM user_account
LIMIT 10;

• HEX(name) 可帮助确认列中实际保存的字节；
• CHARSET(name) 和 COLLATION(name) 反映表达式语义；
• 修改连接字符集只能影响后续传输和解析，不能自动修复已经错误保存的数据。

3. 查不到记录时检查比较等价类

“肉眼不同”不代表排序规则认为不同；“肉眼相同”也不代表字节相同。应分别测试：

SELECT
    name,
    HEX(name),
    name = _utf8mb4'Alice' COLLATE utf8mb4_0900_ai_ci AS equal_ci,
    name = _utf8mb4'Alice' COLLATE utf8mb4_bin AS equal_bin
FROM user_account
WHERE id = 1;

这能把“业务希望的唯一性”与“当前列定义提供的唯一性”区分开。

4. 索引问题要看执行计划和实际参数

对以下因素逐项检查：

• 条件是否对索引列调用了函数或 COLLATE；
• 参数字符集是否触发了转换；
• 列与参数是否发生数值/字符串隐式转换；
• 前缀索引是否选择性不足；
• 查询是否带有前导 %，如 LIKE '%abc'；
• 统计信息是否反映当前数据分布；
• 索引是否因长度、排序规则或行格式限制未按预期创建。

EXPLAIN 只能说明优化器选出的计划；最终仍需结合实际返回行数、执行时间和生产数据分布验证。

---

十二、设计时真正需要作出的取舍

1. 金额选择精度，而不是“看起来够大”的浮点类型

若值需要可复算、可对账、可审计，使用 DECIMAL 并明确总位数和小数位。若金额单位固定，也可以使用整数保存分、厘等最小单位，但必须在领域模型中固定单位，不能让不同服务各自解释。

2. 标识符先确定比较规则，再确定类型

需要大小写不敏感唯一的用户名，应明确使用不区分大小写的排序规则；需要大小写敏感的 API token、哈希或外部编码，通常应采用二进制存储或二进制排序语义。不能在建表后才发现唯一索引已经把两个业务上不同的值视为相同。

3. 文本列长度与索引长度必须一起设计

VARCHAR(1000) 的“1000”是字符数，不是索引字节数。使用 utf8mb4 后，应估算最坏字节数，并考虑复合索引中其他列和 InnoDB 主键带来的额外空间。超过索引限制时，不应机械地缩短字符长度；应判断是否适合前缀索引、摘要列、生成列或独立搜索系统。

4. 默认值只能降低误配概率，不能替代列级契约

数据库和表级默认字符集很重要：

CREATE DATABASE app
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

但关键列仍应在设计评审中明确其字符集和排序规则。尤其是唯一键、外部标识、登录名和搜索字段，不能依赖服务器默认值随版本或部署环境变化。

5. 规范化应服务于明确的比较需求

例如业务要求“邮箱按 ASCII 大小写不敏感唯一”，可以设计一个规范化列并对其建立唯一索引；业务要求“显示原始输入”，则同时保存原始列和规范化列。不要在每次查询中临时调用 LOWER()、CONVERT() 或 COLLATE，再期待普通索引始终有效。

---

数据类型回答“值是什么以及如何计算”，字符集回答“字符如何成为字节”，排序规则回答“这些字符何时被认为相等、如何排序”。索引则把这些语义固化为可快速访问的结构：它保存的不是脱离类型和排序规则的纯字符串，而是由列定义解释后的索引键。

因此，表设计中最危险的不是单独选错一个类型，而是让以下三件事彼此不一致：

业务上的精度与唯一性
数据库列的类型与排序规则
客户端传输和查询时的字符语义

只有三者一致，精确数值、完整编码和可预测索引行为才会同时成立。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：OLTP、OLAP 与 Lakehouse：工作负载、存储布局和数据链路选型
• 下一篇：MySQL 表设计：主键、行格式、NULL、生成列、分区与归档
• 延伸：MySQL 架构全景：连接层、优化器、执行器、存储引擎与日志

官方资料

• MySQL 8.4 Reference Manual
• MySQL InnoDB Storage Engine

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。