MySQL实操指南之复制表及数据复制全解析

来源：互联网 2026-04-21 10:34:39

MySQL复制表与数据：从基础到进阶，一篇讲透所有高频场景在MySQL的日常开发和运维工作中，复制表和数据堪称“高频刚需”。无论是备份关键数据、创建测试环境，还是同步表结构，都离不开它。其中，create table xxx select * from aaa;这条语句大家肯定不陌生，但你真的了解

MySQL复制表与数据：从基础到进阶，一篇讲透所有高频场景

在MySQL的日常开发和运维工作中，复制表和数据堪称“高频刚需”。无论是备份关键数据、创建测试环境，还是同步表结构，都离不开它。其中，create table xxx select * from aaa;这条语句大家肯定不陌生，但你真的了解它的全部细节吗？复制后主键和索引去哪儿了？如何只“克隆”结构不要数据？今天，我们就来彻底拆解MySQL中复制表和数据的各种语法，结合实操案例，让你一次搞懂，远离踩坑。

MySQL实操指南之复制表及数据复制全解析

长期稳定更新的攒劲资源： >>>点此立即查看<<<

一、最常用：create table xxx select * from aaa（复制表+数据）

先从这条最“出圈”的语法说起。create table xxx select * from aaa; 这行代码干了一件什么事呢？简单说，就是创建一张名叫xxx的新表，然后把原表aaa的结构和数据，一股脑儿全搬过去。

1. 实操案例（直接可用）

假设我们手头有一张用户表user_info，结构和数据长这样（你可以直接复制代码到环境里测试）：

-- 原表：user_info
create table user_info(
    id int primary key auto_increment,
    username varchar(50) not null,
    age int,
    create_time datetime default current_timestamp
);
-- 插入测试数据
insert into user_info(username, age) values('zhangsan', 22),('lisi', 25),('wangwu', 28);

现在，我们用那条经典语法来复制它，得到一张新表user_info_copy：

-- 复制表结构+所有数据
create table user_info_copy select * from user_info;

执行完成后，查一下新表，你会发现数据和原表一模一样：

mysql> select * from user_info_copy;
+----+----------+-----+---------------------+
| id | username | age | create_time         |
+----+----------+-----+---------------------+
|  1 | zhangsan |  22 | 2026-04-20 10:00:00 |
|  2 | lisi     |  25 | 2026-04-20 10:00:00 |
|  3 | wangwu   |  28 | 2026-04-20 10:00:00 |
+----+----------+-----+---------------------+
3 rows in set (0.00 sec)

2. 关键注意点（必看避坑）

语法虽然简单，但这里有三个“坑点”需要特别注意，记牢了能省去很多麻烦：

首先，主键、自增、索引统统不会复制。新表user_info_copy只是字段和数据跟原表一样，但原表的主键约束、自增属性、索引这些“高级配置”都丢了。用desc命令看一眼表结构就明白了：

mysql> desc user_info_copy;
+-------------+-------------+------+-----+---------------------+-------+
| Field       | Type        | Null | Key | Default             | Extra |
+-------------+-------------+------+-----+---------------------+-------+
| id          | int         | YES  |     | NULL                |       |  -- 没有主键、不自增
| username    | varchar(50) | NO   |     | NULL                |       |
| age         | int         | YES  |     | NULL                |       |
| create_time | datetime    | YES  |     | current_timestamp   |       |
+-------------+-------------+------+-----+---------------------+-------+

其次，默认值会保留，但特殊约束可能丢失。像字段的默认值（比如create_time的current_timestamp）能带过来，但外键约束、唯一约束这些就没了。
最后，复制所有数据，但可以通过where筛选。如果你不想全盘复制，可以在后面加上where条件，只复制符合要求的数据。比如，只复制年龄大于25的用户：

-- 只复制age>25的数据，不复制全量
create table user_info_copy2 select * from user_info where age > 25;

二、进阶用法：只复制表结构，不复制数据

很多情况下，我们只想“克隆”表的结构（比如字段名、类型），但不需要里面的数据（创建一张空的测试表时尤其如此）。这时候，就需要对上面的语法做个小手术——在select后面加上where 1=0。这个条件永远为假，所以查不出任何数据，自然就只复制结构了。

1. 语法及案例

-- 只复制表结构，不复制数据（核心：where 1=0）
create table user_info_struct select * from user_info where 1=0;

执行后，新表user_info_struct拥有和原表完全一致的字段结构，但里面是空的：

mysql> select * from user_info_struct;
Empty set (0.00 sec)
mysql> desc user_info_struct;
+-------------+-------------+------+-----+---------------------+-------+
| Field       | Type        | Null | Key | Default             | Extra |
+-------------+-------------+------+-----+---------------------+-------+
| id          | int         | YES  |     | NULL                |       |
| username    | varchar(50) | NO   |     | NULL                |       |
| age         | int         | YES  |     | NULL                |       |
| create_time | datetime    | YES  |     | current_timestamp   |       |
+-------------+-------------+------+-----+---------------------+-------+

