DDL 与表结构

这一课你将能做到

1. 独立写出含主键、唯一键、默认值、注释、字符集声明的高质量 CREATE TABLE
2. 用 ALTER TABLE 安全地加列、改列、重命名列，并说清楚每种操作的锁代价
3. 准确区分 DROP / TRUNCATE / DELETE 三者的行为差异和能否回滚

为什么要学

表结构是所有 SQL 的地基：类型选错（FLOAT 存金额、INT 存手机号）会在上线几个月后变成资损事故；默认值和 NOT NULL 没设计好，查询里会到处冒出 NULL 判断。DDL 也是面试高频区——「DATETIME 和 TIMESTAMP 怎么选」「线上千万级大表怎么加字段」几乎是后端面试的必考题。

人话：DML 是往房子里搬家具，DDL 是砌墙。墙砌歪了，家具怎么摆都别扭。本课就用第 1 课的 cf_user 表做靶子，把建表这件事做规范。

先建立图像

一行用户数据在磁盘上的样子：

cf_user 的一行：
┌────┬──────────┬──────────────────┬───────┬────────┬─────────────────────┐
│ id │ username │ email            │ level │ status │ created_at          │
├────┼──────────┼──────────────────┼───────┼────────┼─────────────────────┤
│ 1  │ lin      │ lin@example.com  │ 3     │ active │ 2024-11-01 09:00:00 │
└────┴──────────┴──────────────────┴───────┴────────┴─────────────────────┘
   ↑            ↑                ↑       ↑         ↑
 自增整数    变长字符串≤50字符   无符号小整数  枚举    日期时间(无时区换算)

类型选择的军规表（先背下来，本课逐一验证）：

场景	推荐类型	禁止
主键	BIGINT UNSIGNED AUTO_INCREMENT	INT 撑不了几年
短文本	VARCHAR(n)	CHAR 存变长
金额/积分	DECIMAL 或整数分	FLOAT/DOUBLE
状态	TINYINT/ENUM	VARCHAR 随便写字符串
时间	DATETIME	VARCHAR 存时间
长文本	TEXT 慎用	默认值、内存临时表都受限

跟着做

步骤 1：规范版重建 cf_user

USE sql_practice;
DROP TABLE IF EXISTS cf_user;

CREATE TABLE cf_user (
  id         BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  username   VARCHAR(50)      NOT NULL COMMENT '用户名，唯一',
  email      VARCHAR(255)     NOT NULL COMMENT '邮箱，唯一',
  level      TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '等级 1-5',
  status     ENUM('active','banned') NOT NULL DEFAULT 'active' COMMENT '账号状态',
  created_at DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_username (username),
  UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

注意三个细节：每列有 COMMENT（半年后没人记得字段含义，注释就是文档）；NOT NULL 全覆盖（NULL 是歧义之源）；唯一键显式命名 uk_ 前缀（报错时能定位到约束）。

步骤 2：看 MySQL 眼里的真实结构

SHOW CREATE TABLE cf_user\G
DESC cf_user;

SHOW CREATE TABLE 会把隐式行为补全暴露出来：InnoDB、行格式、字符集、默认值全部显式化。读这条输出是排查表结构问题的基本功。

步骤 3：验证默认值

INSERT INTO cf_user (username, email) VALUES ('lin', 'lin@example.com');
SELECT * FROM cf_user;

只给了两列，其余列自动落默认值：level=1、status='active'、created_at=当前时间。这就是 DEFAULT 的价值——应用层不用填，数据库兜底。

步骤 4：ALTER 三连

-- 加列（8.0.12+ 走 INSTANT 算法，秒级完成，不重建表）
ALTER TABLE cf_user ADD COLUMN bio VARCHAR(500) NULL COMMENT '个人简介' AFTER email;

-- 改列类型（需要重建数据时耗时与表大小相关）
ALTER TABLE cf_user MODIFY COLUMN username VARCHAR(60) NOT NULL;

-- 重命名列（8.0 新语法，只改元数据）
ALTER TABLE cf_user RENAME COLUMN bio TO signature;

执行后 DESC cf_user; 确认列变成 signature。

步骤 5：类型实验——FLOAT 的精度陷阱

SELECT CHAR_LENGTH('码'), LENGTH('码');
-- CHAR_LENGTH 按字符算 = 1；LENGTH 按字节算 = 3。所以 VARCHAR(50) 是 50 个"字符"。

SELECT CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE);
-- 0.30000000000000004 ← 浮点误差实锤
SELECT CAST(0.1 AS DECIMAL(10,2)) + CAST(0.2 AS DECIMAL(10,2));
-- 0.30 ← DECIMAL 精确

这就是金额、积分永远不用 FLOAT/DOUBLE 的原因。

步骤 6：DROP / TRUNCATE / DELETE 对比实验

CREATE TABLE tmp_demo (id INT PRIMARY KEY);
INSERT INTO tmp_demo VALUES (1),(2);

DELETE FROM tmp_demo;            -- DML：逐行删，可回滚，自增值保留
INSERT INTO tmp_demo VALUES (10);
TRUNCATE TABLE tmp_demo;         -- DDL：整表重建，不能回滚，自增重置
DESC tmp_demo;                   -- 结构还在
INSERT INTO tmp_demo VALUES (1); -- 自增从 1 重新开始
DROP TABLE tmp_demo;             -- 连结构一起删，不能回滚
DESC tmp_demo;                   -- ERROR 1146：表不存在了

一句话记忆：DELETE 慎删、TRUNCATE 清空、DROP 灭门。

讲清楚要点

