MySQL中的CREATE TABLE LIKE和CREATE TABLE SELECT

CREATE TABLE LIKE

• CREATE TABLE ... LIKE可以用来复制表结构，源表上的索引和约束也会复制。
• CREATE TABLE ... LIKE不能复制表数据。
• CREATE TABLE ... LIKE只能复制基表，不能复制视图。
• CREATE TABLE ... LIKE可以复制CHECK约束，但是复制后的约束名称是自动生成的。
• CREATE TABLE ... LIKE不能复制外键约束，也不会保留DATA DIRECTORY和INDEX DIRECTORY表创建参数。
• CREATE TABLE ... LIKE不会复制表的临时属性。要复制临时表请使用CREATE TEMPORARY TABLE LIKE。
• 当源表被LOCK TABLES锁定时，CREATE TABLE LIKE语句无法成功执行。

用法示例：

CREATE TABLE t1 LIKE t2;
CREATE TEMPORARY TABLE t1 LIKE t2;

CREATE TABLE SELECT

• CREATE TABLE ... SELECT可以用来复制表结构，但不会复制索引（主键也不会）。
• CREATE TABLE ... SELECT可以复制表数据（可以通过WHERE过滤）。
• CREATE TABLE ... SELECT可以复制基表，也可以复制视图。
• CREATE TABLE ... SELECT不能复制外键约束。
• CREATE TABLE ... SELECT支持在建表时定义字段，从源表复制过来的不同名字段会作为新增字段，同名的字段则会被覆盖。
• CREATE TABLE ... SELECT支持在建表时为字段定义约束和索引。

用法示例：

CREATE TABLE t1 SELECT * FROM t2 where 1=2;             --仅复制表结构
CREATE TABLE t1 SELECT * FROM t2;                       --复制全量数据
CREATE TABLE t1 SELECT name,age FROM t2 where age>30;   --复制部分数据

--复制另外两张表关联查询的结果集
CREATE TABLE t1
  SELECT artist.name, COUNT(work.artist_id) AS number_of_works
  FROM artist LEFT JOIN work ON artist.id = work.artist_id
  GROUP BY artist.id;

--复制的列作为新增字段
CREATE TABLE t1 (a int) SELECT name,age FROM t2;  

--复制的不同列作为新增字段，同名的列被覆盖      
CREATE TABLE t1 (a int, name varchar(30)) SELECT name,age FROM t2; 

--为复制过来的字段定义索引和约束
CREATE TABLE t1 (name varchar(30) not null) SELECT name,age FROM t2; 
CREATE TABLE t1 (unique(employee_id)) SELECT employee_id,name FROM t3; 

实验：

SQL> create table t2 (name varchar(30), age int not null);
SQL> create table t3 (employ_id int, name varchar(30), age int);
SQL> insert into t2 values ('Joe',35),('Rachel',31),('Monica',33);
SQL> insert into t3 values (11,'MJoe',35),(12,'MRachel',31),(13,'MMonica',33);
SQL> commit;

SQL> select * from t2;
+--------+-----+
| name   | age |
+--------+-----+
| Joe    |  35 |
| Rachel |  31 |
| Monica |  33 |
+--------+-----+
3 rows in set (0.00 sec)

SQL> select * from t3;
+-----------+---------+------+
| employ_id | name    | age  |
+-----------+---------+------+
|        11 | MJoe    |   35 |
|        12 | MRachel |   31 |
|        13 | MMonica |   33 |
+-----------+---------+------+
3 rows in set (0.00 sec)


--仅复制表结构
SQL> CREATE TABLE t1 SELECT * FROM t2 where 1=2; 
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

SQL> select * from t1;
Empty set (0.00 sec)

SQL> desc t1;
+-----------+-----------------+------+-----+---------+--------------------------+
| Field     | Type            | Null | Key | Default | Extra                    |
+-----------+-----------------+------+-----+---------+--------------------------+
| my_row_id | bigint unsigned | NO   | PRI | NULL    | auto_increment INVISIBLE |
| name      | varchar(30)     | YES  |     | NULL    |                          |
| age       | int             | NO   |     | NULL    |                          |
+-----------+-----------------+------+-----+---------+--------------------------+
3 rows in set (0.00 sec)
           
--复制的列作为新增字段
SQL> CREATE TABLE t1 (a int) SELECT name,age FROM t2; 
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

SQL> select * from t1;
+------+--------+-----+
| a    | name   | age |
+------+--------+-----+
| NULL | Joe    |  35 |
| NULL | Rachel |  31 |
| NULL | Monica |  33 |
+------+--------+-----+
3 rows in set (0.00 sec)
 
--复制的不同列作为新增字段，同名的列被覆盖      
SQL> CREATE TABLE t1 (a int, name varchar(30)) SELECT name,age FROM t2; 
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

SQL> select * from t1;
+------+--------+-----+
| a    | name   | age |
+------+--------+-----+
| NULL | Joe    |  35 |
| NULL | Rachel |  31 |
| NULL | Monica |  33 |
+------+--------+-----+
3 rows in set (0.00 sec)


--为复制过来的字段定义约束
SQL> CREATE TABLE t1 (name varchar(40) not null default 'Tony') SELECT name,age FROM t2; 
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0

SQL> desc t1;
+-----------+-----------------+------+-----+---------+--------------------------+
| Field     | Type            | Null | Key | Default | Extra                    |
+-----------+-----------------+------+-----+---------+--------------------------+
| my_row_id | bigint unsigned | NO   | PRI | NULL    | auto_increment INVISIBLE |
| name      | varchar(40)     | NO   |     | Tony    |                          |
| age       | int             | NO   |     | NULL    |                          |
+-----------+-----------------+------+-----+---------+--------------------------+
3 rows in set (0.00 sec)

--为复制过来的字段定义索引
SQL> CREATE TABLE t1 (unique(employ_id)) SELECT employ_id,name FROM t3;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

SQL> desc t1;
+-----------+-----------------+------+-----+---------+--------------------------+
| Field     | Type            | Null | Key | Default | Extra                    |
+-----------+-----------------+------+-----+---------+--------------------------+
| my_row_id | bigint unsigned | NO   | PRI | NULL    | auto_increment INVISIBLE |
| employ_id | int             | YES  | UNI | NULL    |                          |
| name      | varchar(30)     | YES  |     | NULL    |                          |
+-----------+-----------------+------+-----+---------+--------------------------+
3 rows in set (0.01 sec)

References
【1】https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html
【2】https://dev.mysql.com/doc/refman/8.0/en/create-table-like.html