首页 / MySQL 入门教程 / 删除表、临时表与复制表

← 上一篇 下一篇 →
MySQL 入门教程

删除表、临时表与复制表

本教程共 46 篇 · 第 14 篇 · 更新于 2026-07-30 · 约 13 分钟阅读

MySQLMySQL 入门教程DROP TABLE临时表复制表CREATE TEMPORARYDDL

14. 删除表、临时表与复制表

本节目标：学会用 DROP TABLE 删表（含 IF EXISTS 和 TEMPORARY），理解临时表的特性与生命周期，掌握复制表结构（LIKE）和复制表数据（AS SELECT）两种方式，以及它们的区别。

建表、改表都讲了，这一节把表操作收尾：怎么删表、怎么用临时表、怎么复制一张表。这些都是日常会用到的操作。

14.1 DROP TABLE：删除表

DROP TABLE 把表连同结构和数据一起删除，是彻底的删除。

基本语法：

DROP TABLE 表名;
DROP TABLE users;

成功返回 Query OK，表就不存在了。

删多张表

一条语句删多张表，用逗号分隔：

DROP TABLE table1, table2, table3;

IF EXISTS：避免报错

删一个不存在的表会报错：

mysql> DROP TABLE aliens;
ERROR 1051 (42S02): Unknown table 'shop.aliens'

加 IF EXISTS，不存在时只给警告，不报错：

DROP TABLE IF EXISTS aliens;
-- Query OK, 0 rows affected, 1 warning (0.00 sec)
Tip

写脚本时用 DROP TABLE IF EXISTS 是好习惯，能让脚本可重复执行。用 SHOW WARNINGS; 可以看到那条”表不存在”的警告。

DROP TABLE 的特点

  • 不可逆：表结构、数据、索引全没，没法 undo；
  • 不删权限：表的权限记录还在，下次建同名表会自动套用（这点要注意，可能是安全隐患）；
  • 需要权限：执行者要有该表的 DROP 权限；
  • RESTRICT 和 CASCADE 关键字是保留字，目前不生效（为未来预留）。
Warning

DROP TABLE 比 DROP DATABASE 精确，但依然危险。执行前三确认：确认表名、确认库、确认不是生产误操作。MySQL 没有”回收站”，删了就是真删了。

14.2 DROP 和 TRUNCATE 和 DELETE 的区别

这三个都能”清空数据”，但本质不同，新手经常搞混：

操作类型删什么能回滚吗重置自增速度
DELETE FROM tDML删行（可带 WHERE）事务内能不重置慢（逐行）
TRUNCATE TABLE tDDL清空所有行不能重置为 1快
DROP TABLE tDDL删表+数据不能-快
-- 只删数据，表还在
DELETE FROM users;        -- 逐行删，能 WHERE，自增不重置
TRUNCATE TABLE users;     -- 清空表，自增重置，快

-- 表也删了
DROP TABLE users;
Note
  • 想清空表数据但保留表结构 -> TRUNCATE TABLE；
  • 想删除表（连结构） -> DROP TABLE；
  • 想按条件删部分数据 -> DELETE FROM ... WHERE。
    TRUNCATE 后面 DML 章节会再细讲。

14.3 临时表：CREATE TEMPORARY TABLE

临时表（Temporary Table） 是一种特殊的表，只在当前会话可见，会话结束自动删除。

基本语法

在 CREATE 和 TABLE 之间加 TEMPORARY：

CREATE TEMPORARY TABLE temp_users (
    id INT,
    name VARCHAR(50)
);

用法和普通表一样：能 INSERT、SELECT、UPDATE、DELETE。

临时表的特性

临时表有几个独特性质：

  1. 会话隔离：只有创建它的那个连接能看见，别的连接看不到，即使同名也不冲突；
  2. 自动删除：会话结束（断开连接）时，临时表自动消失，不用手动删；
  3. 可同名：可以建一个和普通表同名的临时表，这时普通表会被”遮住”，所有操作都作用在临时表上：
-- 已有普通表 users
CREATE TEMPORARY TABLE users (
    id INT,
    name VARCHAR(50)
);

-- 这条 INSERT 进的是临时表，不是普通表
INSERT INTO users VALUES(1, 'test');

-- 删掉临时表后，普通表 users 重新可见
DROP TEMPORARY TABLE users;
  1. 手动删除：用 DROP TEMPORARY TABLE 删（推荐加 TEMPORARY，避免误删普通表）。
Warning

临时表和普通表同名是个坑：你以为是操作普通表，其实在改临时表，断开后发现数据没变。别给临时表起和普通表一样的名字，自找麻烦。

临时表的典型用途

临时表适合存”中间结果”，比如复杂查询的分步处理：

-- 把查询结果存进临时表
CREATE TEMPORARY TABLE top_buyers
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
ORDER BY total DESC
LIMIT 10;

-- 再基于临时表做下一步查询
SELECT u.username, t.total
FROM top_buyers t
JOIN users u ON u.id = t.user_id;

-- 用完手动删（不删也会话结束自动删）
DROP TEMPORARY TABLE top_buyers;
Tip

用连接池的应用要注意：连接复用时临时表可能没及时清理。用完临时表主动 DROP TEMPORARY TABLE 是好习惯，别全指望会话结束自动删。

14.4 复制表结构：CREATE TABLE LIKE

