MySQL create table as與create table like對比

MySQL create table as與create table like對比

在MySQL資料庫中，關於表的複製有多種方式，比如我們可以使用create table ..as .. ，也可以使用create table .. like ..方式。然而這2種不同的方式還是有些差異的，他的差異到底在哪裡呢，本文通過示範對此展開描述。

1、mysql sakila表上的結構

--actor表狀態
robin@localhost[sakila]> show table status like 'actor'\G
*************************** 1. row ***************************
          Name: actor
        Engine: InnoDB
        Version: 10
    Row_format: Compact
          Rows: 200
 Avg_row_length: 81
    Data_length: 16384
Max_data_length: 0
  Index_length: 16384
      Data_free: 0
 Auto_increment: 201
    Create_time: 2014-12-25 13:08:25
    Update_time: NULL
    Check_time: NULL
      Collation: utf8_general_ci
      Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

--actor表索引
robin@localhost[sakila]> show index from actor\G
*************************** 1. row ***************************
        Table: actor
  Non_unique: 0
    Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: actor_id
    Collation: A
  Cardinality: 200
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: actor
  Non_unique: 1
    Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 200
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

--actor表結構
robin@localhost[sakila]> desc actor;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field      | Type                | Null | Key | Default          | Extra                      |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO  | PRI | NULL              | auto_increment              |
| first_name  | varchar(45)          | NO  |    | NULL              |                            |
| last_name  | varchar(45)          | NO  | MUL | NULL              |                            |
| last_update | timestamp            | NO  |    | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

2、使用create table as方式複製表

robin@localhost[sakila]> create table actor_as as select * from actor;
Query OK, 200 rows affected (0.06 sec)
Records: 200  Duplicates: 0  Warnings: 0

robin@localhost[sakila]> desc actor_as;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field      | Type                | Null | Key | Default          | Extra                      |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO  |    | 0                |                            |
| first_name  | varchar(45)          | NO  |    | NULL              |                            |
| last_name  | varchar(45)          | NO  |    | NULL              |                            |
| last_update | timestamp            | NO  |    | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
--從上面的結果可以看出新表缺少了key資訊，以及自增列屬性 auto_increment

robin@localhost[sakila]> show table status like 'actor_as'\G
*************************** 1. row ***************************
          Name: actor_as
        Engine: InnoDB
        Version: 10
    Row_format: Compact
          Rows: 200
 Avg_row_length: 81
    Data_length: 16384
Max_data_length: 0
  Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2015-01-19 10:42:53
    Update_time: NULL
    Check_time: NULL
      Collation: utf8_general_ci
      Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

--從上面的表結構可以看出，表狀態與原表等同，僅僅是建立時間的差異，
robin@localhost[sakila]> show index from actor_as \G
Empty set (0.00 sec)

--從上面的查詢可以看出，新表沒有任何索引

3、使用create table like方式複製表

robin@localhost[sakila]> create table actor_like like actor;
Query OK, 0 rows affected (0.01 sec)

robin@localhost[sakila]> select count(*) from actor_like;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)
--從上面的查詢可知，使用like方式沒有任何資料被複製到新表

robin@localhost[sakila]> desc actor_like;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field      | Type                | Null | Key | Default          | Extra                      |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| actor_id    | smallint(5) unsigned | NO  | PRI | NULL              | auto_increment              |
| first_name  | varchar(45)          | NO  |    | NULL              |                            |
| last_name  | varchar(45)          | NO  | MUL | NULL              |                            |
| last_update | timestamp            | NO  |    | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+

robin@localhost[sakila]> show index from actor_like\G
*************************** 1. row ***************************
        Table: actor_like
  Non_unique: 0
    Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: actor_id
    Collation: A
  Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: actor_like
  Non_unique: 1
    Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

