MySQL复制一张表

SQL语句

创建相同字段、索引的表（不包含数据）

create table if not exists 新表名称 like 旧表名称;

创建相同字段、无索引的表（包含数据）

create table if not exists 旧表名称 select * from 旧表名称;

创建相同字段、相同索引、相同数据的表

create table 旧表名称 like 旧表名称;
insert into 旧表名称 select * from 旧表名称;

样例展示

1. 创建用户表

drop table if exists user;
create table user(
	id int auto_increment,
	name varchar(20) null,
	age int null,
    phone varchar(18) unique,
	constraint user_pk primary key (id)
);

2. 插入数据

insert into user(name, age, phone) values ('zs', 18, '10000'), ('ls', 17, '10010'), ('ww', 16, '10086');

3. 查看表结构

desc user;

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | <null>  | auto_increment |
| name  | varchar(20) | YES  |     | <null>  |                |
| age   | int(11)     | YES  |     | <null>  |                |
| phone | varchar(18) | YES  | UNI | <null>  |                |
+-------+-------------+------+-----+---------+----------------+

4. 查看数据

select * from user;

+----+------+-----+-------+
| id | name | age | phone |
+----+------+-----+-------+
| 1  | zs   | 18  | 10000 |
| 2  | ls   | 17  | 10010 |
| 3  | ww   | 16  | 10086 |
+----+------+-----+-------+

5. 创建相同字段、索引的表（不包含数据）

create table user_bak like user;

6. 查看备份表结构及索引

desc user_bak;

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | <null>  | auto_increment |
| name  | varchar(20) | YES  |     | <null>  |                |
| age   | int(11)     | YES  |     | <null>  |                |
| phone | varchar(18) | YES  | UNI | <null>  |                |
+-------+-------------+------+-----+---------+----------------+

7. 将原表数据插入新表

insert into user_bak select * from user;

8. 查看新表数据

select * from user_bak;

+----+------+-----+-------+
| id | name | age | phone |
+----+------+-----+-------+
| 1  | zs   | 18  | 10000 |
| 2  | ls   | 17  | 10010 |
| 3  | ww   | 16  | 10086 |
+----+------+-----+-------+

9. 创建相同字段、相同数据的表，不需要索引

create table user_bak2 select * from user;

10. 查看表结构

desc user_bak2;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | 0       |       |
| name  | varchar(20) | YES  |     | <null>  |       |
| age   | int(11)     | YES  |     | <null>  |       |
| phone | varchar(18) | YES  |     | <null>  |       |
+-------+-------------+------+-----+---------+-------+

11. 查看数据

select * from user_bak2;

+----+------+-----+-------+
| id | name | age | phone |
+----+------+-----+-------+
| 1  | zs   | 18  | 10000 |
| 2  | ls   | 17  | 10010 |
| 3  | ww   | 16  | 10086 |
+----+------+-----+-------+