CREATE TABLE ... LIKE

语法说明

CREATE TABLE ... LIKE 根据另一个表的定义创建一个空表，它会复制原始表的结构，但不会复制原始表中存储的数据。

语法结构

CREATE TABLE new_tbl LIKE orig_tbl;

示例

create table test1 (a int primary key, b varchar(5) unique key);
create table test2 (a int primary key,b varchar(5) unique key,c double DEFAULT 0, d char,e int, foreign key(e) references foreign01(a), unique index(c,d));
insert into test1 values (101,'abc'),(102,'def');
insert into test2 values (1,'zs1',1,'a',101),(2,'zs2',2,'b',102);

mysql> create table test3 like test2;
Query OK, 0 rows affected (0.02 sec)

mysql> show CREATE TABLE test2;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
`a` INT NOT NULL,
`b` VARCHAR(5) DEFAULT NULL,
`c` DOUBLE DEFAULT 0,
`d` CHAR(1) DEFAULT NULL,
`e` INT DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`),
UNIQUE KEY `c` (`c`,`d`),
CONSTRAINT `018eb74f-38f3-7eb4-80c1-95d9c65de706` FOREIGN KEY (`e`) REFERENCES `foreign01` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT
) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show CREATE TABLE test3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test3 | CREATE TABLE `test3` (
`a` INT NOT NULL,
`b` VARCHAR(5) DEFAULT null,
`c` DOUBLE DEFAULT 0,
`d` CHAR(1) DEFAULT null,
`e` INT DEFAULT null,
PRIMARY KEY (`a`),
UNIQUE KEY `b` (`b`),
UNIQUE KEY `c` (`c`,`d`),
CONSTRAINT `018eb74f-38f3-7eb4-80c1-95d9c65de706` FOREIGN KEY (`e`) REFERENCES `foreign01` (`a`) ON DELETE RESTRICT ON UPDATE RESTRICT
) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from test2;
+------+------+------+------+------+
| a    | b    | c    | d    | e    |
+------+------+------+------+------+
|    1 | zs1  |    1 | a    |  101 |
|    2 | zs2  |    2 | b    |  102 |
+------+------+------+------+------+
2 rows in set (0.00 sec)

mysql> select * from test3;
Empty set (0.01 sec)