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

在mysql日常开发和运维中，复制表和复制数据是高频操作——比如备份表数据、创建测试表、同步核心表结构及数据等场景，都离不开相关语法。其中create table xxx select * from aaa;是最常用的复制表方式，但很多同学只知其然，不知其所以然，比如复制后主键、索引会丢失吗？如何只复制结构不复制数据？如何只复制部分数据？

今天这篇博客，就带大家吃透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. 关键注意点（必看避坑）

这行语法虽然简单，但有3个容易踩坑的点，一定要记牢：

主键、自增、索引不会被复制：新表user_info_copy虽然字段类型和数据和原表一致，但原表的主键约束（primary key）、自增属性（auto_increment）、索引（index）都会丢失。我们用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）会保留，但外键约束（foreign key）、唯一约束（unique）等会丢失。
复制所有数据，可通过where筛选：如果不想复制所有数据，可在后面加where条件，只复制符合条件的数据（比如只复制age > 25的用户）：

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

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

很多场景下，我们只需要复制原表的结构（字段、类型、约束），不需要复制数据（比如创建测试表、备用表），这时候就需要对上面的语法做一点修改——在select后面加where 1=0（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;

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

整理了4个大家最容易踩的坑，看完少走弯路：

坑1：create table xxx select * from aaa 不复制主键、索引、自增，仅复制字段和数据，需保留约束用like语法。

坑2：复制数据时，新表字段类型、顺序必须和select查询的字段一致，否则会报错（比如新表age是int，原表查询字段是varchar）。

坑3：自增字段复制后，新表的自增起始值不会继承原表，需要手动用alter table xxx auto_increment = 起始值调整。

坑4：复制大量数据时，尽量避免在业务高峰期执行，会占用数据库资源，影响业务正常运行。

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

最后用一张表格，帮大家快速理清不同场景该用哪种语法，一目了然：

需求场景	推荐语法	关键说明
复制表结构+全量数据	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;	加上数据库名前缀