Comparison between MySQL create table as and create table like

Comparison between MySQL create table as and create table like

In MySQL databases, there are multiple ways to clone tables. For example, you can use create table... or create table. However, there are still some differences between the two methods. Where is the difference? This article describes it through demonstration.

1. Structure of mysql sakila table

-- Actor Table Status
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: 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 table Index
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:
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:
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

-- Actor table structure
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. Use create table as to clone a table

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 |
+ ------------- + ---------------------- + ------ + ----- + ------------------- + ----------------------------- +
-- From the above results, we can see that the new table lacks key information and the auto-incrementing column property 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)

-- From the table structure above, we can see that the table state is the same as that of the original table, only because of the difference in creation time,
Robin @ localhost [sakila]> show index from actor_as \ G
Empty set (0.00 sec)

-- From the preceding query, we can see that the new table does not have any indexes.

3. Use create table like to clone a table

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)
-- From the preceding query, we can see that no data is cloned to the new table using the like method.

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:
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:
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)

-- From the table structure and index information above, the table is fully cloned except for no data.
-- Insert data to a like table
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:
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)
-- The index statistics of the new table in the preceding query are collected.

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 | 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 | 137 | NULL | 7 | Using index condition |
+ ---- + ------------- + ------------ + ------- + ----------------------- + ------------------- + --------- + ------ + ----------------------- +
1 row in set (0.00 sec)
-- From the preceding execution plan, we can see that the like method uses the same execution plan for table creation and original table creation.

4. create table like CLONE based on myisam Engine

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:
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:
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 | 137 | NULL | 6 | Using index condition |
+ ---- + ------------- + ----------------- + ------- + --------------------- + ------------------- + --------- + ------ + ------------------------- +
1 row in set (0.00 sec)

-- From the test above, we can see that the myisam engine method is also used to clone the original table structure.

5. Summary
A. the create table like method completely clones the table structure but does not insert data. You must use the insert into or load data method to load data separately.
B. the create table as method partially clones the table structure and completely retains the data.
C. create table as select .. where 1 = 0 will clone some table structures, but not data.
D. If gtid is enabled, the create table as method is not supported. Receive ERROR 1786 (HY000): create table... SELECT is forbidden when @ GLOBAL. ENFORCE_GTID_CONSISTENCY = 1.

This article permanently updates the link address: