一、建表：四件事想清楚

一条 CREATE TABLE 里塞着四类决定，混在一起看容易漏。

最容易后悔的是第二件：建表时没加非空约束，数据里就全是 NULL。 事后再加 NOT NULL，得先把那些 NULL 填上，否则一条语句都改不动。 所以建表时那一串 NOT NULL 不是形式主义，它是把「以后要清洗数据」这件事提前做掉。

二、改表：六种动作，代价不一样

ALTER TABLE 能干很多事，常用的六种，代价从「瞬间」到「重建整表」都有。

加索引那一行最容易被低估：它只写一行，但要扫一遍全表。 一亿行的表上，这一行能跑十几分钟，期间写入还会变慢。

三、删表与清空：一字之差

DROP TABLE 表; 连结构带数据一起删。 TRUNCATE TABLE 表; 只清数据，表还在，字段和索引都不动。

最容易踩的坑是以为 TRUNCATE 能回滚。 它不行：TRUNCATE 是 DDL，执行前会隐式提交，事务包不住它。 DELETE FROM 表; 看起来和 TRUNCATE 一样，其实差很远： 它逐行删、能回滚、不重置自增，大表上还特别慢。

四、图形工具里的表结构页

phpMyAdmin 的「结构」页，画的就是这张表的字段清单。 改一个字段的类型，它发出去的是一条 ALTER TABLE ... MODIFY。 它有一个地方比手写 SQL 好用：改之前会告诉你「这次改动会重建表」。 大表上看到这句话，就该换个时间做了。 也有一个地方容易出事：它能一键「清空表」，而那一键就是 TRUNCATE。 点之前手会抖一下，那就对了。

五、动手 Demo：表结构那点事，一条 ALTER 与三步重建

这一课真建一张表，再改它五次，看哪些改动 MySQL 一条语句就够、SQLite 得绕路。 绕路的那种要「建新表、拷数据、换名字」，这三步在大表上就是几十分钟的停机。

import sqlite3

conn = sqlite3.connect(db)
conn.execute("CREATE TABLE tb_goods (id INTEGER PRIMARY KEY,"
             " name TEXT NOT NULL, price REAL NOT NULL DEFAULT 0.00)")

# 三件 SQLite 一条语句能做的
conn.execute("ALTER TABLE tb_goods ADD COLUMN stock INTEGER DEFAULT 0")
conn.execute("ALTER TABLE tb_goods RENAME COLUMN stock TO qty")
conn.execute("ALTER TABLE tb_goods DROP COLUMN qty")

# 两件做不到的
try:
    conn.execute("ALTER TABLE tb_goods ALTER COLUMN memo TYPE VARCHAR(20)")
except sqlite3.Error as e:
    print("改列类型：", e)
try:
    conn.execute("ALTER TABLE tb_goods ADD CONSTRAINT c1 CHECK (price > 0)")
except sqlite3.Error as e:
    print("加约束：", e)

# 绕路：建新表、拷数据、删旧表、改名
conn.execute("CREATE TABLE tb_goods_new (...)")
conn.execute("INSERT INTO tb_goods_new SELECT id, name, price FROM tb_goods")
conn.execute("DROP TABLE tb_goods")
conn.execute("ALTER TABLE tb_goods_new RENAME TO tb_goods")

实跑输出：

