数据类型与建表：一次选对，十年少加班

建表时类型和字符集选错的代价，往往要等数据量上来、索引失效、迁移报警那天才暴露。本章按"选类型三原则 → 每类类型的边界与坑 → 字符集/sql_mode → 建表规范 → 隐式转换"的顺序，把 MySQL 8.4 LTS 下的结构决策讲清楚。

示例表与 02-SQL基础.md 保持同一套电商 schema；InnoDB 页、行格式的细节在 01-核心概念与架构.md，索引怎么用类型在 04-索引原理与实战.md。

一、选类型三原则

1. 够用就小。 字段类型决定行宽，行宽决定一个 16KB 数据页能装多少行，装得少则同样的查询扫更多页、Buffer Pool 里挤占更多缓存。TINYINT 能表达状态就别开 INT，VARCHAR(32) 够用就别写 VARCHAR(1024)。索引列的宽度还直接乘在二级索引和回表代价上。

2. 尽量 NOT NULL。 可空列要占记录头的 null bitmap 位；=NULL 永远为 unknown、<> 会漏掉 NULL 行（02-SQL基础.md 第十一节），应用层每个判空都是一次翻车机会；优化器对可空列的统计和索引选择也更保守。真正"语义上未知"的列（比如 referrer_id）才给 NULL；“空字符串表示没有"和"0 表示未设置"这类场景，用 NOT NULL + 显式默认值更干净。

3. 别让 TEXT/BLOB 满天飞。 大字段行外存储、拉宽单次 IO，SELECT * 会把它们的溢出页一并拖出来；能 VARCHAR 就 VARCHAR，真要大文本就垂直拆表（正文表 / 扩展表），主表保持窄。

原则二的 NULL 代价常被低估，两个日常查询就能说明：

SELECT COUNT(city), COUNT(*) FROM users;    -- 两个数的差值就是 NULL 行数
SELECT * FROM users WHERE city <> '杭州';   -- city IS NULL 的行永远被漏掉

记录头的 null bitmap 开销是小钱，大钱是比较语义反直觉、统计口径说不清，以及"当初可空、后来想改 NOT NULL"时 ALTER 与应用判空逻辑的全线返工。

场景速查（生产里的默认选择）

二、数值类型

整数全家福

注意 INT 的 UNSIGNED 上限 4294967295 看着大，但自增主键、按"次/毫秒"累计的计数很容易被业务量打爆；溢出时报 1264 Out of range。主键统一 BIGINT 是成本最低的保险。

显示宽度与 ZEROFILL： INT(11)、ZEROFILL 里的数字只是展示格式，与存储和取值范围无关（INT(1) 照样存 21 亿），8.0.17 起官方弃用，新表别再写 TINYINT(1) 之外的宽度。历史包袱里 BOOL/BOOLEAN 实际就是 TINYINT(1)，读写时按 0/1 语义即可。

UNSIGNED 取舍： 取值天然非负（主键、数量、ID）就用 UNSIGNED，范围翻倍；代价是永远存不进负数，与外部系统对账字段、可能被"逻辑删除减一"之类的操作污染的列要三思。

DECIMAL：钱必须放的地方

SELECT CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE);
-- 0.30000000000000004 —— 二进制浮点表示不了 0.1，误差就此产生
SELECT 0.1 + 0.2;
-- 0.3：字面量按精确十进制运算，MySQL 里"看起来没事"，落进 DOUBLE 列就现原形

浮点是近似类型，等值比较、聚合累计都会漂；钱和计量的存储必须是精确的十进制：DECIMAL(M, D)，M 总位数（≤65）、D 小数位（≤30，且 D≤M），每 9 位数字压 4 字节。金额惯例 DECIMAL(10,2)，上限 99999999.99；超了就 DECIMAL(12,2) 起步，不要拿 FLOAT 凑。

另一条主流路线是 BIGINT 存"分”：避免小数点参与网络协议/JSON 序列化（很多语言的浮点陷阱源自传输层），账务系统常见。两种都好，比 FLOAT 强；选哪种跟团队协议层习惯走，全库统一。

