7.2 数据表操作：创建、修改、删除、清空

表是数据库中存储数据的核心对象。所有数据最终都落在某张表的某一行中，因此熟练地创建、调整和维护表结构，是后端开发的基本功。本节将围绕 CREATE、ALTER、DROP、TRUNCATE 四条核心命令，结合真实开发中常遇到的坑和最佳实践展开。

7.2.1 创建表：CREATE TABLE

建表是数据库设计的具体落地。一个完整的 CREATE TABLE 语句可能看起来很长，但它的核心结构其实非常清晰：

CREATE TABLE [IF NOT EXISTS] 表名 (
    列名 数据类型 [约束] [默认值] [注释],
    列名 数据类型 [约束] [默认值] [注释],
    ...,
    [表级约束]
) [ENGINE=引擎] [CHARSET=字符集] [COMMENT='表注释'];

最简单的建设例子：用户表

CREATE TABLE IF NOT EXISTS `user` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    `username` VARCHAR(50) NOT NULL COMMENT '用户名',
    `email` VARCHAR(100) NOT NULL 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='用户表';

这段语句包含了几个关键设计习惯：

IF NOT EXISTS：在脚本或迁移工具中常用，避免因表已存在而报错中断执行。但要注意，如果表已存在，整个语句会被静默忽略，不会对现有表做任何修改。
显式指定所有约束：NOT NULL、DEFAULT、COMMENT 等全部写明确。不要让数据库替你猜默认值，这在维护阶段会避免大量“为什么这个字段允许为 NULL”的疑问。
主键和唯一索引写在表定义内：比后续单独 ALTER TABLE ADD INDEX 更清晰，尤其在 DBA 或同事审核表结构时有完整视图。
存储引擎与字符集显式声明：即使当前数据库的默认值和你的设定一致，也写上。因为未来数据库配置可能变更，显式定义将表的行为固定下来，避免意外。
表注释：一张表在数据字典里如果没有任何说明，半年后你自己都可能忘了它的用途。养成写注释的习惯，对自己和别人都有好处。

关于数据类型的选择，建表时必须基于真实业务需求，而不是“数值全用 INT，字符串全用 VARCHAR(255)”。几个常见原则：

BIGINT UNSIGNED 用于自增主键，可容纳 0~18446744073709551615，比 INT (21 亿上限)更安全，不怕主键耗尽。
字符串长度按需分配：VARCHAR(50) 和 VARCHAR(500) 在存储短字符串时性能差异不大，但过大的长度可能导致索引超长或内存浪费。
时间类型使用 DATETIME 还是 TIMESTAMP：TIMESTAMP 受 2038 年溢出限制，且会受时区影响自动转换；DATETIME 范围更广，存储的是字面值。现代 MySQL 版本通常推荐 DATETIME，并根据需要存储 UTC 时间戳。

MySQL 8.0 特有的注意点：8.0 支持 CHECK 约束，可以在建表时约束字段的值范围，例如：

`age` TINYINT UNSIGNED NOT NULL CHECK (age >= 0 AND age <= 120)

但在 5.7 版中，虽然 CHECK 语法能通过解析，却不会真正生效，这是非常隐蔽的兼容性坑。如果你需要向低版本迁移，最好通过业务逻辑或触发器来实现此类校验。

7.2.2 修改表：ALTER TABLE

业务迭代必然导致表结构变化。ALTER TABLE 可以添加列、删除列、修改列类型、添加索引、重命名表等，但它也是 MySQL 中风险极高的操作之一。

添加列

ALTER TABLE `user` ADD COLUMN `phone` VARCHAR(20) DEFAULT NULL COMMENT '手机号' AFTER `email`;

使用 AFTER 可以指定新列的位置，这对保持列顺序的语义一致有些许帮助，但无性能影响。
添加列时如果指定 NOT NULL 但没有默认值，会直接报错，因为现有行的该列值无法确定。

修改列类型或属性

ALTER TABLE `user` MODIFY COLUMN `phone` VARCHAR(30) NOT NULL DEFAULT '';

