Create table like and create TABLE select Comparison

Grammar:

CREATE [temporary] TABLE [IF not EXISTS] Tbl_name

[(Create_definition,...)]

[Table_options] [Select_statement]

CREATE [temporary] TABLE [IF not EXISTS] Tbl_name

[(] like old_tbl_name [)];

Test process:

Original Data sheet:

Mysql> Show CREATE TABLE Test_order \g*************************** 1.  Row *************************** table:test_ordercreate table:create Table ' test_order ' (' pay_time ' timestamp not  Null default Current_timestamp, ' Origin ' int (ten) default null, ' team_id ' int (one) default null, ' state ' int (one) ' Default ' NULL, KEY ' team_id ' (' team_id ')) Engine=innodb DEFAULT charset=utf81 row in Set (0.00 sec)

mysql> CREATE TABLE CC select * from Test_order; Query OK, 9900 rows affected (0.11 sec) records:9900 duplicates:0 warnings:0

Mysql> CREATE TABLE dd like Test_order; Query OK, 0 rows affected (0.22 sec)

View data:

Mysql> SELECT * from cc limit 2;+---------------------+--------+---------+-------+| Pay_time | Origin | team_id | State |+---------------------+--------+---------+-------+|     2011-06-22 18:04:47 |     10 |   100 | 100 | |     2011-06-22 18:04:47 |     10 |   100 | 101 |+---------------------+--------+---------+-------+2 rows in Set (0.00 sec)

Mysql> select * FROM DD; Empty Set (0.00 sec)

Results: The data in CC table is consistent with the original table Test_order, no data in DD table

To view the table structure:

Mysql> Show CREATE TABLE cc \g*************************** 1. Row *************************** table:cccreate table:create Table ' cc ' (' pay_time ' timestamp not NULL DEFAULT ' 0 000-00-00 00:00:00 ', ' origin ' int (ten) default null, ' team_id ' int (one-by-one) default null, ' state ' int (one-by-one) default null) Engin E=innodb DEFAULT charset=utf81 Row in Set (0.00 sec)

Mysql> Show CREATE TABLE DD \g*************************** 1. Row *************************** table:ddcreate table:create Table ' dd ' (' pay_time ' timestamp not NULL DEFAULT CU Rrent_timestamp, ' origin ' int (ten) default null, ' team_id ' int (one) default null, ' state ' int (one) default null, KEY ' tea m_id ' (' team_id ')) Engine=innodb DEFAULT charset=utf81 row in Set (0.00 sec)

Result: The index in the original table in the CC table disappears; DD table is consistent with the original table

Conclusion:

     The CREATE TABLE select copies the data from the original table in a full copy, but the indexes in the table structure are lost.

     The CREATE table like will only completely replicate the original table statement, but will not replicate the data

Create table like and create TABLE select Comparison