为什么你的 MySQL 数据库认为 “café” “café”

Source: Dev.to

问题

现在是凌晨 2:47。你正在生产环境中调试（是的，我们都有过这种经历）。巴黎的一位客户报告说他们找不到自己刚创建的餐厅 “Café Rouge”。

SELECT * FROM restaurants WHERE name = 'Café Rouge';

结果：Empty set（空集合）。

但数据确实在：

问题在于：数据库无法匹配你输入的字符串。

为什么编码很重要

字母 “é” 有多种编码方式：

对人类而言它们看起来相同，但对计算机来说却完全不同。如果用户输入一种形式，而数据库存储的是另一种形式，直接比较就会失败。

字符集

字符集 将符号映射到数字（编码）。常见的 MySQL 字符集：

• utf8mb4 – 完整的 UTF‑8 支持（4 字节字符、表情符号等）。
• latin1 – 西欧语言。
• ascii – 仅 7 位英文。

注意： MySQL 旧的 utf8 字符集最多只支持 3 字节字符，因而无法存储许多表情符号或罕见符号。

-- 这在插入表情符号时会失败
CREATE TABLE old_table (
    message VARCHAR(100) CHARACTER SET utf8
);

INSERT INTO old_table VALUES ('I love coding! 😍');
-- ERROR 1366: Incorrect string value

改用 utf8mb4：

CREATE TABLE modern_table (
    message VARCHAR(100) CHARACTER SET utf8mb4
);

INSERT INTO modern_table VALUES ('I love coding! 😍');
-- Success

校对规则（Collations）

如果字符集是字典，校对规则 就是比较的语法：

• 大小写敏感（A vs a）
• 重音敏感（café vs cafe）
• 多字符映射（ß vs ss）
• 排序规则（自然顺序）

MySQL 校对规则命名模式：charset_language_sensitivity

比较示例

SET @name1 = 'José';
SET @name2 = 'jose';
SET @name3 = 'José'; -- 不同的 é 编码

-- 二进制校对（字节比较）
SELECT @name1 = @name2 COLLATE utf8mb4_bin;          -- 0 (false)

-- 大小写不敏感校对
SELECT @name1 = @name2 COLLATE utf8mb4_general_ci; -- 1 (true)

-- 重音不敏感校对
SELECT 'José' = 'Jose' COLLATE utf8mb4_0900_ai_ci; -- 1 (true)

实际影响

唯一用户名

CREATE TABLE users (
    username VARCHAR(50) COLLATE utf8mb4_0900_as_cs
    -- as = accent sensitive, cs = case sensitive
);

INSERT INTO users VALUES ('José'), ('jose'), ('Jose');
-- 三条不同的记录

灵活搜索

CREATE TABLE search_terms (
    query VARCHAR(100) COLLATE utf8mb4_0900_ai_ci
);

SELECT * FROM products
WHERE name LIKE '%café%' COLLATE utf8mb4_0900_ai_ci;
-- 匹配 café、Café、CAFÉ、cafe、CAFE

真实案例：德语 “Müller”

某站点在 latin1 并使用二进制校对存储姓名。用户使用分解形式的 ü（u + ¨）输入 “Müller”，查询返回空结果。

解决办法：

ALTER TABLE users
MODIFY name VARCHAR(100)
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

表情符号错误

INSERT INTO posts (content) VALUES ('This is fire! 🔥');
-- ERROR 1366: Incorrect string value

预防措施： 确保表使用 utf8mb4。

性能考量

二进制校对更快，因为它直接比较原始字节。

-- 二进制校对 (≈0.05 s)
SELECT COUNT(*) FROM users
WHERE email = 'test@example.com' COLLATE utf8mb4_bin;

-- Unicode 校对 (≈0.12 s)
SELECT COUNT(*) FROM users
WHERE email = 'test@example.com' COLLATE utf8mb4_unicode_ci;

何时使用哪种校对

混合字符集与校对

MySQL 允许在不同层级（服务器、数据库、表、列、查询）使用不同设置。混用会导致错误：

CREATE DATABASE app
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci;

CREATE TABLE app.users (
    id INT PRIMARY KEY,
    email VARCHAR(255) CHARACTER SET latin1,   -- ⚠️
    username VARCHAR(100) COLLATE utf8mb4_bin -- ⚠️
);

SELECT * FROM users u1
JOIN users u2 ON u1.username = u2.email;
-- ERROR 1267: Illegal mix of collations

黄金法则： 为整个应用选择统一的字符集和校对规则（例如 utf8mb4 + utf8mb4_unicode_ci），并始终如一地使用。

推荐默认设置

CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

取证查询

检查字符串的编码

SELECT HEX('café');  -- 返回 636166C3A9（UTF‑8）

查看列的字符集和校对

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CHARACTER_SET_NAME,
    COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'your_table';

使用特定校对比较字符串

SELECT 'Müller' = 'Mueller' COLLATE utf8mb4_unicode_ci;

查找校对不匹配

SELECT DISTINCT COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database';
-- 理想情况下只返回一种或两种校对规则

迁移已有数据库

如果你的数据库已经使用了错误的字符集/校对：

1. 备份（永远要做）：

   mysqldump your_database > backup.sql

2. 转换表/列（单表示例）：

   ALTER TABLE your_table
   CONVERT TO CHARACTER SET utf8mb4
   COLLATE utf8mb4_unicode_ci;

3. 更新服务器默认值（在 my.cnf 中）：

   [mysqld]
   character-set-server = utf8mb4
   collation-server = utf8mb4_unicode_ci

4. 设置每个连接的字符集（如有需要）：

   SET NAMES utf8mb4;

迁移完成后，重新运行应用测试，确保所有查询行为符合预期。