原来还可以这样创建表

转载

mb5fe32930661bd 2021-06-11 05:15:44

文章标签 java 文章分类 Java 后端开发

张工是一名java程序员。最近张工到一家互联网公司面试java开发岗位。面试官看了张工简历上写着，精通数据库，于是就问他。create table as 和create table like 建表方式有什么区别？张工回答不出来，因为对于建表张工一直是用create table这样的方式，面试官提的这种建表方式，张工平时并没有接触到，更别提使用。自然就说不出个所以然了。面试官：MySql语法都没掌握，你简历怎么写精通数据库呢。被面试官这么一说，张工有点不好意思了。下面我们来看create table 、create table as 和create table like这三种建表方式有什么不同。举个例子，我们要建立一张短信模板表1、create table

CREATE TABLE `sms_template` (
  `sms_template_id` bigint(20) NOT NULL COMMENT '短信模板ID',
  `tenant_id` bigint(20) NOT NULL COMMENT '分销商ID',
  `title` varchar(20) NOT NULL COMMENT '短信模板标题',
  `content` varchar(255) NOT NULL COMMENT '短信模板内容',
  `sms_type_id` bigint(20) NOT NULL COMMENT '触发类型ID',
  `sms_type` tinyint(4) NOT NULL COMMENT '短信模板类型：1-系统短息;2-自建短息',
  `company` varchar(50) NOT NULL COMMENT '公司名称',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否逻辑删除：0-未删除，1-已删除',
  `is_receiver` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否允许添加手机号：0-不允许；1-允许',
  `is_charge` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否收费：1-收费；4-不收费',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`sms_template_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信模板主表';

使用create table我们的就把短信模板表建立好了。工作中，我们有时候需要对一张表进行备份。一般都会使用 create table as select(简称:CTAS)，这种建表方式简单方便，需要注意的是CTAS建表产生的问题，因为CTAS建表并不一定会保存原表样式。2、create table ...as select..（CTAS）懒人模式使用CTAS创建表的好处

CTAS语句中使用查询的结果创建和填充表。由于CTAS创建的表具有原子的，这意味着在填充所有查询结果前，我们不会看到该表。所以，我们看到要么是带有完整查询到该表的数据，要么根本查询不到该表的数据。

在CTAS中有两个部分，select选择部分可以是HiveQL支持的任何选择语句。CTAS的create创建部分从select选择部分获取结果模式，并可以使用SerDe和存储格式等其他表属性创建指定的目标表。比如指定行列切分格式等属性。

尤其要注意的是使用CTAS建表会有表结构的变化，以及使用CTAS建表的缺陷和限制。接下来我们再来看看create table like建表方式。3、create table like.....半自动化模式 create table like本质就是复制现有表定义(不复制其数据)，其创建的表除了表名和源表不一样外，其余所有的细节都是一样的，就是没有源表的数据。譬如create table sms_send_type_test like sms_send_type;这样我们就创建了一张sms_send_type_test，其结构跟sms_send_type是一致的。

原来还可以这样创建表_java

我们来验证下，先查看sms_send_type_test的表结构

#查看目标表结构
show create table sms_send_type_testCREATE TABLE `sms_send_type_test` (
  `sms_type_id` bigint(20) NOT NULL COMMENT '触发类型ID',
  `send_type_name` varchar(50) NOT NULL COMMENT '短信模板触发类型描述',
  `is_receiver` tinyint(4) NOT NULL COMMENT '是否允许添加手机号：1-允许；4-不允许',
  `is_deleted` tinyint(2) NOT NULL COMMENT '是否逻辑删除:0-未删除，1-已删除',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`sms_type_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信模板触发类型表'

再查看源表sms_send_type的结构

#查看源表结构
show create table sms_send_typeCREATE TABLE `sms_send_type` (
  `sms_type_id` bigint(20) NOT NULL COMMENT '触发类型ID',
  `send_type_name` varchar(50) NOT NULL COMMENT '短信模板触发类型描述',
  `is_receiver` tinyint(4) NOT NULL COMMENT '是否允许添加手机号：1-允许；4-不允许',
  `is_deleted` tinyint(2) NOT NULL COMMENT '是否逻辑删除:0-未删除，1-已删除',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`sms_type_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信模板触发类型表'

可以发现两张表的结构是一致的。总结 create table as 与create table like 的区别相同点：都是创建一个新表不同点：create table as 只是复制原数据，其实就是把查询的结果建一个表create table like 产生与源表相同的表结构，包括索引和主键，数据需要用insert into 语句复制进去。由于笔者水平有限，文中纰漏之处在所难免，权当抛砖引玉，不妥之处，请大家批评指正。https://mp.weixin.qq.com/s/SDGcJKSe07dRk9YGj7MgNg