数据库字符集、排序规则与乱码实战：utf8mb4、Collation 选择与排查

-- ❌ utf8 存 emoji 的表现
INSERT INTO users(nickname) VALUES('👋');  -- 变成 '?' 或报错

-- ✅ utf8mb4 正常存储
INSERT INTO users(nickname) VALUES('👋');  -- 正常

-- 建库
CREATE DATABASE app DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 建表（列级/表级都显式声明，避免继承混乱）
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  nickname VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 连接层（关键：JDBC 显式指定）
jdbc:mysql://host/db?characterEncoding=UTF-8&connectionCollation=utf8mb4_unicode_ci

-- 同一个 'abc' 在不同 collation 下
SELECT 'A' = 'a' COLLATE utf8mb4_bin;        -- 0（区分大小写）
SELECT 'A' = 'a' COLLATE utf8mb4_general_ci; -- 1（不区分）

-- 按中文拼音排序：需要正确的 collation（如 utf8mb4_0900_ai_ci 对中文拼音有一定支持）
SELECT name FROM users ORDER BY name COLLATE utf8mb4_unicode_ci;
-- 若列是 general_ci，则 ORDER BY name 可能与界面期望的拼音序不一致

用户输入（👋）
  → HTTP 请求（UTF-8 bytes: F0 9F 91 8B）
  → 应用读取（按 utf-8 解码成字符串）
  → JDBC 连接（characterEncoding）
  → 数据库连接层（set names）
  → 存储到列（列 charset）
  → 返回读取
  → 页面/接口输出（Content-Type: charset=utf-8）

只要其中任何一层的字符集不是 utf8mb4/utf-8，就出现 ? / 乱码 / 丢字。

-- 1) 看列/表/库的字符集
SHOW CREATE TABLE users\G
SHOW FULL COLUMNS FROM users;

-- 2) 看当前连接字符集（重要！）
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- 3) 验证存储是否真的 4 字节 emoji
SELECT nickname, HEX(nickname) FROM users WHERE id=1;
-- 👋 的 UTF-8 应为 F0 9F 91 8B（4 字节）

character_set_client     ← 客户端发来的字节按什么解码
character_set_connection ← 服务端内部用什么做运算/比较
character_set_results    ← 结果返回给客户端按什么编码

三条原则：三者都要 utf8mb4；set names 一次统一

SET NAMES utf8mb4;   -- 等价于设置上面三个变量为 utf8mb4

-- 逐表转换（会锁表/重建，需评估窗口）
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 批量脚本思路：information_schema 拼 ALTER
SELECT CONCAT('ALTER TABLE ', table_name,
       ' CONVERT TO CHARACTER SET utf8mb4;')
FROM information_schema.tables
WHERE table_schema='app' AND table_collation NOT LIKE 'utf8mb4%';