mysql中create as_MySQL create table as与create table like对比

最新推荐文章于 2024-04-10 07:47:25 发布
最新推荐文章于 2024-04-10 07:47:25 发布
阅读量1.5k 收藏 1
点赞数
文章标签： mysql中create as
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_30706851/article/details/113600971
版权

在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.

0b1331709591d260c1c78e86d0c51c18.png

小葙
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
数据库 MYSQL
xiangbaoo的博客
09-02 2652
创建一张表,表的字段和数据来自于一条查询语句 语法: create table 表名 as select *from 另一张表 where 1=1; ----创建一张表会把另一张表中的字段和对应的数据全部复制过去 如果我们只想要字段不要数据 create table 表名 as select *from 另一张表 where 1=2; 如果我们只想要个别字段 create table 表名 as ...
MySQL 的create table as 与like 的使用
Voryla的博客
01-15 2万+
1.MySQL复制相同表结构的方法： -- 1.使用AS复制相同的表结构 CREATE TABLE table_name AS SELECT * FROM other_table WHERE 1=2;(或者LIMIT 0) -- 2.使用LIKE复制相同的表结构 CREATE TABLE table_name LIKE other_table; 2.复制表的数据 复制全部数据 注意：复制全部数据 列的类型要相同 INSERT INTO table_name SELECT * FROM
CREATE TABLE ... AS SELECT 与 CREATE TABLE ... LIKE 的相同点和不同点
最新发布
小湘西的博客
04-10 602
目录相同点不同点 数据复制：索引和表选项：默认值和约束：自定义能力：使用场景：性能： 综上所述，和虽然都用于创建新表，但根据是否需要复制数据、保留索引和表选项以及自定义表结构的需求，会选择不同的语句来满足特定的场景。
MySQL复制表结构create table as与like的区别
我的博客
07-31 966
LIKE：用来创建完整表结构和全部索引(可以使用INSERT INTO和SELECT语句复制源表的所有数据)AS：用来创建相同表结构并复制源表数据(可根据后面的条件来控制要不要复制源表数据)完全的复制了源表的结构包括索引信息。AS复制了表的源数据和结构，但。使用AS复制book表。
MySQL创建表
weixin_46020795的博客
09-04 352
创建表的三种方式 直接创建 create table if not exists 表名 将table1的部分拿来创建table2 create table if not exists table2 ( ...... ) select 列名，列名 from table1 -- CREATE TABLE 新表SELECT * FROM 旧表 -- 完整拷贝过来， 区别： 　　通过 as 创建出来的新表缺少源表的索引信息，只有表结构相同，没有索引。 　　通过 like 创建出来的新表包含源
mysql/oracle中create table as的用法
苦笔的博客
12-08 2万+
1 mysqlcreate table t2 as select c1 from t1; --正确，一般用法--新表中对列重命名 create table t2 as select c1 c2 from t1; --正确create table t2(c2 varchar(50)) as select c1 from t1; --语法正确，但不是预期结果 desc t2; --c
MySQL中create table as 与like的区别分析
01-19
本文分析了MySQL中create table as 与like的区别。分享给大家供大家参考，具体如下： 对于mysql的复制相同表结构方法，有create table as 和create table like 两种，区别是什么呢？ 代码如下:create table t2 as ...
MySQL中表复制:create table like 与 create table as select
12-15
CREATE TABLE A LIKE B 此种方式在将表B复制到A时候会将表B完整的字段结构和索引复制到表A中来。 代码如下: CREATE TABLE A AS SELECT x,x,x,xx FROM B LIMIT 0 此种方式只会将表B的字段结构复制到表A中来，但...
mysql中GROUP_CONCAT的使用方法实例分析
12-14
本文实例讲述了mysql中GROUP_CONCAT的使用方法。分享给大家供大家参考，具体如下： 现在有三个表，结构如下： cate表： CREATE TABLE `cate` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id', `...
mysql直接create as select
qq_41081716的博客
04-16 681
表结构比较：auto_increment变成了default '0'，所以用create as select 这种简单粗暴方式建表的时候。一定要比较前后表结构。
mysql table as_Mysql中create table as与like命令的区别
weixin_35835030的博客
02-17 1247
MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。MySQL 所使用的 SQL 语言是用于访问数据库的最常用标准化语言。在做数据迁移时，对于mysql中复制相同表结构方式有create table as select与create table like 两种方式，具体分析如下。慎用create table as...
MySQL Create table as select无法执行
程序员青菜学厨记
11-05 1331
MySQL5.6及更低版本系统参数enforce_gtid_consistency 默认值为OFF，MySQL5.7及更高版本默认值为ON，当设置为ON时，MySQL只允许能够保障事务安全并且能够被日志记录的语句才会被执行，而像create table as select 或create temporarytable 这种一条语句中包括事务和非事务是不被允许执行的。根据GTID可以知道事务最初是在哪个实例上提交的，可以更简单实现主从复制且更安全。注：下次写一篇传统的主从复制和GTID主从复制的文章。
MySQL-数据库表的创建/管理（CREATE/ALTER/DROP/AS/CHANGE/ADD/MODIFY/TRUNCATE/DELETE）
萌萌虎的博客
07-01 1392
数据库名、表名不得超过30个字符，变量名限制为29个 必须只能包含 A–Z, a–z, 0–9, _共63个字符 数据库名、表名、字段名等对象名中间不要包含空格 同一个MySQL软件中，数据库不能同名； 同一个库中，表不能重名； 同一个表中，字段不能重名 必须保证你的字段没有和保留字、数据库系统或常用方法冲突。 ............
MySQL create table as与create table like对比
欧辰的专栏
06-03 444
对于mysql的复制相同表结构方法，有create table as 和create table like 两种，区别是什么呢？ create table t2 as select * from t1 where 1=2 ； 或者 limit 0； as创建出来的t2表（新表）缺少t1表（源表）的索引信息，只有表结构相同，没有索引。 create table t2 like t1 ； like 创建出来的新表包含源表的完整表结构和索引信息 二者的用途： as用来创建相同表...
【Hive】Hive中表创建方式及其对比
Together
08-15 1528
HIve表的创建方式 【1】craete table 首先就是最常见的方式，通 create table 方式进行建立hive表 例如???? create [external] table [IF NOT EXISTS] tableName ( column columnType comment 'annotation', column columnType comment 'annotation', column columnType comment 'annotation', ... ) partit
mysql备份create as_Mysql中create as 和create like有什么区别？（对比介绍）
weixin_42515181的博客
02-08 213
本篇文章给大家带来的内容是关于Mysql中create as 和create like有什么区别？(对比介绍)，有一定的参考价值，有需要的朋友可以参考一下，希望对你有所帮助。大家可能使用Navicat Premium时发现很方便，比如复制表或数据结构等，其实这种复制表数据或结构方法就是create table as 和create table like 这种方式实现细心的朋友会问，他们有啥区别呢？...
SELECT INTO 与 Create as
Wishing的专栏
03-09 1392
SELECT INTONameSELECT INTO  --  从一个查询的结果中创建一个新表 SynopsisSELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] * | expression [ AS output_name ] [, ...] INTO [ TEMPORARY | TEMP ] [ TABLE ]
create table ,create table as 与create like三种建表方式的使用详解
热门推荐
涤生大数据
01-02 7万+
Hive的hql是基于sql而来，而sql中关于表的创建有几种方式。同样，hive也支持这些表的创建方式。 官网文档关于建表的地址：hive常见建表方式官网文档 1.自主创业方式create table 使用create table从无到有，按照建表的格式和要求实现建表。实际开发中较常用的方式，主要可以定制化建表。 具体参考博客：https://blo...
mysql中 create table as与create table like对比
06-03
在MySQL中，CREATE TABLE AS和CREATE TABLE LIKE都是用于创建新表的命令，但它们之间有一些区别。 CREATE TABLE AS语法如下： ``` CREATE TABLE new_table AS SELECT * FROM old_table; ``` 这将创建一个名为new_table的新表，并从old_table中复制所有列和行数据。新表将具有与原始表相同的列名称、数据类型和约束。 CREATE TABLE LIKE语法如下： ``` CREATE TABLE new_table LIKE old_table; ``` 这将创建一个名为new_table的新表，但不会将任何数据从原始表中复制到新表中。新表将具有与原始表相同的列名称、数据类型和约束，但不会包括任何数据。 因此，CREATE TABLE AS用于创建新表并将数据复制到新表中，而CREATE TABLE LIKE用于创建新表但不复制数据。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值