-- FLOAT 存钱的经典失现场景（二进制无法精确表示 0.1）：
CREATE TABLE bad_price (p FLOAT);
INSERT INTO bad_price VALUES (19.99);
SELECT p * 3 FROM bad_price;        -- 约 59.969999，聚合/结算按分取整时会错
DROP TABLE bad_price;

FLOAT / DOUBLE / BIT

FLOAT 4 字节约 67 位有效数字，DOUBLE 8 字节约 1516 位；只用于科学值、统计值这类允许误差的量（评分均值、地理位置小数）。BIT(M)（1~64 位）存位标志，读写和多数客户端的字节打交互麻烦，几乎总是 TINYINT/SMALLINT 手动位运算替代更好，生产库极少见正当用途。

AUTO_INCREMENT 的四个必须知道

1. 计数器的持久化史： 8.0 之前 InnoDB 的自增当前值只记在内存，重启后按索引 MAX(id)+1 重算——如果最大行被 DELETE 过，重启后新插入会复用已发号的 ID，靠"ID 不复增"做幂等/对账的系统会翻车。8.0 起自增值持久化到 redo（崩溃恢复也往前走，不会回退）。
2. 空洞是正常现象： INSERT 失败回滚、INSERT IGNORE 被忽略的行、REPLACE 的删插、批量插入预分配、ODKU 命中更新分支——都会吃掉号。自增主键保证"唯一、单调"，不保证"连续"。
3. 别手写大值顶表： 显式插入一个超大 id，计数器会跟着跳到它后面；再叠加 BIGINT 逼近上限时报 1467（“table is full”）无法再自增，主键类型选小的灾难见 13-常见陷阱与最佳实践.md。
4. 分布式 ID 用 BIGINT 承接： 雪花号约 63 位，正落在 BIGINT（含符号位）范围内，表结构上不要为它开 UNSIGNED 再纠结溢出。
5. 多写源架构要错开发号： 双主/多源合表时用 auto_increment_increment / auto_increment_offset 错开号段，或干脆改应用层发号；两个源各自"连续"自增，合并进同一张表就是 1062 冲突大爆炸（拓扑话题见 10-主从复制与高可用.md）。

三、字符串类型

CHAR vs VARCHAR

M 是字符不是字节：VARCHAR(20) 放 20 个汉字没问题（utf8mb4 下汉字多为 3 字节）；反过来 CHAR_LENGTH 数字符、LENGTH 数字节，一行 utf8mb4 中文的字节数是字符数的 34 倍。utf8mb4 单行所有 VARCHAR/CHAR 加起来（含长度前缀）受 65535 字节整行上限约束，所以极端情况下 VARCHAR 最大约 VARCHAR(16383)；TEXT/BLOB 列只按 912 字节计入这个总和。超限时建表当场报 ERROR 1118 Row size too large (> 65535)，不会等你跑到线上才炸。

SELECT LENGTH('杭州'), CHAR_LENGTH('杭州');   -- 6, 2：LENGTH 是字节，CHAR_LENGTH 是字符（utf8mb4 汉字常 3 字节）

InnoDB 还有第二道闸：单行必须大致放进半个 16KB 数据页（约 8126 字节可用），超了就把最长的变长列整体挪到行外溢出页，行内只留 20 字节指针——数据在，代价是每次读多一跳页。页与行格式在 01-核心概念与架构.md。

TEXT 家族与"能用 VARCHAR 就不用 TEXT"

TEXT 与 VARCHAR 的本质差别在存储位置策略：TEXT 更容易整体外溢到溢出页，读取多一次页访问；建索引只能上前缀索引（04-索引原理与实战.md）；排序/GROUP BY 大字段退化代价高；而且它会诱惑后来人往主表塞越来越多大列。结论：几百字节内的文本一律 VARCHAR(64)/VARCHAR(255)；真正的文章正文、报文快照用 TEXT，并且放扩展表，主表列表查询用不到它。BLOB 存二进制同理——大文件更该走对象存储，库里只放 URL。

四、日期时间类型

全家对比

加小数秒写 DATETIME(3)/TIMESTAMP(3)（5.6.4 起），精度 06 位，多占 13 字节。高并发下"同秒两条日志分先后"值得用 (3)。

