mysql升级中的数据类型变化与适配问题

mysql 8.0 默认启用ONLY_FULL_GROUP_BY、utf8mb4字符集和严格布尔映射，导致GROUP BY报错、索引超限及ORM布尔值异常，需分别通过调整SQL语义、缩短索引前缀、显式声明TINYINT(1)并配置驱动参数适配。

MySQL 8.0 中 GROUP BY 默认行为变更导致的 select 报错

MySQL 5.7 允许 SELECT 列表中出现未在 GROUP BY 中声明、也未被聚合函数包裹的字段；8.0 默认启用 sql_mode=ONLY_FULL_GROUP_BY，直接报错 Expression #1 of SELECT list is not in GROUP BY clause。

这不是数据类型问题，但常被误认为“升级后 SQL 突然不跑”，实际是语义校验变严格。适配核心是让查询符合标准 SQL 语义：

• 检查所有含 GROUP BY 的 SQL，确认 SELECT 中每个非聚合字段都出现在 GROUP BY 子句里
• 避免用 SELECT * 配合 GROUP BY —— 即使 5.7 能过，结果也不确定
• 若业务依赖旧行为（如取分组内某行任意值），改用 ANY_VALUE(col) 显式包裹，例如：SELECT ANY_VALUE(name), count(*) FROM user GROUP BY dept_id
• 临时关闭该模式仅用于兼容过渡：SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));，但不推荐长期使用

TINYINT(1) 和布尔字段在 8.0+ 的实际存储与 ORM 映射风险

MySQL 从未真正支持布尔类型，boolEAN 或 BOOL 只是 TINYINT(1) 的别名。升级本身不改变存储，但客户端驱动和 ORM（如 Django、SQLAlchemy）可能因版本更新调整对 TINYINT(1) 的解释逻辑。

典型现象：应用升级 ORM 后，原本返回 True/False 的字段变成 1/0，或反向出错。

• 明确建表时不依赖别名：is_active TINYINT(1) default 0，而非 is_active Boolean，避免工具链歧义
• django 用户注意：2.0+ 默认将 TINYINT(1) 映射为 BooleanField，但若字段允许 NULL 或值范围超出 {0,1}，会出错；可强制指定 models.IntegerField() 并加 choices
• java JDBC 用户：检查 tinyInt1isBit 连接参数，默认 true 会让驱动把 TINYINT(1) 当作 BIT 处理，建议设为 false 并统一用 getInt() 读取

utf8mb4 字符集默认化引发的索引长度超限

MySQL 8.0 默认字符集从 utf8（实为 utf8mb3）升级为 utf8mb4，单字符最多占 4 字节。若表中已有 VARCHAR(255) 字段并建了前缀索引（如 INDEX idx_name (name(255))），升级后可能触发错误：Specified key was too long; max key Length is 3072 bytes。

根本原因是：InnoDB 单索引键最大长度为 3072 字节，utf8mb4 下 255 字符 × 4 字节 = 1020 字节 —— 表面看没问题，但若字段定义为 VARCHAR(1000) + 前缀索引 (col(768))，则 768 × 4 = 3072，刚好卡线；一旦加上其他索引列或开启 innodb_large_prefix=OFF（老配置），立刻失败。

• 检查现有索引长度：SELECT table_name, index_name, SUBSTR(index_columns, 1, 30) AS cols, seq_in_index, length FROM information_schema.statistics WHERE table_schema = 'your_db' ORDER BY length DESC LIMIT 10;
• 缩短前缀长度，例如将 INDEX (title(255)) 改为 INDEX (title(191))（191 × 4 = 764
• 确认 innodb_large_prefix 已启用（8.0 默认 ON），否则最大索引长度仅为 767 字节
• 避免在 TEXT/VARCHAR 上建全文索引以外的全字段索引 —— 既低效又易超限

jsON 字段的隐式类型转换与比较行为差异

MySQL 5.7 引入 json 类型，但 8.0 优化了其内部表示和比较逻辑。最易踩坑的是：用 = 比较两个 JSON 值时，5.7 会做宽松匹配（忽略对象键序、空格、数值精度），而 8.0 更严格，尤其在涉及浮点数时。

例如：SELECT '{"a": 1.0}' = '{"a": 1}' 在 5.7 返回 1，8.0 返回 0；再如 JSON_EXTRACT(json_col, '$.id') 返回的是 JSON 文本，不是原生数字，直接跟整数比较会触发隐式转换，行为不稳定。

• 比较 JSON 值时，统一用 JSON_CONTaiNS()、JSON_OVERLAPS() 或 JSON_EQ()（8.0.17+）等专用函数
• 提取后需转类型再比较：CAST(JSON_EXTRACT(data, '$.count') AS UNSIGNED)，而不是直接 JSON_EXTRACT(...) = 42
• 写入前验证 JSON 结构，避免因格式差异（如 true vs 1）导致后续查询不一致
• ORM 层（如 SQLAlchemy 1.4+）对 JSON 字段默认启用序列化/反序列化，确认是否启用了 json_serializer 和 json_deserializer 避免 double-encode

SELECT    id,   CAST(JSON_EXTRACT(metadata, '$.score') AS DECIMAL(5,2)) AS score_num,   JSON_CONTAINS(metadata, '{"status": "active"}') AS is_active FROM items WHERE CAST(JSON_EXTRACT(metadata, '$.score') AS DECIMAL(5,2)) > 85.5;

升级不是单纯换二进制的事。字符集、SQL 模式、JSON 解析、索引限制这些点，表面看不碰数据类型，实则处处影响字段如何被读、写、比较和索引。最容易被忽略的是 ORM 和驱动层对底层变更的响应延迟 —— 数据库升级了，应用代码没动，但连接参数或映射配置已经悄悄失效。