--------------------------------------------------------------------
第十一课：表结构那点事，一条 ALTER 与三步重建
--------------------------------------------------------------------

  **3.3 节讲在图形工具里建表、改表、删表。**
  **建表最麻烦的不是写 SQL，是想清楚每列的类型和约束。**
  **这一课真建一张表，再改它五次，看哪些改动 MySQL 一条语句就够、SQLite 得绕路。**

  一、教材原码：建表与改表
  --------------------------------------------------------------------

  [教材原码 · 本沙箱不执行]
  | CREATE TABLE tb_goods (
  |     id INT NOT NULL AUTO_INCREMENT,
  |     name VARCHAR(50) NOT NULL,
  |     price DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  |     PRIMARY KEY (id)
  | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  | ALTER TABLE tb_goods ADD COLUMN stock INT DEFAULT 0;
  | ALTER TABLE tb_goods MODIFY COLUMN name VARCHAR(100) NOT NULL;
  | ALTER TABLE tb_goods CHANGE COLUMN stock qty INT DEFAULT 0;
  | ALTER TABLE tb_goods DROP COLUMN qty;
  | RENAME TABLE tb_goods TO tb_product;
  | DROP TABLE tb_product;
    （建表、加列、改列定义、改列名、删列、改表名、删表）


  二、同样的事，在 SQLite 里能做几件
  --------------------------------------------------------------------
  mysql> CREATE TABLE tb_goods (id INTEGER PRIMARY KEY,
         name TEXT NOT NULL, price REAL NOT NULL DEFAULT 0.00)
  **建表成功。**注意 AUTO_INCREMENT 换成了 `INTEGER PRIMARY KEY`，
  **`DECIMAL` 换成了 `REAL`——这一处会在第 6 章细讲，先记住它不是等价替换。**

  mysql> ALTER TABLE tb_goods ADD COLUMN stock INTEGER DEFAULT 0

  mysql> ALTER TABLE tb_goods RENAME COLUMN stock TO qty

  mysql> ALTER TABLE tb_goods DROP COLUMN qty

  mysql> ALTER TABLE tb_goods ADD COLUMN memo TEXT

  三、两种引擎，一件事的代价不一样
  --------------------------------------------------------------------

  改动      SQLite 的写法                  结果
  --------------------------------------------------------------------------
  加一列    ALTER TABLE ... ADD COLUMN     一条就行
  改列名    ALTER TABLE ... RENAME COLUMN  一条就行
  删一列    ALTER TABLE ... DROP COLUMN    一条就行
  改列类型  ALTER COLUMN ... TYPE          不支持：near "TYPE": syntax error
  加约束    ADD CONSTRAINT                 一条就行
  **三件能做，两件不能。**
  **不能的那两件，MySQL 一条 `ALTER` 就完，SQLite 得「建新表、拷数据、换名字」。**

  四、绕路的写法：三步重建
  --------------------------------------------------------------------
  **三步走完：建新表、拷进 0 行、删旧表、改名。**
  SELECT sql
  FROM sqlite_master
  WHERE name = 'tb_goods'
  CREATE TABLE "tb_goods" (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL
  DEFAULT 0.00, memo VARCHAR(20))
  **注意这个过程里表「消失过几毫秒」。**
  **大表上拷贝要几分钟，那几分钟里谁都查不了这张表。**
  **MySQL 8.0 的在线 DDL 让大部分 `ALTER` 不用锁表，这是它比 SQLite 更适合生产的一个具体原因。**

第一段那条 CREATE TABLE 值得逐字看：AUTO_INCREMENT 换成了 INTEGER PRIMARY KEY。 这不是换个名字，是换了机制——SQLite 的 INTEGER PRIMARY KEY 就是 rowid 的别名。 DECIMAL(8,2) 换成 REAL 更要注意。 REAL 是浮点，存 0.1 会存成 0.1000000000000000055…。 钱用浮点存，迟早要对不上账（第 6 章会专门讲）。 第二段那五行里，前三行成功、后两行报错。 报错的两行在 MySQL 上都是一条语句的事，这就是两个引擎的差距。 第三段是绕路写法：建新表、拷数据、删旧表、改名。 注意中间那几毫秒里表是「不存在」的。 大表上拷贝要几分钟，那几分钟谁都查不了这张表，而 MySQL 8.0 的在线 DDL 就是为解决这个来的。

30 秒实验

1. 把 Demo 里那段绕路写法改成「先建新表、拷完数据再删旧表」，调一下顺序。 你会发现最后那次改名必须放到最后——名字冲突，同时只能存在一张叫 tb_goods 的表。
2. 试着给 price 加一个 CHECK (price >= 0)，看 SQLite 怎么拒绝。 它不支持后加约束，只能重建表。 MySQL 8.0 支持 ADD CONSTRAINT，但会扫全表检查已有数据。
3. 想一想：一亿行的表要加一列，为什么 MySQL 8.0 能瞬间完成？ 因为它只在表的元数据里记一笔「这列默认值是多少」，不碰已有行。 但如果你给的默认值是「随机数」或者「当前时间」，它就不得不重写了。

常见误区

1. 「TRUNCATE 能回滚，因为它只是一条语句。」 不能。TRUNCATE 是 DDL，执行前隐式提交，事务包不住它。
2. 「DELETE FROM 表 和 TRUNCATE TABLE 表 是一回事。」 差很远：DELETE 逐行删、能回滚、不重置自增、大表上还慢。 TRUNCATE 直接丢弃数据页，快，但不可回滚。
3. 「改字段类型就是把定义改一下，很快。」 只有「放宽」的时候才快。 VARCHAR(50) 改 VARCHAR(100) 多数瞬间完成，VARCHAR(100) 改 VARCHAR(50) 要检查并可能截断， 通常要重写整表。

下一篇往最里一层：表里的数据怎么增删改查。 重点不在语法，在「WHERE 写错会动多少行」这件事上。

• ☐ （本篇完，下一篇 3.4 管理数据记录）

相关笔记

• 数据库操作管理
• 管理数据记录
• 45 课 MySQL 必知必会：建表与约束的系统讲法
• 🛢️ MySQL从入门到精通 · 索引