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

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

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  | rows | Extra                 |
+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | actor | range | idx_actor_last_name | idx_actor_last_name | 137     | NULL |    7 | Using index condition |
+----+-------------+-------+-------+---------------------+---------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

robin@localhost[sakila]> explain select * from actor_like where last_name like 'A%';
+----+-------------+------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
| id | select_type | table      | type  | possible_keys       | key                 | key_len | ref  | rows | Extra                 |
+----+-------------+------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | actor_like | range | idx_actor_last_name | idx_actor_last_name | 137     | NULL |    7 | Using index condition |
+----+-------------+------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
--从上面的执行计划可以看出,like方式建表与原表使用了相同的执行计划

4、基于myisam引擎进行create table like方式克隆

robin@localhost[sakila]> alter table actor_like engine=myisam;
Query OK, 200 rows affected (0.03 sec)
Records: 200  Duplicates: 0  Warnings: 0

robin@localhost[sakila]> show table status like 'actor_like'\G
*************************** 1. row ***************************
           Name: actor_like
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 200
 Avg_row_length: 25
    Data_length: 5016
Max_data_length: 281474976710655
   Index_length: 7168
      Data_free: 0
 Auto_increment: 201
    Create_time: 2015-01-19 11:19:55
    Update_time: 2015-01-19 11:19:55
     Check_time: 2015-01-19 11:19:55
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

robin@localhost[sakila]> create table actor_like_isam like actor_like;
Query OK, 0 rows affected (0.01 sec)

robin@localhost[sakila]> insert into actor_like_isam select * from actor_like;
Query OK, 200 rows affected (0.00 sec)
Records: 200  Duplicates: 0  Warnings: 0

robin@localhost[sakila]> insert into actor_like_isam select * from actor_like;
Query OK, 200 rows affected (0.00 sec)
Records: 200  Duplicates: 0  Warnings: 0

robin@localhost[sakila]> show index from actor_like_isam\G
*************************** 1. row ***************************
        Table: actor_like_isam
   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_isam
   Non_unique: 1
     Key_name: idx_actor_last_name
 Seq_in_index: 1
  Column_name: last_name
    Collation: A
  Cardinality: 100
     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_like_isam where last_name like 'A%';
+----+-------------+-----------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
| id | select_type | table           | type  | possible_keys       | key                 | key_len | ref  | rows | Extra                 |
+----+-------------+-----------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | actor_like_isam | range | idx_actor_last_name | idx_actor_last_name | 137     | NULL |    6 | Using index condition |
+----+-------------+-----------------+-------+---------------------+---------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

--从上面的测试可以看出基于myisam引擎方式对原表结构也是使用完成克隆方式

5、小结 a、create table like方式会完整地克隆表结构,但不会插入数据,需要单独使用insert into或load data方式加载数据 b、create table as  方式会部分克隆表结构,完整保留数据 c、create table as select .. where 1=0 会克隆部分表结构,但不克隆数据。 d、如果启用了gtid,create table as方式不被支持。收到ERROR 1786 (HY000): CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏IT开发技术与工作效率

MySQL 8 列出相关依赖 使用CTE(公共数据表达式)

2235
来自专栏惨绿少年

MySQL 索引管理与执行计划

1.1 索引的介绍   索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她,则与在表中...

2310
来自专栏Ryan Miao

mysql数据库去重复

参考:http://www.cnblogs.com/duanjie/archive/2011/08/13/2136862.html 说到去重复,感觉逻辑很简单。...

38611
来自专栏杨建荣的学习笔记

MySQL和Oracle对比学习之数据字典元数据(r4笔记第33天)

MySQL和Oracle虽然在架构上有很大的不同,但是如果从某些方面比较起来,它们有些方面也是相通的。 毕竟学习的主线是MySQL,所以会从MySQL的角度来对...

2926
来自专栏技术小站

(转)MySQL之char、varchar和text的设计

http://www.cnblogs.com/billyxp/p/3548540.html

952
来自专栏技术碎碎念

mysql使用基础 sql语句与数据完整性(二)

二、DML:Data Manipulation Language 数据操作语言   作用:操作表中的数据的。   关键:INSERT UPDATE DELE...

35110
来自专栏james大数据架构

你真的会玩SQL吗?删除重复数据且只保留一条

在网上看过一些解决方法 我在此给出的方法适用于无唯一ID的情形 表:TB_MACVideoAndPicture 字段只有2个:mac,content mac作为...

1888
来自专栏Java帮帮-微信公众号-技术文章全总结

MySQL全部知识点(1)

? 数据库 1 数据库概念(了解) 1.1 什么是数据库 数据库就是用来存储和管理数据的仓库! 数据库存储数据的优先: 可存储大量数据; 方便检索; 保持数据...

3105
来自专栏二进制文集

MySQL 基础练习

最近看了 Sams Teach Yourself 系列的《SQL必知必会》。里面的内容很基础,使用SQL上手简单。我将其中典型的题目及答案总结如下,包含数据库所...

852
来自专栏面朝大海春暖花开

mysql树形结构递归查询

之前一直用的是Oracle,对于树形查询可以使用start with ... connect by 

4944

扫码关注云+社区

领取腾讯云代金券