本文详解 Python 使用 mysql-connector 执行多语句 SQL 时，因 CREATE TEMPORARY TABLE ... AS SELECT 与显式 CREATE TEMPORARY TABLE 的字符集继承机制不同，导致 JOIN 查询结果异常的问题，并提供可复用的修复方案。
本文详解 python 使用 mysql-connector 执行多语句 sql 时，因 `create temporary table ... as select` 与显式 `create temporary table` 的字符集继承机制不同，导致 join 查询结果异常的问题，并提供可复用的修复方案。
在使用 Python 操作 MySQL（如 mysql-connector-python 或 PyMySQL）时，若将建表与查询合并为单条多语句（multi=True）执行，与拆分为多个独立语句执行，可能得到完全不同的查询结果——这并非逻辑错误或代码 Bug，而是由 MySQL 临时表的字符集（character set）和排序规则（collation）隐式继承行为所致。
核心问题在于：
✅ CREATE TEMPORARY TABLE IF NOT EXISTS temp_pairs AS SELECT ...
→ 会严格继承 SELECT 子句中字段的字符集与 collation（通常为连接默认值，如 utf8mb4 / utf8mb4_general_ci）；
❌ CREATE TEMPORARY TABLE temp_pairs (nid INT, title VARCHAR(255))（无显式声明）
→ 则继承当前数据库（database=fanw）的默认字符集（本例中为 latin1），而 latin1 无法正确存储/比较含 Unicode 字符（如中文、emoji、重音字母等）的姓名数据，导致后续 LIKE 和 JOIN 匹配失败，结果为空或严重缺失。
验证这一点非常简单：执行以下 SQL 即可查看两张临时表的实际字符集：
SHOW CREATE TABLE temp_pairs; -- 在 all_in_one 中执行后查 -- 对比 separate 中建表后的同命令输出
✅ 正确做法：显式指定字符集与排序规则
在 separate() 函数中，应将建表语句改为：
cursor.execute("""
CREATE TEMPORARY TABLE IF NOT EXISTS temp_pairs (
nid INT(10) UNSIGNED NOT NULL,
title VARCHAR(255) NOT NULL
) ENGINE=InnoDB
CHARACTER SET = 'utf8mb4'
COLLATE = 'utf8mb4_general_ci';
""")
同时，确保插入前 cursor 连接已设置正确的客户端编码（推荐在连接参数中统一配置）：
conn = mysql.connector.connect(
host="localhost",
user="fanw",
password="secret",
database="fanw",
charset='utf8mb4', # ← 关键：强制客户端通信编码
collation='utf8mb4_general_ci'
)
? 额外优化建议：
- 避免手动循环 fetchall() 后再 executemany() 插入——既低效又易出错。可直接使用 cursor.executemany() 或更优的 cursor.executemany("INSERT ...", result)；
- result = cursor.fetchall() 返回即为 list[tuple]，无需二次遍历解包再构造新列表；
- 若数据量大，考虑使用 INSERT ... SELECT 替代中间临时表 + 多次插入，进一步减少网络与解析开销。
⚠️ 注意事项总结
| 场景 | 字符集来源 | 风险点 |
|---|---|---|
| CREATE ... AS SELECT | 继承 SELECT 字段元数据 | ✅ 安全（通常与源表一致） |
| CREATE TABLE (...)（无声明） | 继承数据库默认值 | ❌ 高危！尤其当 DB 默认为 latin1 时 |
| 连接未设 charset | 使用驱动默认（可能为 latin1） | ❌ 导致传输层乱码，BINARY 比较失效 |
最后，请始终通过 SHOW VARIABLES LIKE 'character_set%'; 和 SHOW VARIABLES LIKE 'collation%'; 核查 MySQL 服务端、数据库、连接三层次的编码配置，确保端到端统一为 utf8mb4 —— 这是现代 Web 应用处理国际化数据的基石。
修复后，separate() 函数将稳定输出 96，与 all_in_one() 完全一致。
Python免费学习笔记（深入）：立即使用
在学习笔记中，你将探索 Python 的核心概念和高级技巧！