有时候你想建一张和现有表结构一模一样的新表（连索引都复制），但不要数据。用 CREATE TABLE ... LIKE：

CREATE TABLE new_users LIKE users;

这条会：

  • 复制所有列定义（名称、类型、约束）；
  • 复制索引（主键、唯一键、普通索引）；
  • 复制表选项（字符集、引擎）；
  • 不复制数据（新表是空的）；
  • 不复制外键、触发器、自增起始值（自增属性保留，但计数从 1 开始）。

验证：

SHOW CREATE TABLE new_users\G

会发现结构和 users 一模一样，但 AUTO_INCREMENT 没了数据。

Note

CREATE TABLE LIKE 只复制结构不复制数据，是”克隆表骨架”的标准做法。注意它不复制外键约束，因为外键涉及其他表。

14.5 复制表数据：CREATE TABLE AS SELECT

如果想既复制结构又复制数据，或者只复制部分列，用 CREATE TABLE ... AS SELECT：

-- 复制整张表（结构 + 数据）
CREATE TABLE users_backup
AS SELECT * FROM users;

-- 只复制部分列和部分行
CREATE TABLE vip_users
AS SELECT id, username, email
FROM users
WHERE age > 18;

AS SELECT 的特点：

  • 新表的列由 SELECT 决定；
  • 会复制数据（SELECT 出来的行）；
  • 不复制索引、主键、约束（这是和 LIKE 的关键区别）；
  • 列的数据类型从查询结果推断。

LIKE 和 AS SELECT 的区别

这是新手容易混淆的，对比一下：

对比项CREATE TABLE LIKECREATE TABLE AS SELECT
复制结构完整（含索引、约束）只复制列名和类型
复制数据不复制复制
复制索引复制不复制
复制主键复制不复制
灵活性只能全复制能选列、选行
Tip

想要”结构+数据”都完整复制，要分两步：先 CREATE TABLE new LIKE old 复制结构，再 INSERT INTO new SELECT * FROM old 复制数据。一步 AS SELECT 会丢索引和主键。

14.6 完整复制表的标准做法

把上面两种结合，给一个”完全复制一张表”的可靠流程：

-- 第一步：复制结构（含索引、主键、约束）
CREATE TABLE users_copy LIKE users;

-- 第二步：复制数据
INSERT INTO users_copy
SELECT * FROM users;

-- 验证
SELECT COUNT(*) FROM users;       -- 原表行数
SELECT COUNT(*) FROM users_copy;  -- 应该一样
SHOW CREATE TABLE users_copy\G    -- 结构应和原表一致

这两步走完，users_copy 在结构、索引、数据上都和 users 一致（外键除外）。

14.7 用 SHOW CREATE TABLE 手动复制

第三种复制方式，适合需要改一点点结构再建的场景：

-- 1. 看原表的建表语句
SHOW CREATE TABLE users\G

输出完整的 CREATE TABLE 语句。复制这段语句，把表名改成新名字，按需调整，再执行：

-- 2. 粘贴并修改表名后执行
CREATE TABLE `users_archive` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `age` tinyint unsigned DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. 复制数据
INSERT INTO users_archive SELECT * FROM users;
Note

这种方式最灵活，能任意修改结构（加列、改类型、改字符集）。缺点是要手动粘贴改，适合偶尔操作，不适合自动化。mysqldump 工具导出再导入也是类似原理，适合大批量迁移。

14.8 复制表的注意事项

复制表时几个容易忽略的点：

  1. 自增列：LIKE 会保留自增属性但重置计数；AS SELECT 不保留自增属性；
  2. 外键：三种方式都不复制外键，要手动重建；
  3. 触发器、视图、存储过程：都不复制，依赖这些的对象要单独处理；
  4. 权限：新表不继承旧表的权限，要重新 GRANT；
  5. 字符集：LIKE 复制字符集；AS SELECT 用服务器/库默认。

14.9 一个综合示例

演示临时表 + 复制表的组合用法：

USE shop;

-- 1. 建一张结构备份（不含数据）
CREATE TABLE users_2026 LIKE users;

-- 2. 用临时表做中间计算
CREATE TEMPORARY TABLE recent_orders
SELECT user_id, COUNT(*) AS order_cnt
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY user_id;

-- 3. 把"今年下过单的用户"复制进备份表
INSERT INTO users_2026
SELECT u.*
FROM users u
JOIN recent_orders r ON u.id = r.user_id;

-- 4. 查看结果
SELECT COUNT(*) AS backed_up FROM users_2026;

-- 5. 清理临时表
DROP TEMPORARY TABLE recent_orders;

14.10 小结

这一节讲完了表的删除、临时和复制：

  • DROP TABLE [IF EXISTS] 删表，不可逆，删前三确认；
  • DROP（删表）、TRUNCATE（清空数据保结构）、DELETE（按条件删行）三者要分清；
  • 临时表会话级可见、断开自动删，用 CREATE TEMPORARY TABLE，用完主动 DROP TEMPORARY TABLE；
  • CREATE TABLE LIKE 复制结构（含索引），不复制数据；
  • CREATE TABLE AS SELECT 复制数据，不复制索引约束；
  • 完整复制 = LIKE 建结构 + INSERT ... SELECT 填数据；
  • 自增、外键、触发器、权限都不会自动复制，要单独处理。

下一节讲自增列和生成列这两个实用的列属性。

上一篇
修改表结构 ALTER TABLE
下一篇
自增列与生成列