TIMESTAMP 的两个关键差异： 一是 2038-01-19 UTC 溢出上限，长命业务表（合同、档案）直接 DATETIME；二是时区换算——会话时区改了，TIMESTAMP 列显示值跟着变，同一时刻跨时区读出不同"墙上时间"。跨国部署团队最常见的"数据怎么对不上"，根源就在这：DATETIME 存的是墙钟、谁读都一样；TIMESTAMP 存的是绝对时刻、随会话时区变形。全公司统一一套解释（推荐统一 UTC 会话时区 + DATETIME(3)），比选哪个类型更重要。

-- 假设 ts TIMESTAMP 与 dt DATETIME 在同一时刻写入：
SET time_zone = '+00:00';
SELECT ts, dt FROM tz_demo;   -- 都显示写入时刻的 UTC 表示
SET time_zone = '+08:00';
SELECT ts, dt FROM tz_demo;   -- dt 原样不动；ts 按会话时区换算，显示值整体前移 8 小时

默认值与 ON UPDATE CURRENT_TIMESTAMP

create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
            ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间'

DATETIME/TIMESTAMP 都支持 DEFAULT CURRENT_TIMESTAMP（5.6.5 起）。ON UPDATE 的行为坑要背下来：

• 只在行真的发生变化时触发；ODKU 更新回相同值、UPDATE 未改变任何列，update_time 不动。
• 显式给该列赋值会覆盖自动行为——批量迁移/修数脚本不想刷 updated_at 的，显式 SET update_time = update_time 即可保旧值。
• 主从复制下这个值来自谁：行复制（8.0 默认）取主库算好的值；语句复制时该列在从库按回放时刻重新求值，同一行主从时间能差出一个复制延迟——跨实例比对 update_time 不要认真到毫秒（binlog 格式见 07-日志体系.md）。

BIGINT 存 Unix 时间戳：什么时候合理

应用层统一毫秒 BIGINT 的派别，优点是跨语言无时区歧义、比较排序都是整数、溢出到 2286 年。代价同样明确：库里直接看不可读、WHERE created_at BETWEEN '2026-10-01' AND ... 这类运维/报表查询全要转换、DATE/时间函数用不上。官方立场是 DATETIME/TIMESTAMP 优先。务实结论：业务主表用 DATETIME(3)（人可读、可索引、可分区），高吞吐日志/埋点表 BIGINT 毫秒；一旦选定，全库同一单位（秒还是毫秒写进规范）。

五、ENUM / SET：不推荐的两个理由

-- 看起来省空间的写法
status ENUM('pending','paid','shipped') NOT NULL DEFAULT 'pending'

ENUM 内部按定义顺序编号存 1~2 字节，SET 是 64 个成员的位集合。看起来优雅，实际三宗罪：

1. 排序陷阱： ORDER BY status 按内部编号排，得到的就是定义顺序——调整定义顺序等于改业务排序；想要"按字母/按业务优先级"都得显式 CASE/FIELD 改写。
2. 改值历史沉重： 在末尾追加成员尚可是 INPLACE 元数据变更；在中间插入/删除成员是 COPY 算法整表重建，千万行大表上就是一场锁表事故；5.x 时代老版本对 ENUM 变更的复制兼容问题更添黑历史。
3. 非法值静默降级： 非严格模式下写入不存在的值，落的是 ''（编号 0，比最小值还小），严格模式才报错——脏数据来源从此查无对证。

SELECT status FROM t ORDER BY status;
-- ENUM('refund','pending','paid') 时按内部编号排序：refund(1) < pending(2) < paid(3)，
-- 得到的是"定义顺序"不是字母序——想按业务优先级排还得 CASE 重写，等于 ENUM 白省了空间

结论：状态一律 TINYINT + 代码常量（+ 必要时的字典表），含义注释写全；迁移存量 ENUM 按 02-SQL基础.md 里 ALTER TABLE 的注意事项，先 SHOW CREATE TABLE 抄全定义。

六、JSON 类型（适度使用）

MySQL 的 JSON 列以二进制格式存储（解析过的文档，不是裸字符串），读路径跳过重新解析。函数速查：

