create table like 和create table select 比较
来源:互联网 发布:免费的发型设计软件 编辑:程序博客网 时间:2024/05/17 16:44
语法:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(] LIKE old_tbl_name [)];
测试过程:
原数据表:
mysql> show create table test_order \G*************************** 1. row *************************** Table: test_orderCreate Table: CREATE TABLE `test_order` ( `pay_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `origin` int(10) DEFAULT NULL, `team_id` int(11) DEFAULT NULL, `state` int(11) DEFAULT NULL, KEY `team_id` (`team_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
mysql> create table cc select * from test_order;Query OK, 9900 rows affected (0.11 sec)Records: 9900 Duplicates: 0 Warnings: 0
mysql> create table dd like test_order; Query OK, 0 rows affected (0.22 sec)
查看数据:
mysql> select * from cc limit 2;+---------------------+--------+---------+-------+| pay_time | origin | team_id | state |+---------------------+--------+---------+-------+| 2011-06-22 18:04:47 | 10 | 100 | 100 || 2011-06-22 18:04:47 | 10 | 100 | 101 |+---------------------+--------+---------+-------+2 rows in set (0.00 sec)
mysql> select * from dd;Empty set (0.00 sec)
结果:cc表中数据与原表test_order中的一致,dd表中无数据
查看表结构:
mysql> show create table cc \G*************************** 1. row *************************** Table: ccCreate Table: CREATE TABLE `cc` ( `pay_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `origin` int(10) DEFAULT NULL, `team_id` int(11) DEFAULT NULL, `state` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
mysql> show create table dd \G*************************** 1. row *************************** Table: ddCreate Table: CREATE TABLE `dd` ( `pay_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `origin` int(10) DEFAULT NULL, `team_id` int(11) DEFAULT NULL, `state` int(11) DEFAULT NULL, KEY `team_id` (`team_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
结果:cc表中,原表中的索引消失了;dd表与原表一致
结论:
create table select 会将原表中的数据完整复制一份,但表结构中的索引会丢失。
create table like 只会完整复制原表的建表语句,但不会复制数据
0 0
- create table like 和create table select 比较
- create table like 和create table select 比较
- create table like 和create table select 比较
- postgres create table as select & create table like
- create table tb as select 和create table tb like的区别
- Mysql create table tb as select 和create table tb like的区别
- mysql表复制create table like和create table as比较
- Create table as select
- create table as select
- MySQL中表复制:create table like 与 create table as select
- DB2的create table like
- CREATE TABLE A LIKE B
- MySQL create table as与create table like对比
- create table as select 和 create table 再insert into select 的差异
- oracle create table as select
- Oracle Create Table as Select
- 浅谈create table as select
- MySQL create table as 与 like区别
- eclipse的中文乱码问题
- Jquery.Ajax+php 学习笔记
- 二叉查找树(二叉排序树)的详细实现
- SIGPIPE信号
- SNMP——snmputil 工具
- create table like 和create table select 比较
- 前端小字典 (2)– Base64
- css text-shadow
- 八步详解Hibernate的搭建及使用
- Eclipse快捷键
- 关于Split和Block
- C# byte[]与char[]、string与char[]、byte[] 与 string 互转
- 给数据库远程连接的权限
- 第五章树和二叉树