mysql复制表的两种方法_mysql新建copy表

mysql复制表的两种方法

1.creat table “a” select * from b;

例：创建表1并将表二的数据复制到表1
不过此方法在复制后不会将原有字段属性（如primary key、Extra（auto_increment）等属性）复制过来，需要自己添加，且容易弄错，不推荐使用

mysql> select * from student;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 1    |    1 |
|    2 | 2    |    2 |
|    3 | 3    |    3 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> create table aaa select * from student;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from aaa;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 1    |    1 |
|    2 | 2    |    2 |
|    3 | 3    |    3 |
+------+------+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

在复制完成后查看表结构，发现原属性没有复制过来

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   |     | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> desc aaa;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | 0       |       |
| name  | varchar(128) | NO   |     | NULL    |       |
| age   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.先复制结构，在复制数据，完成数据和结构都一致

create table a like b
不复制数据 只复制结构

mysql> create table a1 like student;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from a1;
Empty set (0.00 sec)
1
2
3
4

查看结构，结构一致

mysql> desc student;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   |     | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> desc a1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(128) | NO   |     | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

复制数据

mysql> insert into a1 select * from student;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from a1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | 1    |    1 |
|    2 | 2    |    2 |
|    3 | 3    |    3 |
+------+------+------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

复制完成