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)