一、建表语句的骨架

教材 6.1 给的语法很长，去掉可选项之后其实只有四段。

CREATE TABLE [IF NOT EXISTS] 表名 (
    列名 类型 [约束] [默认值] [注释],   -- 一行一列，逗号分隔
    列名 类型 [约束] ...
) [ENGINE = 引擎] [DEFAULT CHARSET = 字符集];

第一段是表名，第二段是列定义（可以有几十行），第三段是表选项。 IF NOT EXISTS 是可选的，它的作用是「已存在就跳过、不报错」。 这个可选项要慎用：脚本里加了它，表结构改错了再跑一遍也不会报错，你会以为跑成功了。 它适合初始化脚本，不适合改结构的脚本。 每一列的写法顺序是固定的：先名字，再类型，再约束，最后默认值和注释。 顺序错了不一定报错，可能被当成别的东西——比如把 DEFAULT 写在类型前面。

二、六种约束，各拦什么

这一篇的核心就在这张表。六种约束，六种「不许」。

第四行那个「不报错」要单独说：默认值不是约束，它不拦任何东西，只是帮你填。 很多人把它当约束用，结果发现「插了空字符串」照样进得去。 第三行那个「允许 NULL」也是常错点：UNIQUE 只管重复，不管空。 所以「唯一且非空」要写两个约束，缺一个就有一条漏路。 第五行是六种里唯一需要「另一张表」才能成立的。 它默认还是关的：MySQL 里由 FOREIGN_KEY_CHECKS 控制， SQLite 里要显式 PRAGMA foreign_keys = ON。

三、自增的三个真相

AUTO_INCREMENT 看着简单，三个行为都跟直觉不一样。 第一个：号不回收。 插了 1、2、3，删掉 3，再插一行拿到的是 4，不是 3。 第二个：计数器跟着最大值跳。 手动插一行 id = 100，下一行就是 101，中间那些号永远不会再发。 第三个最隐蔽：8.0 之前，重启之后计数器会重算成「当前最大值加一」。 也就是说删掉末尾 100 行再重启，号会被重新发一遍——如果那些号在别处被引用过，就撞了。 MySQL 8.0 把计数器写进了 redo log，重启不再回退。 三条合起来是一句实践结论：自增 id 只是「不重复」，不是「连续」，更不是「业务编号」。 要对外可见的单号（订单号、流水号）得自己生成，别用自增 id。

四、建表语句末尾那一行

ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 这一行经常被省掉。 省掉不是错，是「跟着库的默认走」，而库的默认又跟着服务器走。

推荐第四行。不是因为库的默认不好，是因为「换一台服务器执行同一份建表脚本」时不会悄悄变样。 这种「换环境就变」的 bug 最难查，因为它在你机器上永远复现不出来。 字符集还能单独定在某一列上，但只在「这一列确实要存别的字符集」时才用。 混着用会让 JOIN 时出现隐式转换，索引直接失效。 排序规则（COLLATE）也能写，默认跟字符集绑。 utf8mb4_0900_ai_ci 里的 ci 是「大小写不敏感」，ai 是「重音不敏感」。 要区分大小写就把 ci 换成 bin 或 as_cs。

五、建表前想清楚的五件事

教材 6.1 的例子是一张商品表。真正动手时，先回答这五个问题比先敲 SQL 有用。

第一行和第五行是最容易后悔的两件。 主键定了之后改不动（所有外键都指着它），软删除没有预留列，后面每条查询都要改。 还有一个不成文的规矩：每张表都加 created_at 和 updated_at。 出事时第一个要看的就是「这行什么时候变成这样的」，而这两列事后补是全表改。

六、动手 Demo：建一张商品表，把六种约束一种一种试

这一课真建一张商品表，然后把六种违规输入一种一种往里塞，看数据库怎么答。 沙箱里是 SQLite 的语法，MySQL 的建表语句会在旁边贴出来对照。 最后再拿自增做三个实验：删末尾、显式插大号、看计数器怎么走。

c = sqlite3.connect(db)
c.execute("PRAGMA foreign_keys = ON")     # 外键默认是关的
c.execute("CREATE TABLE tb_goods ("
          "id INTEGER PRIMARY KEY AUTOINCREMENT, "
          "code TEXT NOT NULL UNIQUE, "
          "price REAL NOT NULL DEFAULT 0 CHECK (price >= 0), "
          "cat_id INTEGER REFERENCES tb_cat (id), "
          "created TEXT DEFAULT CURRENT_TIMESTAMP)")

