CREATE TABLE … LIKE

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 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 [IF NOT EXISTS] new_tbl LIKE orig_tbl;

The optional IF NOT EXISTS clause makes the operation idempotent: if the target table already exists, the statement becomes a silent no-op instead of raising ERROR 1050. This works for both regular and TEMPORARY tables. The copy inherits the full schema (columns, types, defaults, keys, indexes, foreign keys) from the source table but contains no data.

Examples

Basic LIKE

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)

IF NOT EXISTS LIKE

CREATE TABLE IF NOT EXISTS ... LIKE makes the copy operation idempotent. If the target table already exists, the statement is a no-op; otherwise, it creates the table as usual.

DROP DATABASE IF EXISTS t25119;
CREATE DATABASE t25119;
USE t25119;

CREATE TABLE foo (id INT, name VARCHAR(20));
INSERT INTO foo VALUES (1,'a'),(2,'b');

-- First call creates the shadow table
CREATE TABLE IF NOT EXISTS foo_shadow LIKE foo;
SHOW CREATE TABLE foo_shadow;

-- Repeated calls are silent no-ops
CREATE TABLE IF NOT EXISTS foo_shadow LIKE foo;
CREATE TABLE IF NOT EXISTS foo_shadow LIKE foo;
SHOW TABLES;

-- The shadow table is an independent empty copy
DESC foo_shadow;
SELECT COUNT(*) FROM foo_shadow;

-- TEMPORARY + IF NOT EXISTS LIKE
CREATE TEMPORARY TABLE IF NOT EXISTS foo_tmp LIKE foo;
CREATE TEMPORARY TABLE IF NOT EXISTS foo_tmp LIKE foo;
SELECT COUNT(*) FROM foo_tmp;

DROP TABLE IF EXISTS foo_tmp;
DROP TABLE foo_shadow;
DROP TABLE foo;
DROP DATABASE t25119;