SQL 语言基础（DDL / DML / DCL / TCL） · DDL — 库与表的创建

CREATE / ALTER / DROP / RENAME / TRUNCATE 各自的语义和区别？

What are the semantics and differences of CREATE, ALTER, DROP, RENAME, and TRUNC

答案语言

考察要点

这道题面试官想看你是否清晰地理解 SQL 中 DDL (Data Definition Language) 的核心指令。重点在于区分 TRUNCATE、DROP 和 DELETE (DML) 这三个易混淆操作的语义、性能、事务和底层实现差异。同时，也考察你对在生产环境中执行 DDL 操作（如 ALTER TABLE）的风险和最佳实践（如 Online DDL）的认知。

核心概念

DDL (Data Definition Language) 是用于定义和管理数据库对象的 SQL 子集，如库、表、索引、视图等。其核心特点是操作会隐式提交 (Implicit Commit)，一旦执行成功，通常无法在当前事务中回滚。

下面是各指令的详细解析：

指令	类型	操作对象	核心语义
`CREATE`	DDL	新数据库对象	创建一个全新的数据库对象，如 `CREATE TABLE`、`CREATE DATABASE`、`CREATE INDEX`。
`ALTER`	DDL	已存在的数据库对象	修改一个已存在对象的结构，如给表添加/删除/修改列、重命名列、添加/删除索引。
`DROP`	DDL	已存在的数据库对象	彻底删除一个数据库对象，包括其结构定义、所有数据、索引、约束、触发器等。
`RENAME`	DDL	已存在的数据库对象	修改一个数据库对象的名称。
`TRUNCATE`	DDL	表内所有数据	快速清空一个表中的所有行，但保留表结构、索引、约束等定义。

关键区别：`TRUNCATE` vs `DROP` vs `DELETE`

这是本题的核心，也是面试最高频的对比。DELETE 属于 DML (Data Manipulation Language)，但常与 TRUNCATE 和 DROP 一起比较。

数据示例： 假设我们有一个 users 表：

sql

 1CREATE TABLE users (
 2    id INT AUTO_INCREMENT PRIMARY KEY,
 3    name VARCHAR(50)
 4);
 5INSERT INTO users (name) VALUES ('Alice'), ('Bob');

操作	`DELETE FROM users;`	`TRUNCATE TABLE users;`	`DROP TABLE users;`
结果	表结构和定义保留，`AUTO_INCREMENT` 计数器不重置。	表结构和定义保留，`AUTO_INCREMENT` 计数器重置。	表 `users` 不复存在。

详细对比表：

特性	`DELETE`	`TRUNCATE`	`DROP`
SQL 类型	DML (数据操作语言)	DDL (数据定义语言)	DDL (数据定义语言)
操作粒度	逐行删除	删除所有数据页/区	删除整个对象（表文件）
事务与回滚	可回滚 (在 `COMMIT` 前)	不可回滚 (隐式提交)	不可回滚 (隐式提交)
触发器	会触发 `ON DELETE` 触发器	不会触发 `ON DELETE` 触发器	不会触发任何表级触发器
执行速度	慢 (记录日志，逐行操作)	极快 (元数据操作，少量日志)	快 (文件系统级别操作)
`AUTO_INCREMENT`	不重置	重置为初始值	随表一起被删除
返回删除行数	返回删除的行数	通常返回 0	不适用
锁机制 (`InnoDB`)	行级锁 (Row Lock)	表级锁 (Table Lock)	表级锁 (Table Lock)
适用场景	删除部分数据；需要回滚；需要触发器	清空整张表；追求性能；无需回滚	彻底废弃一张表
安全性	相对安全，可恢复	高风险，数据瞬时丢失	极高风险，结构和数据全无

常见低分回答

混淆 TRUNCATE 和 DROP：“TRUNCATE 就是删除一张表”，这是完全错误的。TRUNCATE 只清空数据，表结构依然存在。
认为 TRUNCATE 和 DELETE 没区别：“TRUNCATE TABLE t 和 DELETE FROM t 效果一样”，这种回答忽略了事务、性能、触发器、自增ID重置等本质区别，是面试中的重大失分点。
对 DDL 的事务性理解不清：“我开启一个事务，然后 ALTER TABLE，如果错了再 ROLLBACK 就行了”，这是错误的。在 MySQL 中，DDL 会导致隐式提交，之前的 DML 操作会被提交，DDL 本身也无法回滚。
不知道 TRUNCATE 会重置 AUTO_INCREMENT：在被问到清空表后新增数据 ID 从几开始时，回答会从之前的最大 ID 继续，这暴露了知识盲点。
在 WHERE 子句中使用 TRUNCATE：TRUNCATE 是一个独立的指令，不能用于 WHERE 子句中，如 DELETE FROM t WHERE id > 100 TRUNCATE ... 这种语法是错误的。

工程实践与优化

大表 ALTER 风险：在生产环境对上亿行的大表执行 ALTER TABLE 是高危操作。在 MySQL 5.5 及之前，它会锁住整张表进行数据拷贝和重建，导致服务长时间不可用。即使后续版本引入了 Online DDL，在某些场景下依然有性能抖动和锁等待风险。
Online DDL 方案：对于大表结构变更，业界标准做法是使用外部工具，如 Percona 的 pt-online-schema-change 或 GitHub 的 gh-ost。
- 工作原理：
  1. 创建一个与原表结构相同但表名不同的“影子表”(_new_table)。
  2. 在影子表上执行 ALTER 操作。
  3. 在原表上创建触发器（INSERT/UPDATE/DELETE），将变更期间的增量数据同步到影子表。
  4. 将原表数据分块（chunk by chunk）拷贝到影子表。
  5. 拷贝完成后，通过原子性的 RENAME TABLE 操作将原表和影子表对调，最后删除旧表。
- 优点：整个过程对业务应用几乎无感知，仅在最后 RENAME 阶段有秒级或毫秒级的锁。
DROP / TRUNCATE 的安全策略：
- 禁止直接 DROP：为防止误操作，生产环境通常会回收普通开发人员的 DROP 权限。
- “软删除”策略：将不再使用的表 RENAME 为 t_to_drop_YYYYMMDD，观察一段时间确认无影响后再于维护窗口期 DROP。
- 谨慎使用 TRUNCATE：由于其不可回滚的特性，TRUNCATE 权限也应严格控制。在脚本中使用时，必须有日志记录和二次确认机制。
原子 DDL (Atomic DDL)：MySQL 8.0 引入了对 DROP TABLE, TRUNCATE TABLE, RENAME TABLE 等操作的原子性支持。这意味着 DDL 操作要么完全成功，要么完全失败（例如，服务器在操作中途崩溃），不会出现中间状态（如只删除了一部分数据文件），增强了数据字典的一致性和操作的可靠性。

常见追问与延伸题目