【MySQL 笔记】表操作（DDL – Data Definition Language）详解
MySQL 中对表的操作主要属于 DDL（数据定义语言），包括创建表、修改表、删除表、重命名表、复制表、查看表结构等。
下面按常用程度和重要性排序，逐一讲解。
一、核心表操作命令速查表
| 操作 | 命令 | 主要用途 | 是否可回滚（事务内） | 是否锁表（大表时） | 常用场景 |
|---|---|---|---|---|---|
| 创建表 | CREATE TABLE | 定义新表结构 | 否 | 否 | 新建业务表 |
| 修改表结构 | ALTER TABLE | 加/删/改列、改约束、改引擎、改字符集 | 否 | 是（多数操作） | 需求变更、字段调整 |
| 删除表 | DROP TABLE | 永久删除表及数据 | 否 | 短暂 | 清理测试表、卸载模块 |
| 清空表数据 | TRUNCATE TABLE | 快速清空所有数据（保留表结构） | 否 | 是 | 重置自增、清理大表 |
| 重命名表 | RENAME TABLE / ALTER TABLE … RENAME | 修改表名 | 否 | 短暂 | 表名规范化、版本迁移 |
| 复制表结构+数据 | CREATE TABLE … LIKE / SELECT | 快速复制表 | 否 | 视数据量 | 备份、分表、测试环境 |
| 查看表结构 | SHOW CREATE TABLE / DESC / EXPLAIN | 查看建表语句或列信息 | — | 否 | 开发、运维、排查 |
二、详细语法 & 实战示例
1. 创建表（CREATE TABLE）
最完整写法（推荐生产使用）
CREATE TABLE IF NOT EXISTS order_items (
id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '自增主键',
order_id BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
product_id BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
sku_id BIGINT UNSIGNED DEFAULT NULL COMMENT 'SKU ID',
quantity INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '购买数量',
unit_price DECIMAL(10,2) NOT NULL COMMENT '单价',
subtotal DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED COMMENT '小计（计算列）',
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间',
PRIMARY KEY (id),
INDEX idx_order_id (order_id),
INDEX idx_product_id (product_id),
CONSTRAINT fk_order_items_order
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_order_items_product
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT ON UPDATE CASCADE
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
COMMENT='订单明细表';
常用变体：
-- 只复制结构（不带数据）
CREATE TABLE users_bak LIKE users;
-- 复制结构 + 数据（可加 WHERE 过滤）
CREATE TABLE users_2025 AS
SELECT * FROM users
WHERE created_at >= '2025-01-01';
2. 修改表（ALTER TABLE）—— 最复杂的操作
-- 1. 添加列
ALTER TABLE users
ADD COLUMN last_login_ip VARCHAR(45) DEFAULT NULL AFTER last_login_time,
ADD COLUMN is_vip TINYINT(1) DEFAULT 0 AFTER status;
-- 2. 修改列定义（类型、默认值、注释等）
ALTER TABLE products
MODIFY COLUMN price DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '售价（升级精度）';
-- 3. 重命名列
ALTER TABLE orders
CHANGE COLUMN amt amount DECIMAL(10,2) NOT NULL COMMENT '订单金额';
-- 4. 删除列（不可恢复！）
ALTER TABLE temp_table DROP COLUMN temp_field;
-- 5. 添加/删除索引
ALTER TABLE users ADD INDEX idx_mobile (mobile);
ALTER TABLE users DROP INDEX idx_mobile;
-- 6. 添加/删除主键（极少用，大表慎重）
ALTER TABLE old_table ADD PRIMARY KEY (id);
ALTER TABLE old_table DROP PRIMARY KEY;
-- 7. 修改表引擎 / 字符集（常见迁移操作）
ALTER TABLE users ENGINE = InnoDB;
ALTER TABLE logs CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 8. 修改表名（两种写法等价）
RENAME TABLE users TO users_v2;
ALTER TABLE users_v2 RENAME TO users;
大表 ALTER TABLE 注意事项（2026 年视角）
- MySQL 8.0+ 大部分 ALTER 操作支持 在线 DDL（inplace），但仍可能短暂锁表。
- 推荐做法：用 pt-online-schema-change / gh-ost 等工具做无锁变更。
- 永远先在测试环境验证 ALTER 语句的执行时间和锁情况。
3. 删除表 & 清空表
-- 删除表（不可恢复）
DROP TABLE IF EXISTS temp_table;
-- 删除多张表
DROP TABLE t1, t2, t3;
-- 快速清空数据（保留表结构，自增重置为1）
TRUNCATE TABLE log_temp;
-- 对比：DELETE FROM 不重置自增，且可 WHERE，可回滚
DELETE FROM log_temp WHERE create_time < '2025-01-01';
4. 查看表信息（开发/运维必备）
-- 1. 查看建表语句（最完整）
SHOW CREATE TABLE users\G
-- 2. 查看列信息（简洁）
DESC users;
DESCRIBE users;
-- 3. 查看所有表
SHOW TABLES;
SHOW TABLES LIKE 'order%';
-- 4. 查看表状态（行数、数据大小、索引大小等）
SHOW TABLE STATUS LIKE 'users';
-- 5. 查看表分区（如果用了分区表）
SHOW CREATE TABLE sales PARTITIONS;
三、生产中表操作最佳实践总结
- 建表必须写 COMMENT（字段注释 + 表注释）
- 统一字符集：utf8mb4_unicode_ci（2026 年主流）
- 主键优先用 BIGINT UNSIGNED AUTO_INCREMENT
- 字段命名：下划线命名法，见名知意
- 重要表加 created_at / updated_at（自动维护）
- 避免频繁 ALTER TABLE 大表（提前规划好字段）
- 测试环境先跑 DROP / TRUNCATE / ALTER，确认无误
- 核心业务表建议加 软删除（is_deleted + deleted_at）
下一期预告建议：MySQL 索引全家桶（上）—— 索引类型 + 创建时机 + 复合索引最左匹配原则
有哪种表结构变更、复制表、或 ALTER 操作你觉得特别麻烦，或者项目里常见的表操作模板，欢迎留言讨论～
祝大家表操作稳准狠，不乱改结构！🛠️