1. AUTO_INCREMENT 用 BIGINT UNSIGNED：INT 上限约 21 亿，高频写入的分表场景几年就能耗尽；BIGINT 上限约 9.2×10^18，几乎不可能耗尽。且表里已有 21 亿行时再改类型代价极大，一开始就用 BIGINT。
2. NOT NULL + DEFAULT 是默认姿势：NULL 参与任何比较都返回 NULL（NULL = NULL 结果不是 TRUE），聚合函数忽略 NULL，COUNT(列) 和 COUNT(*) 不一致，索引统计也会受影响。显式默认值让数据语义干净。
3. VARCHAR 长度按字符计，按需分配：VARCHAR(255) 只是上限，实际存储按真实长度；但长度选太大会让优化器估算失真，按业务上限选合理值（用户名 50、邮箱 255、标题 200）。
4. ALTER 的三种代价：8.0 里加列默认 INSTANT（只改元数据，秒级）；改列类型多为 INPLACE 或 COPY（锁表或重建表）；所以「线上大表加字段」在 8.0 基本安全，「改字段类型」要评估，必要时用 gh-ost/pt-osc 在线改表工具。
5. TRUNCATE 是 DDL：它会隐式提交事务、清空后自增重置、比逐行 DELETE 快几个数量级；代价是触发器不触发、不能回滚。清测试数据用它，删业务数据用带 WHERE 的 DELETE。
6. 命名规范三件套：库表列全小写下划线；索引前缀 idx_（普通）、uk_（唯一）、fk_（外键）；布尔列用 is_xxx。规范的命名让报错信息一眼定位。

你可能会懵的地方

• VARCHAR(50) 到底能存多少？ 50 个字符（不是字节）。一个汉字在 utf8mb4 里占 3~4 字节但只算 1 个字符，所以 CHAR_LENGTH 与 LENGTH 结果不同。

• TIMESTAMP 不是更省空间吗，为什么全用 DATETIME？ TIMESTAMP 只到 2038 年（32 位秒数上限），且存取依赖会话时区自动转换，跨时区部署容易出诡异 bug。DATETIME 范围 1000~9999 年、无隐式转换，配合应用层统一存 UTC 更稳。

• 改列类型会锁表吗？ 看算法：ALGORITHM=INSTANT 只改元数据不锁；INPLACE 允许并发读写但耗资源；COPY 重建全表基本不可用于大表。8.0 下大部分操作是前两种，但改 VARCHAR 长度超过字节阈值时会退化为 COPY。

• AUTO_INCREMENT 的值能倒退吗？ 不能。删了最大 id 的行，自增计数器不回退（8.0 还会持久化该值，重启也不回退）。所以不要依赖「id 连续」做业务判断。

• 为什么每张表都要写 ENGINE=InnoDB？ 虽然默认引擎是 InnoDB，但老环境可能被改成 MyISAM。显式声明是防御性建表，跟显式列名 INSERT 是同一个思想。

错法对照

错法	问题	正解
FLOAT 存积分/金额	浮点误差导致对账不平	DECIMAL(12,2) 或整数分
不写 NOT NULL 也不写默认值	全是 NULL，查询到处写 IS NULL	NOT NULL DEFAULT xx
用保留字当列名（order、desc）	每条 SQL 都要反引号，易踩坑	改名 order_no、created_desc
一列既存 ID 列表又当备注	违反第一范式，无法建索引关联	拆成关联表
生产大表直接 MODIFY 列类型	COPY 算法长时间锁写	先在从库演练，必要时 gh-ost

卡点急救

1. ERROR 1067 Invalid default value for 'created_at'：DATETIME 的默认值写成了 '0000-00-00' 等非法值。用 DEFAULT CURRENT_TIMESTAMP。
2. ERROR 1366 Incorrect string value（插 emoji 报错）：表或列字符集不是 utf8mb4。ALTER TABLE t CONVERT TO CHARACTER SET utf8mb4;。
3. ERROR 1146 Table doesn't exist：没 USE sql_practice 就查询，或大小写写错（Linux 上表名默认大小写敏感）。先 USE，再统一小写。

面试常问

• DATETIME 和 TIMESTAMP 的区别？ 范围（1000-9999 vs 1970-2038）、存储（8 字节 vs 4 字节）、时区行为（DATETIME 原样存取，TIMESTAMP 按 UTC 存储并随会话时区转换）。业务时间用 DATETIME，由应用层统一时区。

• 线上千万级大表怎么加字段？ MySQL 8.0 优先 ALGORITHM=INSTANT 加列（秒级）；低版本或不支持的操作用 gh-ost/pt-online-schema-change 在影子表上变更后原子切换；改前在从库评估耗时，低峰期执行。

• CHAR 和 VARCHAR 怎么选？ 定长短串（如国家码 CHAR(2)）用 CHAR，存取无长度开销；变长一律 VARCHAR。CHAR 超长会截断报错，VARCHAR 有 1~2 字节长度前缀。

自测三问

1. DELETE FROM t、TRUNCATE TABLE t、DROP TABLE t 三者分别能否在事务中回滚？为什么？
2. 为什么积分字段不能用 FLOAT？如何用一条 SELECT 验证浮点误差？
3. 8.0 的在线 DDL 中，加列默认走什么算法？什么操作最危险？

小结一句话

DDL 定义数据的骨架——类型决定精度边界、NOT NULL+DEFAULT 决定数据语义、ALTER 的算法决定变更代价，DROP/TRUNCATE/DELETE 的回滚差异是安全底线。

延伸

• MySQL 8.0 Reference Manual — CREATE TABLE
• MySQL 8.0 Reference Manual — ALTER TABLE
• 下一课：DML 操作——往这套骨架里安全地写入、修改、删除数据。