-- 建一个只用于演示的扩展字段示例
ALTER TABLE users ADD COLUMN info JSON;

SELECT info->>'$.city_code' FROM users;        -- 等价 JSON_UNQUOTE(JSON_EXTRACT(info,'$.city_code'))，取文本
SELECT JSON_EXTRACT(info, '$.tags[0]'),        -- 返回 JSON 值（带引号）
       JSON_LENGTH(info->'$.tags'),
       JSON_CONTAINS(info->'$.tags', '"vip"');
UPDATE users SET info = JSON_SET(info, '$.vip_level', 3) WHERE id = 101;

-> 取 JSON、->> 取去引号文本；8.0 的 JSON_TABLE 可以把 JSON 数组展开成行 joins。

关键限制：JSON 列不能直接建普通索引（JSON 值不可比较大小）。高频查询字段的经典组合是生成列 + 索引：

ALTER TABLE users
  ADD COLUMN city_code CHAR(6)
    GENERATED ALWAYS AS (info->>'$.city_code') VIRTUAL COMMENT '生成列',
  ADD KEY idx_city_code (city_code);

VIRTUAL 生成列不占行内存储、二级索引会自动物化被索引的部分（STORED 则真的落列）；生成列表达式必须确定性，且不能再被随意 UPDATE。

选型立场：JSON 适合模式频繁演化、按整文档读写、少量等值点查的扩展属性；核心查询/排序字段该老老实实建列，塞进 JSON 再靠生成列捞回来，等于自己给自己加维护面。更新是整文档重写，大 JSON 频繁局部改动的场景性能要实测。8.0.4+ 的 JSON_TABLE 能把 JSON 数组展开成行，和普通表 JOIN：

SELECT u.id, jt.tag
FROM users u,
     JSON_TABLE(u.info, '$.tags[*]' COLUMNS (tag VARCHAR(32) PATH '$')) AS jt;
-- 一行用户的 tags:[...] 数组被展开成多行 tag，适合报表侧临时解析

七、空间类型（知道即可）

POINT / LINESTRING / POLYGON / GEOMETRY 存坐标，配 SRID 声明坐标系，SPATIAL 索引支持范围检索；“附近的人"这类需求 8.0 起可用 ST_Distance_Sphere(POINT(经度, 纬度), ?, 6371000) 直接算球面米距（“附近的人"更常见的做法还是 Redis GEO，见 ../redis/02-数据类型.md）。本手册不展开。

八、字符集与排序规则

utf8 不是真正的 UTF-8

MySQL 的 utf8 是历史实现的 utf8mb3，单字符最多 3 字节——存不进 emoji 和部分生僻汉字（4 字节字符），严格模式报 ERROR 1366 Incorrect string value: '\xF0\x9F...'。utf8mb4 才是完整 UTF-8（最多 4 字节/字符）。8.0 起服务器默认字符集已是 utf8mb4，但老项目、老配置文件里显式 CHARSET=utf8 会把手脚重新捆上：新库新表一律写死 utf8mb4；存量 utf8 表升级到 utf8mb4 用 ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4;（全表重建，大表操作看 09-Online-DDL与大表变更.md）。

CREATE TABLE t1 (c VARCHAR(10)) CHARACTER SET utf8;  -- utf8 是别名，实际是 utf8mb3
INSERT INTO t1 VALUES ('𠮷');        -- ERROR 1366: Incorrect string value（"𠮷"与 emoji 都是 4 字节字符，mb3 装不下）
-- JDBC 里报同一错误时，九成是连接串没配 utf8mb4 或表还是 utf8mb3，先查第八节的链条再改表

utf8mb4_0900_ai_ci vs utf8mb4_general_ci

日常业务里两者对大小写不敏感这一点一致，差别在边缘字符和尾部空格语义。同一套系统统一用一种，混用才是事故来源（见下面的 1267）。

