mysql复制表的两种方法

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)

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

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)

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)

查看结构MySQL 复制表，结构一致

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)

复制数据

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)

复制完成

（编辑：武汉站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!