2. 补充：保留主键、索引的复制方式

如果你希望连主键、索引、自增这些约束也一并复制过来，上面的方法就力不从心了。这时，create table xxx like aaa;语法才是你的最佳选择，它能完整保留原表的所有结构定义。

-- 复制完整表结构（保留主键、索引、自增等所有约束），不复制数据
create table user_info_like like user_info;

再来验证一下表结构，可以看到主键、自增属性都完好无损：

mysql> desc user_info_like;
+-------------+-------------+------+-----+---------------------+----------------+
| Field       | Type        | Null | Key | Default             | Extra          |
+-------------+-------------+------+-----+---------------------+----------------+
| id          | int         | NO   | PRI | NULL                | auto_increment |  -- 主键+自增保留
| username    | varchar(50) | NO   |     | NULL                |                |
| age         | int         | YES  |     | NULL                |                |
| create_time | datetime    | YES  |     | current_timestamp   |                |
+-------------+-------------+------+-----+---------------------+----------------+

三、单独复制数据：insert into xxx select * from aaa（表已存在）

如果新表已经提前建好了（并且结构和原表一致），这时候只想往里灌数据，该怎么办？答案就是insert into xxx select * from aaa;。这是表已存在时，复制数据的标准操作。

1. 实操案例

先创建一张和原表结构一致的空表（可以用上面提到的like语法），然后再把数据插进去：

-- 1. 创建空表（保留完整结构）
create table user_info_data like user_info;
-- 2. 单独复制原表所有数据到新表
insert into user_info_data select * from user_info;

执行后，新表不仅数据和老表一样，表结构也是完整的（主键、自增都在）。

2. 常用拓展（实用场景）

场景一：只复制部分字段。不需要所有字段时，可以明确指定要复制的字段列表（注意顺序和类型要与新表匹配）：

-- 只复制username和age两个字段的数据
insert into user_info_data(username, age) select username, age from user_info;

场景二：复制数据并去重。如果原表数据有重复，复制时加上distinct关键字，可以避免新表出现重复记录：

-- 复制数据并去重（避免新表出现重复记录）
insert into user_info_data select distinct * from user_info;

场景三：条件复制数据。和之前一样，可以配合where条件，只复制符合要求的数据：

-- 只复制username包含"li"的用户数据
insert into user_info_data select * from user_info where username like '%li%';

四、其他常用复制场景（补充）

除了上述核心操作，还有两个比较高频的场景，大家可以根据需要选用。

1. 跨数据库复制表

如果需要把A数据库的表复制到B数据库，操作也很简单，在表名前加上数据库名前缀就行：

-- 从db1数据库的user_info，复制到db2数据库，创建user_info_copy（表+数据）
create table db2.user_info_copy select * from db1.user_info;

2. 复制表并修改字段（进阶）

在复制表的同时，还可以对字段进行一些简单的修改，比如改个字段名、调整下默认值，这适合快速创建一张结构类似的“变体”表：

-- 复制表，同时将username字段改为name，age字段默认值设为18
create table user_info_modify select id, username as name, ifnull(age, 18) as age, create_time from user_info;

五、常见坑汇总（避坑必看）

最后，集中梳理一下大家最容易踩的四个坑，看完能少走不少弯路：

坑1：create table xxx select * from aaa 不复制主键、索引、自增，它只复制字段和数据。如果需要保留这些约束，请使用like语法。

坑2：复制数据时，务必确保新表的字段类型、顺序和select查询出来的结果集完全一致，否则很容易报类型不匹配或列数不对的错误。

坑3：自增字段复制后，新表的自增值起始点不会继承原表当前的最大值，如果需要连续，得手动用alter table xxx auto_increment = 起始值;来调整。

坑4：复制海量数据时，一定要避开业务高峰期。这种操作会占用较多的数据库I/O和锁资源，可能影响线上业务的正常运行。

六、总结（快速选型指南）

为了方便大家快速回顾和选型，我们把不同场景下的推荐语法整理成了下面这个表格，一目了然：

需求场景	推荐语法	关键说明
复制表结构+全量数据	create table xxx select * from aaa;	不保留主键、索引
只复制表结构（保留所有约束）	create table xxx like aaa;	不复制数据，保留主键、自增、索引
只复制表结构（不保留约束）	create table xxx select * from aaa where 1=0;	不复制数据，不保留约束
表已存在，单独复制数据	insert into xxx select * from aaa;	可加where、distinct筛选
跨数据库复制表	create table db2.xxx select * from db1.aaa;	加上数据库名前缀