假设存在以下Table：
mysql> select * from staff; +----+----------+-------+ | id | name | slary | +----+----------+-------+ | 3 | haofugui | 10000 | | 4 | guoming | 3500 | | 5 | haotian | 2900 | +----+----------+-------+ 3 rows in set (0.00 sec) mysql> describe staff; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(20) | YES | | NULL | | | slary | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
1. 只复制表结构到新表
语句1：CREATE TABLE new_table_name SELECT [field1,field2... | *] FROM old_table_name WHERE 1=2;
语句2：CREATE TABLE new_table _name LIKE old_table_name;
示例：
mysql> create table staff_bak select id,name from staff where 1=2; //根据旧表的指定属性创建一个新的空表 Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from staff_bak; //新建的数据库为空表 Empty set (0.00 sec) mysql> describe staff_bak; //原表的主键和自动增长不能被复制 +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | name | char(20) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql> create table staff_bak_1 like staff; //根据旧表创建一个新的空表，无法指定属性或属性组 Query OK, 0 rows affected (0.03 sec) mysql> select * from staff_bak_1; Empty set (0.00 sec) mysql> describe staff_bak_1; //所有数据类型和完整性约束条件都能被复制，包括主键和自动增长 +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(20) | YES | | NULL | | | slary | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
注意：语句1可指定复制的属性范围，但无法复制主键类型和自增方式；
语句2会把旧表的所有字段类型都复制到新表，但无法复制指定属性或属性组。
2. 复制表结构及数据到新表
语句：CREATE TABLE new_table_name SELECT [field1,field2... | *] FROM old_table_name;
mysql> create table staff_bak select id,name from staff; //根据旧表将指定属性及其数据创建新表 Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> describe staff_bak; //新表结构展示 +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | name | char(20) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from staff_bak; //新表数据显示 +----+----------+ | id | name | +----+----------+ | 3 | haofugui | | 4 | guoming | | 5 | haotian | +----+----------+ 3 rows in set (0.00 sec)