create table test_create_table_CreateAs as select * from test_create_table; 表结构的破坏 复制字段结构 复制表结构 LIKE

 小结：

1）

CREATE TABLE t_copy LIKE t;

INSERT INTO t_copy SELECT * FROM t;

DROP TABLE IF EXISTS t;

CREATE TABLE t LIKE t_copy;

INSERT INTO t SELECT * FROM t_copy;

 

 

 

案例中：

索引丢失、分区丢失

实际测试

 

 

Target Server Type : MYSQL
Target Server Version : 50616
File Encoding : 65001

Date: 2019-03-20 15:08:45
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for test_create_table
-- ----------------------------
DROP TABLE IF EXISTS `test_create_table`;
CREATE TABLE `test_create_table` (
`uid` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
`pv` smallint(6) NOT NULL DEFAULT '0' COMMENT '浏览总量',
`ip` bigint(20) NOT NULL DEFAULT '0' COMMENT 'ip',
`date` date NOT NULL DEFAULT '0000-00-00' COMMENT '日期',
KEY `idx_uid` (`uid`),
KEY `date` (`date`),
KEY `uid_date` (`uid`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='访问页面统计总表';

-- ----------------------------
-- Records of test_create_table
-- ----------------------------
INSERT INTO `test_create_table` VALUES ('435345', '0', '0', '2019-02-27');
INSERT INTO `test_create_table` VALUES ('5464', '0', '0', '2019-03-14');

 

 

Target Server Type : MYSQL
Target Server Version : 50616
File Encoding : 65001

Date: 2019-03-20 15:11:24
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for test_create_table_createas
-- ----------------------------
DROP TABLE IF EXISTS `test_create_table_createas`;
CREATE TABLE `test_create_table_createas` (
`uid` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
`pv` smallint(6) NOT NULL DEFAULT '0' COMMENT '浏览总量',
`ip` bigint(20) NOT NULL DEFAULT '0' COMMENT 'ip',
`date` date NOT NULL DEFAULT '0000-00-00' COMMENT '日期'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test_create_table_createas
-- ----------------------------
INSERT INTO `test_create_table_createas` VALUES ('435345', '0', '0', '2019-02-27');
INSERT INTO `test_create_table_createas` VALUES ('5464', '0', '0', '2019-03-14');

 

 

索引丢失

 

create table test_create_table_CreateLike LIKE test_create_table;
insert into test_create_table_CreateLike SELECT * FROM test_create_table;

 

复制结构

Target Server Type : MYSQL
Target Server Version : 50616
File Encoding : 65001

Date: 2019-03-20 15:31:48
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for test_create_table_createlike
-- ----------------------------
DROP TABLE IF EXISTS `test_create_table_createlike`;
CREATE TABLE `test_create_table_createlike` (
`uid` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
`pv` smallint(6) NOT NULL DEFAULT '0' COMMENT '浏览总量',
`ip` bigint(20) NOT NULL DEFAULT '0' COMMENT 'ip',
`date` date NOT NULL DEFAULT '0000-00-00' COMMENT '日期',
KEY `idx_uid` (`uid`),
KEY `date` (`date`),
KEY `uid_date` (`uid`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='访问页面统计总表';

-- ----------------------------
-- Records of test_create_table_createlike
-- ----------------------------
INSERT INTO `test_create_table_createlike` VALUES ('435345', '0', '0', '2019-02-27');
INSERT INTO `test_create_table_createlike` VALUES ('5464', '0', '0', '2019-03-14');

 

插入数据

 

 

drop table if EXISTS test_create_table_CreateLike;