--從上面的表結構以及索引資訊可以看到，表除了沒有資料之外，結構被進行了完整複製
--下面為like方式的表插入資料
robin@localhost[sakila]> insert into actor_like select * from actor;
Query OK, 200 rows affected (0.03 sec)
Records: 200  Duplicates: 0  Warnings: 0

robin@localhost[sakila]> show index from actor_like\G
*************************** 1. row ***************************
        Table: actor_like
  Non_unique: 0
    Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: actor_id
    Collation: A
  Cardinality: 200
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: actor_like
  Non_unique: 1
    Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name  -- Author: Leshami
    Collation: A          -- Blog  : http://blog.csdn.net/leshami
  Cardinality: 200
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)
--上面的查詢中新表的索引統計資訊被收集

robin@localhost[sakila]> explain select * from actor where last_name like 'A%';
+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys      | key                | key_len | ref  | rows | Extra                |
+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | actor | range | idx_actor_last_name | idx_actor_last_name | 137    | NULL |    7 | Using index condition |
+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

robin@localhost[sakila]> explain select * from actor_like where last_name like 'A%';
+----+-------------+------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
| id | select_type | table      | type  | possible_keys      | key                | key_len | ref  | rows | Extra                |
+----+-------------+------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | actor_like | range | idx_actor_last_name | idx_actor_last_name | 137    | NULL |    7 | Using index condition |
+----+-------------+------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
--從上面的執行計畫可以看出，like方式建表與原表使用了相同的執行計畫

4、基於myisam引擎進行create table like方式複製

robin@localhost[sakila]> alter table actor_like engine=myisam;
Query OK, 200 rows affected (0.03 sec)
Records: 200  Duplicates: 0  Warnings: 0

robin@localhost[sakila]> show table status like 'actor_like'\G
*************************** 1. row ***************************
          Name: actor_like
        Engine: MyISAM
        Version: 10
    Row_format: Dynamic
          Rows: 200
 Avg_row_length: 25
    Data_length: 5016
Max_data_length: 281474976710655
  Index_length: 7168
      Data_free: 0
 Auto_increment: 201
    Create_time: 2015-01-19 11:19:55
    Update_time: 2015-01-19 11:19:55
    Check_time: 2015-01-19 11:19:55
      Collation: utf8_general_ci
      Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

robin@localhost[sakila]> create table actor_like_isam like actor_like;
Query OK, 0 rows affected (0.01 sec)

robin@localhost[sakila]> insert into actor_like_isam select * from actor_like;
Query OK, 200 rows affected (0.00 sec)
Records: 200  Duplicates: 0  Warnings: 0

robin@localhost[sakila]> insert into actor_like_isam select * from actor_like;
Query OK, 200 rows affected (0.00 sec)
Records: 200  Duplicates: 0  Warnings: 0

robin@localhost[sakila]> show index from actor_like_isam\G
*************************** 1. row ***************************
        Table: actor_like_isam
  Non_unique: 0
    Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: actor_id
    Collation: A
  Cardinality: 200
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: actor_like_isam
  Non_unique: 1
    Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 100
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)

robin@localhost[sakila]> explain select * from actor_like_isam where last_name like 'A%';
+----+-------------+-----------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
| id | select_type | table          | type  | possible_keys      | key                | key_len | ref  | rows | Extra                |
+----+-------------+-----------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | actor_like_isam | range | idx_actor_last_name | idx_actor_last_name | 137    | NULL |    6 | Using index condition |
+----+-------------+-----------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

--從上面的測試可以看出基於myisam引擎方式對原表結構也是使用完成複製方式

5、小結
a、create table like方式會完整地複製表結構，但不會插入資料，需要單獨使用insert into或load data方式載入資料
b、create table as  方式會部分複製表結構，完整保留資料
c、create table as select .. where 1=0 會複製部分表結構，但不複製資料。
d、如果啟用了gtid，create table as方式不被支援。收到ERROR 1786 (HY000): CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.

本文永久更新連結地址：