CREATE TABLE … LIKE¶
CREATE TABLE … LIKE 根据另一个表的定义创建一个空表，它会复制原始表的结构，但不会复制原始表中存储的数据。
语法说明¶
CREATE TABLE ... LIKE 根据另一个表的定义创建一个空表，它会复制原始表的结构，但不会复制原始表中存储的数据。
语法结构¶
CREATE TABLE [IF NOT EXISTS] new_tbl LIKE orig_tbl;
可选的 IF NOT EXISTS 子句使操作幂等：如果目标表已存在，语句静默跳过，而不是抛出 ERROR 1050。这同时适用于普通表和 TEMPORARY 表。副本继承源表的完整结构（列、类型、默认值、键、索引、外键），但不包含数据。
示例¶
基本 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 使复制操作幂等。如果目标表已存在，语句静默跳过；否则正常创建表。
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');
-- 首次调用创建副本表
CREATE TABLE IF NOT EXISTS foo_shadow LIKE foo;
SHOW CREATE TABLE foo_shadow;
-- 重复调用为静默空操作
CREATE TABLE IF NOT EXISTS foo_shadow LIKE foo;
CREATE TABLE IF NOT EXISTS foo_shadow LIKE foo;
SHOW TABLES;
-- 副本表是独立的空表
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;