MODIFY 用于修改列定义（类型、约束等）。一个常见痛苦是：当你只想给字符串加长长度时，也必须将 NOT NULL、DEFAULT 和 COMMENT 重新写一遍，因为 MODIFY 会覆盖该列的全部定义。如果你省略了注释，注释就会变空。所以很多 DBA 会先 SHOW CREATE TABLE 拿到列定义，修改后重新放回去，确保不会丢失已有属性。

重命名列或表

ALTER TABLE `user` CHANGE `phone` `mobile` VARCHAR(30) NOT NULL DEFAULT '';
ALTER TABLE `user` RENAME TO `users`;

CHANGE 可以同时重命名列和修改其定义，RENAME TO 用于重命名表。重命名表的场景较少，但在大版本升级或表清理时偶尔用到。

必须注意的性能陷阱

ALTER TABLE 在很多情况下会导致全表拷贝，对线上服务影响巨大。MySQL 的处理方式主要有三种：

COPY 算法（老方式）：新建一张临时表，复制原表数据，执行修改，再替换原表。整个过程会锁表（或允许读但阻塞写），表越大时间越长。
INPLACE 算法：在原始表空间内就地修改，不需要全表复制，但依然可能在特定阶段持有元数据锁，阻塞其他 DDL。
INSTANT 算法（MySQL 8.0 支持）：仅修改数据字典中的元数据，不碰数据，瞬间完成。目前仅支持添加列（在最后位置）、设置默认值等少数操作。

生产环境安全法则：

永远不要直接在高峰期对百万级以上的表执行不确定算法的 ALTER。你可以用 ALGORITHM=INSTANT 试探，如果引擎不支持会立即报错，而不会默默执行数十分钟。
对于需要长时间锁定的大表修改，使用 pt-online-schema-change（Percona Toolkit）或 gh-ost 等工具，它们会在后台无阻塞地完成表结构修改。
删除列或修改列类型的操作尤其危险，因为所有数据行都需要重写。务必事先在从库或测试环境评估耗时。

7.2.3 删除表：DROP TABLE

DROP TABLE [IF EXISTS] 表名;

看似简单，但后果极端：表结构和所有数据将被立即永久删除。没有回收站，没有确认提示，且即使在事务中，DROP TABLE 在多数存储引擎下会隐式提交当前事务，无法回滚。

安全实践经验：

生产数据库的 DROP 权限应严格收回，普通开发账号不应持有。
在执行危险操作前，先 SELECT COUNT() 再次确认表名和数据量，或者使用 CREATE TABLE xxx_bak AS SELECT FROM xxx 备份。
MySQL 8.0 的“回收站”功能可通过设置 innodb_undo_tablespaces 等参数配合 FLASHBACK 提供一定程度的误删恢复，但不可依赖。备份才是王道。
删除分区表的一个分区使用 ALTER TABLE ... DROP PARTITION，比直接 DELETE 高效得多。

7.2.4 清空表：TRUNCATE TABLE

TRUNCATE [TABLE] 表名;

TRUNCATE 用于快速清空一张表的所有数据，相比于不带 WHERE 的 DELETE，它有两个显著差异：

执行方式：TRUNCATE 实际上是先删除表，再重建一张结构相同的空表（对于 InnoDB）。因此它不会逐行记录 Undo Log，操作几乎是瞬间完成，无论表中有多少千万行数据。
自增值重置：TRUNCATE 会将 AUTO_INCREMENT 计数器重置为起始值（通常是 1），而 DELETE 不会重置。

使用注意：

TRUNCATE 不能用于有外键引用的表，即使子表为空或外键设置 CASCADE，也会报错。必须先取消外键约束或清空相关表。
在事务中，TRUNCATE 的行为取决于版本。早期版本会隐式提交，MySQL 8.0 中 TRUNCATE 在某些条件下可以回滚（例如在原子 DDL 下），但最好不要依赖这种行为，仍视其为不可回滚操作。
如果你的业务逻辑需要保留自增 id 的连续性（例如订单号不做重用），则应该使用 DELETE 而不是 TRUNCATE。

一切与表结构相关的操作，本质都是在改写数据库元数据。这些操作一旦执行，通常难以撤销。因此，谨慎、显式、备份、不在生产环境手工操作是每个开发者面对这张表该有的意识。