mysql复制一个空表_Mysql 复制一个新表

1、复制表结构及数据到新表

CREATE TABLE 新表 SELECT * FROM 旧表

这种方法会将oldtable中所有的内容都拷贝过来，当然我们可以用delete from newtable;来删除。

不过这种方法的一个最不好的地方就是新表中没有了旧表的primary key、Extra(auto_increment)等属性。需要自己用"alter"添加，而且容易搞错。

2、只复制表结构到新表

CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2

或CREATE TABLE 新表  LIKE 旧表

这个和上面一样主键索引和一些属性都会消失。

原来的表：

MariaDB [syw]> desc news_person;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(30) | NO | | NULL | |

| age | int(11) | NO | | NULL | |

+-------+-------------+------+-----+---------+----------------+

3 rows in set (0.05 sec)

执行后：

MariaDB [syw]> create table news_person_new select * from news_person where 1=2;

Query OK, 0 rows affected (0.49 sec)

Records: 0 Duplicates: 0 Warnings: 0

查看索引：

MariaDB [syw]> show index from news_person;

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| news_person | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |

+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)

MariaDB [syw]> show index from news_person_new;

Empty set (0.00 sec)

正确方法：

CREATE TABLE 新表

LIKE 旧表

MariaDB [syw]> create table news_person_new1 like news_person;

Query OK, 0 rows affected (0.24 sec)

MariaDB [syw]> desc news_person_new1;

+-------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(30) | NO | | NULL | |

| age | int(11) | NO | | NULL | |

+-------+-------------+------+-----+---------+----------------+

3 rows in set (0.03 sec)

MariaDB [syw]> show index from news_person_new1;

+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| news_person_new1 | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |

+------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1 row in set (0.00 sec)