有时我们需要建立一张和已存在的表结构相同的表,但又不想重新输入建表命令,这个时候就可以用到表结构克隆命令,本文介绍两种MySQL中最常用的表结构克隆方法;其次,本文还补充了如何重命名表以及如何在指定位置插入新列。
● 表结构克隆有create table as和create table like两种方法;
● 两者区别:
create table like 将表结构与索引完全克隆,但不克隆表数据;
create table as 仅复制表结构但不复制表索引,但能克隆表数据(比如主键索引);
● 注意事项:create table like是MySQL独有,oracle仅支持create table as,不支持create table like;
● 表结构克隆实例:
//这是上一篇文章实例中创建的t18 mysql> select * from t18; +----+---------------+ | id | uname | +----+---------------+ | 1 | qingsword.com | | 2 | qingsword.com | | 3 | qingsword.com | | 4 | qingsword.com | | 6 | qingsword.com | | 7 | qingsword.com | +----+---------------+ //t18表结构如下,id是主键索引,并有自增长属性 mysql> desc t18; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | uname | char(20) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+ //使用as命令,将t18表结构与数据克隆到t18as表,t18as不能是已存在的表,否则会报错 mysql> create table t18as as select * from t18; //从select可以得知,t18表数据被克隆到了t18as表中 mysql> select * from t18as; +----+---------------+ | id | uname | +----+---------------+ | 1 | qingsword.com | | 2 | qingsword.com | | 3 | qingsword.com | | 4 | qingsword.com | | 6 | qingsword.com | | 7 | qingsword.com | +----+---------------+ //desc显示,t18的主键索引以及自增长属性并没有被克隆过来 mysql> desc t18as; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | uname | char(20) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ //如果我们仅想通过as命令克隆表结构,不克隆表数据,可以使用limit筛选(limit在后面的文章中会详细介绍,这里只要知道,这个筛选命令将表数据全部排除在要被克隆的数据之外就可以了,也就是仅克隆表结构,不克隆表数据) mysql> create table t18ast as select * from t18 limit 0; //like命令克隆表结构 mysql> create table t18like like t18; //like命令不会克隆表数据 mysql> select * from t18like; Empty set (0.00 sec) //表结构与原表t18完全一致,包括索引值和自增长 mysql> desc t18like; +-------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | uname | char(20) | YES | | NULL | | +-------+----------+------+-----+---------+----------------+
[**] 注:如文中未特别声明转载请注明出自:QingSword.COM