# 六种违规输入，一种一种试
for tag, sql in tests:
    print(tag, try_do(c, sql))

# 自增：删末尾、显式插 100，看下一个号是多少
c.execute("DELETE FROM tb_ai WHERE id = 3")
c.execute("INSERT INTO tb_ai (v) VALUES ('d')")

实跑输出：

--------------------------------------------------------------------
第二十五课：建表就是写清六件事
--------------------------------------------------------------------

  **教材 6.1 的语法看着长，拆开就六件事：列名、类型、约束、默认值、注释、表选项。**
  **六件事里最容易省、也最容易还债的是约束。**
  **这一课真建一张商品表，把主键、非空、唯一、默认、外键、CHECK 这六种约束一种一种试。**
  **沙箱里用的是 SQLite 的语法，MySQL 的写法会在旁边贴出来。**

  一、六件事各管什么
  --------------------------------------------------------------------

  部件      写在哪     省了会怎样
  ---------------------------------------------
  列名      每行开头   省不了
  数据类型  列名后面   省不了，但选错很难改
  约束      类型后面   脏数据能进库，事后清不动
  默认值    `DEFAULT`  插入时不写就变 NULL
  注释      `COMMENT`  半年后没人知道这列是什么
  表选项    语句末尾   引擎和字符集跟库的默认走
  **第三行是这一课的主线。**
  **约束不是「给数据库添麻烦」，是把「不许出现的数据」挡在入口。**
  **省掉约束的项目，最后都会写一堆定时脚本去查脏数据。**
  **那些脚本本来可以不存在。**

  [教材原码 · 本沙箱不执行]
  | CREATE TABLE tb_goods (
  |     id      INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
  |     code    VARCHAR(20) NOT NULL UNIQUE COMMENT '商品编码',
  |     name    VARCHAR(50) NOT NULL COMMENT '商品名',
  |     price   DECIMAL(8,2) NOT NULL DEFAULT 0.00 COMMENT '单价',
  |     cat_id  INT UNSIGNED COMMENT '类别',
  |     created DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '建档时间',
  |     CONSTRAINT fk_cat FOREIGN KEY (cat_id) REFERENCES tb_cat (id),
  |     CONSTRAINT ck_price CHECK (price >= 0)
  | ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;
  ① 教材原码，本沙箱不执行


  二、先建表，再插一行正常的
  --------------------------------------------------------------------
  **沙箱里这张表用了同样的六个东西，只把 `AUTO_INCREMENT` 换成 SQLite 的自增写法。**
  **先插一行正常的，看看「全都没违规」时数据长什么样：**
  id code name     price cat_id created
  -------------------------------------------------
  1  G001 机械键盘 399   2      2026-10-30 13:26:12
  1 row in set
  **一行里六列全齐：主键自动给了 1，`created` 自动填了当前时间，其余是我给的。**
  **这就是「约束全满足」的样子——注意它没有报错，也没有提示。**

  三、六种错误输入，六种拦法
  --------------------------------------------------------------------

  试了什么    数据库怎么答
  -----------------------------------------------------
  编码重复    UNIQUE constraint failed: tb_goods.code
  编码为空    NOT NULL constraint failed: tb_goods.code
  名字为空    NOT NULL constraint failed: tb_goods.name
  价格为负    CHECK constraint failed: price >= 0
  类别不存在  FOREIGN KEY constraint failed
  主键重复    UNIQUE constraint failed: tb_goods.id
  **六行全是拒绝，一行都没进去。**
  **这就是约束的价值：错误在写入的那一刻暴露，而不是三个月后对账时。**
  **看第五行那个「类别不存在」。**
  **它是外键约束，也是七种约束里唯一需要「另一张表」才能成立的。**
  **注意它默认是关的。**
  **MySQL 里由 `FOREIGN_KEY_CHECKS` 控制，SQLite 里要显式 `PRAGMA foreign_keys = ON`。**
  **关掉它的时候，脏数据能进去，而且事后加不上约束。**
  **这一点在第 11 章讲外键时会再展开。**

  四、自增的三个真相
  --------------------------------------------------------------------
  **第一个真相：删掉最后一行，新行的 id 不回来。**
  **先插三行拿到 1、2、3，删掉 3 再插一行——**

  动作                         结果
  ------------------------------------------
  插三行                       id = 1, 2, 3
  删掉 id = 3，再插一行        新行 id = 4
  显式插入 id = 100，再插一行  新行 id = 101
  **第二行是关键：计数器只往前走，不回收。**
  **所以「id 连续」这件事永远不要指望，删过的号就是空洞。**
  **第二个真相：显式插一个更大的 id，计数器会跟着跳过去。**
  **第三行就是这样，插了 100 之后下一行是 101。**
  **第三个真相更隐蔽：8.0 之前，重启之后计数器会重算成「当前最大值加一」。**
  **MySQL 8.0 把计数器写进了 redo log，重启不再回退——这是一条很多人不知道的版本差异。**

  五、建表语句末尾那一行
  --------------------------------------------------------------------
  **`ENGINE = InnoDB DEFAULT CHARSET = utf8mb4` 这一行经常被省掉。**
  **省掉不是错，是「跟着库的默认走」；而库的默认又跟着服务器走。**

  写不写        结果
  ----------------------------------------
  全不写        用库的默认引擎和字符集
  只写 ENGINE   引擎指定，字符集还是跟库
  只写 CHARSET  字符集指定，引擎跟库
  两个都写      与库无关，这张表自己说了算
  **第四行是推荐做法。**
  **不是因为库的默认不好，是因为「换个库执行同一份建表脚本」时不会悄悄变样。**
  **字符集还可以单独定在某一列上，但那只在「这一列确实要存别的字符集」时才用。**
  **混着用会让 JOIN 时出现隐式转换，索引直接失效。**

第一段那张表和 MySQL 的写法一一对应，只有自增那一处不同。 SQLite 用 INTEGER PRIMARY KEY AUTOINCREMENT，MySQL 用 INT AUTO_INCREMENT PRIMARY KEY。 第二段六行全是拒绝，一行都没进去。 这就是约束的价值：错误在写入那一刻暴露，而不是三个月后对账时。 注意最后一行报的是 UNIQUE constraint failed: tb_goods.id，不是 PRIMARY KEY。 因为主键在底层就是一个唯一索引，所以报错文本说的是 UNIQUE。 第三段那三个数是 1、2、3 → 删 3 → 4 → 显式插 100 → 101。 号不回收、计数器跟着最大值跳，两件事一次看全。

30 秒实验

1. 把 price 那一列的 CHECK (price >= 0) 去掉，再插一个 -1。能插进去了。 约束去掉之后，负价格就成了合法数据，而且事后加不回来——已有的负值会让你加不上 CHECK。
2. 把 code 列的 UNIQUE 换成 PRIMARY KEY，想想能不能这么做。 不能，一张表只能有一个主键，而这张表已经有 id 了。 UNIQUE 可以有多个，这就是它存在的意义。
3. 给表加一列 is_deleted INTEGER DEFAULT 0，再想想为什么「软删除」要提前留。 因为要改所有查询语句，而且旧数据里的 NULL 会让 WHERE is_deleted = 0 漏行。

常见误区

1. 「IF NOT EXISTS 加了更安全。」 它只是不报错，不是「检查结构对不对」。 表结构改错之后重跑，它是静默跳过的，你会以为成功了。
2. 「DEFAULT 是约束，能拦住空值。」 它是「你没有指定值时，我帮你填」。 显式写 NULL 或者写空字符串，它一点都不拦。
3. 「主键自增就一定连续。」 删过的号不回收，显式插大号会跳过一段，回滚也会消耗号。 并发插入时，号还可能被两个事务交叉拿到。
4. 「约束影响性能，能省就省。」 唯一约束靠索引实现，那个索引本来就该建。 省掉它换来的是脏数据，清脏数据的成本远高于那点写入开销。

6.1 到这儿结束。下一篇 6.2 讲怎么看表结构—— 建完之后得会验收，DESCRIBE 和 SHOW CREATE TABLE 各看一半。

• ☐ （本篇完，下一篇 6.2 查看表结构）

相关笔记

• MySQL 存储引擎
• MySQL 数据类型
• 数据表操作管理
• 🛢️ MySQL从入门到精通 · 索引