-- 名字即说明书：utf8mb4_0900_ai_ci
--   0900 = 基于 Unicode 9.0 UCA；ai/as = 重音 insensitive/sensitive；ci/cs = 大小写 insensitive/sensitive
--   utf8mb4_bin 逐字节比较，最严格，"必须区分大小写"的列（如 API 密钥）常用
SELECT 'ab' = 'ab  ';   -- 尾部空格语义：general_ci(PAD SPACE) 为 true，0900_ai_ci(NO PAD) 为 false
SELECT 'ß' = 's', 'ß' = 'ss';  -- general_ci: 1,0；0900_ai_ci: 0,1 —— 拿不准就用查询实测

连接层的字符集链条

一条语句的编码要过四道：character_set_client（把客户端字节按此解释）→ character_set_connection（解析/比较用的中间字符集）→ 落盘转列字符集；查询返回再转 character_set_results。SHOW VARIABLES LIKE 'character_set%' 看全链路（还有 server/database 两个建表默认值）。乱码排查就是查这条链哪一环解释错了；连接串/客户端配置（如 JDBC characterEncoding=utf8、mysql 客户端 --default-character-set=utf8mb4）务必与库表一致。

1267 Illegal mix of collations

成因： 两个同字符集、不同排序规则的值放在一起做比较/拼接/UNION——典型是老表 utf8mb4_general_ci JOIN 新表 utf8mb4_0900_ai_ci，或列与字面量、跨库同名不同规则的字段互相比。错误信息里的 Implied 表示其中一方是按上下文推断出来的规则。

修法（按优先级）：

-- 临时：比较处显式 COLLATE 统一
SELECT * FROM a JOIN b ON a.name COLLATE utf8mb4_0900_ai_ci = b.name;
-- 治本：把表/列/库的排序规则改成一致（CONVERT TO CHARACTER SET ... COLLATE ...）
-- 连接/库级：建库建表时就声明统一规则，别让它继承到不确定的层级

大小写敏感的三层

1. 列值： 默认排序规则 ci——WHERE username = 'Abc' 能命中 ‘abc’；唯一键在 ci 下不区分大小写去重（‘Alice’ 和 ‘alice’ 视为同一个，注册系统高频坑）。需要区分时列改 utf8mb4_0900_as_cs 或 ..._bin，或者应用层规范化后存。
2. 库表列名： 由服务器参数 lower_case_table_names 决定：0 按原样存储比较（Linux 默认），1 一律转小写（Windows 常见默认），macOS 上部署常配 2。该参数在数据目录初始化后不能改（8.0 启动会校验，改了就拒绝启动）——开发机 macOS/Windows + 生产 Linux 的团队，要么统一小写表名纪律，要么统一初始化参数，别等 Table 'shop.Orders' doesn't exist 才发现。
3. 字符串字面量与标识符的敏感策略是两回事，别在 review 里混为一谈。

九、sql_mode：默认值是来保护你的

8.0/8.4 默认：

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE,
ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION

• STRICT_TRANS_TABLES： 类型不对、超范围、超长截断——事务表（InnoDB）直接报错拒写，而不是悄悄截断加个 warning。没有它，66 字的标题被剪成 64、'abc' 进 INT 列变 0、金额小数位被抹——数据在库里错着，bug 在线上留着。

-- 66 个字符的标题写进 VARCHAR(64) 列：
-- 默认（严格模式）：  ERROR 1406 Data too long for column 'title'
-- 关掉严格模式：      存前 64 字符 + Warning 1265 —— 被剪掉的 2 个字永远找不回来

• ONLY_FULL_GROUP_BY： 挡住"组内随机取一列”（来龙去脉与正确解法在 02-SQL基础.md 第六节）。
• NO_ZERO_DATE / NO_ZERO_IN_DATE： 禁 '0000-00-00' 和 '2026-00-01'。历史上拿零日期当"未设置"占位，在 8.4 世界请改用 NULL 或业务哨兵值；从老库迁移数据先扫 WHERE dt = '0000-00-00' 做清洗预案。
• ERROR_FOR_DIVISION_BY_ZERO： 严格模式下除零插入/更新直接错；配合 NULLIF 防（02 第十节）。
• NO_ENGINE_SUBSTITUTION： 指定 InnoDB 而引擎不可用时报错，而不是静默换成 MyISAM 把事务丢了。

