MySQL create table as与create table like对比(2)

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