MySQL create table as compared with create table like, mysqlcreate

MySQL create table as compared with create table like, mysqlcreate

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: Rows: 0 Index_length: 16384 Data_free: 0 Auto_increment: 201 Create_time: 13:08:25 Update_time: NULL Check_time: NULL Collation: Limit 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: 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 table structure: robin @ localhost [sakila]> desc actor; + ------------- + hour + ------ + ----- + ----------------- + hour + | Field | Type | Null | Key | Default | Extra | + ------------- + hour + ------ + ----- + ------------------- + hour + | 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 | + ------------- + hour + ------ + ----- + ------------------- + hour + 4 rows in set (0.00 sec)

2. Use create table as to clone a table

Robin @ localhost [sakila]> create table actor_as select * from actor; Query OK, 200 rows affected (0.06 sec) Records: 200 Duplicates: 0 Warnings: 0robin @ localhost [sakila]> desc actor_as; + ------------- + hour + ------ + ----- + ----------------- + hour + | Field | Type | Null | Key | Default | Extra | + ------------- + hour + ------ + ----- + ------------------- + hour + | 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 | + ------------- + hour + ------ + ----- + ----------------- + hour + -- the preceding results show that the key information of the new table is missing, and auto_incrementrobin @ 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: Bytes: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 10:42:53 Update_time: NULL Check_time: NULL Collation: bytes 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 the difference in creation time, robin @ localhost [sakila]> show index from actor_as \ GEmpty set (0.00 sec) -- from the preceding query, we can see that the new table has no index.

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) -- as shown in the preceding query, using like, no data is cloned to the new table robin @ localhost [sakila]> desc actor_like; + ------------- + hour + ------ + ----- + ----------------- + hour + | Field | Type | Null | Key | Default | Extra | + ------------- + hour + ------ + ----- + ------------------- + hour + | 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 | + ------------- + hour + ------ + ----- + ------------------- + hour + 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) -- from the table structure and index information above, we can see that the table does not have any data, the structure is completely cloned-The following table inserts data like @ localhost [sakila]> insert into actor_like select * from actor; Query OK, 200 rows affected (0.03 sec) records: 200 Duplicates: 0 Warnings: 0robin @ 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) -- The index statistics of the new table in the preceding query are collected by robin @ localhost [sakila]> explain select * from actor where last_name like 'a % '; + ---- + ------------- + ------- + hour + --------------------- + --------- + ------ + hour + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------- + hour + --------- + ------ + hour + | 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 % '; + ---- + ------------- + ------------ + ------- + upper + --------------------- + --------- + ------ + upper + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + ------------ + ------- + hour + --------- + ------ + hour + | 1 | SIMPLE | actor_like | range | idx_actor_last_name | 137 | NULL | 7 | Using index condition | + ---- + ------------- + ------------ + ------- + --------------------- + --------- + ------ + Limit + 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: 0robin @ 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: Rows: 281474976710655 Index_length: 7168 Data_free: 0 Auto_increment: 201 Create_time: 11:19:55 Update_time: 11:19:55 Check_time: 11:19:55 Collation: Limit 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: 0robin @ localhost [sakila]> insert into actor_like_isam select * from actor_like; Query OK, 200 rows affected (0.00 sec) Records: 200 Duplicates: 0 Warnings: 0robin @ 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 % '; + ---- + ------------- + ----------------- + ------- + hour + --------- + ------ + hour + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | extra | + ---- + ------------- + --------------- + ------- + hour + --------- + ------ + hour + | 1 | SIMPLE | range | idx_actor_last_name | 137 | NULL | 6 | Using index condition | + ---- + ------------- + ----------------- + ------- + --------------------- + ------------------- + --------- + ------ + Limit + 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.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.