调整方式：SET SESSION sql_mode='...';、SET GLOBAL、8.0 起 SET PERSIST，或配置文件 [mysqld] sql_mode=...。别随手关校验器：老代码批量报 1366/1265 时，正确动作是修列宽/修数据/修参数类型；只有"救火 + 当次会话"才允许临时 SET SESSION 降级。

十、建表规范清单

一张"合规样板"胜过十条规定：

CREATE TABLE orders (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  order_no     VARCHAR(32)     NOT NULL COMMENT '订单号，业务唯一',
  user_id      BIGINT UNSIGNED NOT NULL COMMENT '下单用户ID',
  status       TINYINT         NOT NULL DEFAULT 0 COMMENT '状态：0待付款 1已付款 2已发货 3完成 4退款',
  amount       DECIMAL(10,2)   NOT NULL DEFAULT 0.00 COMMENT '订单金额(元)',
  is_deleted   TINYINT         NOT NULL DEFAULT 0 COMMENT '逻辑删除：0正常 1已删',
  remark       VARCHAR(255)    NOT NULL DEFAULT '' COMMENT '备注',
  create_time  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time  DATETIME(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
               ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_time (user_id, create_time)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci
  COMMENT = '订单主表';

逐条背后的理由：

• 主键：BIGINT(UNSIGNED) 自增，业务字段永不进主键（业务规则会变，PK 不能跟着重构）；分布式生成 ID 也放 BIGINT。
• create_time / update_time 每表必备：审计、增量同步、数据归档全靠它俩。
• NOT NULL + 显式 DEFAULT：三原则之二；remark 用 '' NOT NULL DEFAULT ''，referrer_id 这类"确实可能没有"的才允许 NULL。
• COMMENT 齐全：列注释含枚举取值含义（status 那行），表注释说明职责；注释缺失的库对新人是黑盒。
• 命名：库表列全小写下划线（order_items，不是 OrderItems）；布尔列 is_xxx；避开保留词（desc、order、group、key——反引号能救语法，救不了可读性）。
• 单表列数与行宽：40 列以内；超过就垂直拆扩展表，别让窄查询拖着宽行。
• 逻辑删除字段（is_deleted）与物理删除的取舍见 13-常见陷阱与最佳实践.md（唯一键 + 软删的组合坑也在那里）。
• 给归档留后路：有增长曲线的表从第一天就按 create_time 规划归档窗口/分区键，别等 5 亿行再回来补列（拆分与归档见 12-分库分表.md）。

为什么互联网厂禁用外键、存储过程和触发器

• 外键： 分库分表后跨库引用根本无法约束（12-分库分表.md）；行级联检查带来额外往返和锁放大，级联删除能在一次 UPDATE 里悄悄锁删整片子表；库内约束与业务校验双轨容易行为不一致。
• 存储过程/触发器： 逻辑藏在数据库里——不进版本控制的代码、难调试、难测试、难水平扩展（计算贴着数据走，扩容时先卡 DB CPU）；复制拓扑下触发器还会放大主从延迟。
• 共同立场：一致性和业务规则放应用层 + 事务（05-事务与锁.md），数据库只负责存储与约束执行。 传统企业应用确有外键的场景另议，但团队内要有明确立场，不要一半表有一半没有。

十一、SHOW CREATE TABLE 与"照抄线上结构”

SHOW CREATE TABLE orders\G      -- \G 是 mysql 客户端的竖排显示，不是 SQL 语法

输出里除定义本体，还有两样东西要注意：AUTO_INCREMENT=10241 是当前计数值（跨环境建新表时决定要不要带走）；定义里以 /*! 加版本号开头的隐藏注释段（形如 /*!80013 ...*/）是按版本条件展开的子句——分区语法、InnoDB 的 SE 私有数据都走这里，肉眼读不懂就别手改，复制定义时保留原样交给目标版本自己处理。

照抄线上结构的工作流（强烈建议成为默认习惯）：

1. 目标环境执行 SHOW CREATE TABLE t\G（或 mysqldump --no-data --skip-comments 导出全库结构）；
2. 把输出贴成迁移脚本基线，只改要改的那一列/那一行，其余逐字节保持；
3. 评审 diff 只有意图变更，没有"本地手写版和线上版悄悄长得不一样"的漂移。

schema 漂移（测试环境手写建表 vs 线上历史 ALTER）是"本地能跑线上报错"的头号来源；5.7 dump 进 8.4 通常兼容，但记得按第八节把字符集统一成 utf8mb4。

十二、隐式类型转换：索引杀手

原理： 比较的两侧类型/字符集不一致时，MySQL 按优先级规则把一侧转换后才能比。只要转换发生在列这一侧，该列的索引就基本报废（B+ 树按原值有序，转了类型/字符集等于换了排序键）。

-- 灾难 1：varchar 列传数字
WHERE mobile = 13800001111
-- mobile 列被逐行转 DOUBLE 再比 → 全表扫；且 '138...x' 这类脏值转出来是 138，还会多匹配

-- 相对安全：int 列传字符串（转换发生在常量侧）
WHERE id = '100'            -- 可用索引；但 '100a' 会被按 100 处理并伴随 1292 warning（严格模式管写入，不管 SELECT 里的比较）

-- 函数套列同样杀索引（08 有完整等价改写清单）
WHERE DATE(create_time) = '2026-10-20'          -- 失效
WHERE create_time >= '2026-10-20'
  AND create_time <  '2026-10-21'                -- 区间改写，可用索引

-- 两侧不同字符集/排序规则的列 JOIN：转换甚至 1267 报错（见第八节）

写法对照表：

习惯层面：列类型与传参类型对齐是 ORM 时代的基本功——Go 的 struct 字段、Java 的实体类型要和 DDL 对得上，Code Review 对着 SHOW CREATE TABLE 看 WHERE 是最便宜的索引保险。索引失效的优化器视角与实测方法在 04-索引原理与实战.md 和 08-EXPLAIN与查询优化.md。

十三、ALTER 改类型：先想代价再动手

改列类型不是元数据一行字的事，代价分三档（完整分级与工具在 09-Online-DDL与大表变更.md）：

ALTER TABLE users MODIFY COLUMN name VARCHAR(128) NOT NULL;   -- 加长：可能 INPLACE，看前缀字节是否跨线
ALTER TABLE orders MODIFY COLUMN id BIGINT UNSIGNED NOT NULL; -- INT→BIGINT：COPY 整表重建
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4;            -- 改字符集：整表重建

三档算法的大致归位（8.4；完整分级与工具在 09-Online-DDL与大表变更.md）：

- INSTANT（秒级，只改元数据）：表尾加列、删列（8.0.29 起）、改列名、改默认值
- INPLACE（通常允许并发 DML，是否重建表看具体操作）：加二级索引、同长度前缀字节数内加长 VARCHAR
- COPY（整表重建）：INT→BIGINT、改列字符集、ENUM 中间插值

重建意味着：磁盘翻倍、执行时间与表大小成正比、期间锁风险与主从延迟。给高危变更显式指定算法是自我防御——ALGORITHM=INPLACE 或 ALGORITHM=INSTANT 写进语句，不支持时 MySQL 会直接报错而不是默默拷贝全表。大表（> 千万行 / > 数 GB）的结构变更走 gh-ost/pt-online-schema-change，见 09。

实战建议

• 拿到需求先问三件事：这个字段最大值是多少（定整数宽度）、会不会为空（定 NOT NULL）、要不要整段读出来（定 VARCHAR/TEXT 和拆表）。
• 钱用 DECIMAL(M,D) 或"分"+BIGINT，禁用 FLOAT/DOUBLE；时间统一 DATETIME(3)、全库一套时区解释。
• 新表从上面那份合规样板复制起步：BIGINT 自增主键、双时间列、注释齐全、utf8mb4_0900_ai_ci、不写 ENUM 不写外键。
• sql_mode 保持 8.4 默认；报错先修数据/列宽/传参，不关校验器。
• 改任何存量结构：先 SHOW CREATE TABLE 抄定义，MODIFY 写全整列；大表变更按 09 走算法/工具评估。
• 字符集、排序规则、表名大小写三件事在项目初始化（第一张表之前）就定下并写进部署脚本，事后统一等于全库重建。