? 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? | row