测试create table a as select * from b 与create table a like b的区别

目的:测试create table a as select * from b 与create table a  like b的区别

mysql 下测试:

源表:ti

表结构如下

root:test> show create table ti\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `id` int(11) DEFAULT NULL,
  `amount` decimal(7,2) DEFAULT NULL,
  `m_photo_big` varchar(64) DEFAULT NULL,
  `tr_date` date DEFAULT NULL,
  `new_msg_flag` tinyint(4) NOT NULL DEFAULT '0',
  `love_listreq` int(3) DEFAULT '1',
  `love_listconfig` int(3) DEFAULT '1',
  KEY `new_msg_flag` (`new_msg_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

a 使用create as select语句创建表

root:test> create table ti2 as select * from ti limit 0;
Query OK, 0 rows affected (0.00 sec)

sroot:test> how create table ti2 ;
----------------------------
CREATE TABLE `ti2` (
  `id` int(11) DEFAULT NULL,
  `amount` decimal(7,2) DEFAULT NULL,
  `m_photo_big` varchar(64) DEFAULT NULL,
  `tr_date` date DEFAULT NULL,
  `new_msg_flag` tinyint(4) NOT NULL DEFAULT '0',
  `love_listreq` int(3) DEFAULT '1',
  `love_listconfig` int(3) DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

对比源表的表结构,发现KEY `new_msg_flag` (`new_msg_flag`)没有被创建


b 使用like子句创建表

root:test> create table ti1 like ti;
Query OK, 0 rows affected (0.06 sec)

root:test> show create table ti1;  
----------------------------------------
CREATE TABLE `ti1` (
  `id` int(11) DEFAULT NULL,
  `amount` decimal(7,2) DEFAULT NULL,
  `m_photo_big` varchar(64) DEFAULT NULL,
  `tr_date` date DEFAULT NULL,
  `new_msg_flag` tinyint(4) NOT NULL DEFAULT '0',
  `love_listreq` int(3) DEFAULT '1',
  `love_listconfig` int(3) DEFAULT '1',
  KEY `new_msg_flag` (`new_msg_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

对比源表的表结构,两者完全一致,完整的包含了表结构和索引

结论:mysql下create table a as select * from b形式创建的表不包含索引信息,like子句形式包含完整表结构和索引信息

所以 as select 子句一般适用于建表并复制源表数据的情况,like子句适用于只复制表结构的情况

误用的风险: 索引的缺失对于业务的性能是致命的,不必多说.

Oracle 下:

a create as select同样不会创建索引

b oracle不支持like子句

 

权限不会随like一起复制

原表 test.test

原表table级别权限：

root:mysql> select * from tables_priv where Db='test' and Table_name='test';
+-------------+------+-------+------------+----------------+---------------------+-----------------------------+-------------+
| Host        | Db   | User  | Table_name | Grantor        | Timestamp           | Table_priv                  | Column_priv |
+-------------+------+-------+------------+----------------+---------------------+-----------------------------+-------------+
| 192.168.%.% | test | root  | test       | root@localhost | 2009-09-17 11:22:25 | Select,Insert,Update,Delete |             | 
| 192.168.%.% | test | lidan | test       | root@localhost | 2009-09-17 11:23:58 | Select,Insert,Update,Delete |             | 
+-------------+------+-------+------------+----------------+---------------------+-----------------------------+-------------+
2 rows in set (0.00 sec)

复制表：

root:test> create table pri_test like test;
Query OK, 0 rows affected (0.00 sec)

pri_test的权限
root:mysql> select * from tables_priv where Db='test' and Table_name='pri_test';
Empty set (0.00 sec)

可见使用like不可能保持原表和复制表的权限一致

至于如何实现完全创建表结构和索引的方法有待继续探讨!