在mysql数据库中,关于表的克隆有多种方式,比如我们可以使用create table ..as .. ,也可以使用create table .. like ..方式。然
在mysql数据库中,关于表的克隆有多种方式,比如我们可以使用create table ..as .. ,也可以使用create table .. like ..方式。然而这2种不同的方式还是有些差异的,他的差异到底在哪里呢,本文通过演示对此展开描述。
1、mysql sakila表上的结构
--actor表状态
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: 2014-12-25 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表索引
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表结构
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、使用create table as方式克隆表
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 |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
--从上面的结果可以看出新表缺少了key信息,以及自增列属性 auto_increment