CREATE TABLE ... LIKE
Syntax Description
CREATE TABLE ... LIKE Create an empty table based on the definition of another table, which copies the structure of the original table but not the data stored in the original table.
Syntax structure
CREATE TABLE new_tbl LIKE orig_tbl;
Examples
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)