mysql复制表的两种方法

阳光穿透心脏的1/2处 2023-01-03 12:55 131阅读 0赞

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> 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)

复制完成