[草稿]db小课堂

[TOC]

易混知识

问题1：create table as和like的区别

CREATE TABLE 新表名 LIKE 源表;
CREATE TABLE 新表名 [AS] SELECT * FROM 源表;

区别

• like 创建完整表结构和全部索引，但新表没有数据。
• as 创建相同表结构并复制源表数据，但新表没有索引。

引申一下

• 在5.6及以上版本，如果启用gtid，as会报语法错误

  ERROR 1786 (HY000): CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.
  

  CREATE TABLE SELECT 实际上是2个独立的事件，一个DDL用于建表，一个DML用于向新表插入源表数据。由于DDL会导致自动提交，但是在GTID模式下，只能给这个sql生成一个GTID，所以会报错。

• 一个事务中混合了事务和非事务语句，它会提交当前和未完成的事务。

  例如下面的例子，若第2条update执行失败，会导致第1个update回滚，但是其中的create table as select语句为DDL操作而无法回滚。

  begin;
  update accounts set amount = amount – 10000 where account_id=123;
  create table as select … join …
  update accounts set amount = amount + 10000 where account_id=321;
  commit;
  

• CREATE TABLE SELECT 在语句完成之前不会释放元数据锁，其他业务无法查询源表

• 针对 CREATE TABLE SELECT 的改进：

  CREATE TABLE 新表名 LIKE 源表;
  INSERT INTO 新表名 [AS] SELECT * FROM 源表;
  

  解读：元数据锁(metadata lock)仍然在 CREATE TALBE 时(非常短)持有，但 INSERT 期间不会持有，保持锁定的总时间要短得多。

我司建议

• **禁止使用 CREATE TABLE SELECT 语句**

问题2：truncate与delete的区别

• truncate 是删表再创建，而 delete 是逐条删除；
• truncate 重置auto_increment的值，而 delete 不会；
• truncate 不知道删除了几条，而 delete 知道；
• 当被用于带分区的表时，truncate 会保留分区；

问题3：delete和truncate对自增属性的影响

• delete 不会破坏自增属性，即便全表delete也不会；
• truncate 会导致自增从1开始；

问题4：timestamp在mssql及mysql中的不同

• mssql数据库：timestamp 给表行加版本戳（注意这里不是日期或时间类型），8个字节储存。对字段的任何更改都将导致timestamp变化，这也是区别自增字段的的地方。
• mysql数据库：timestamp 时间戳，4个字节储存，取值范围 1970-01-01 ~ 2038-01-19，时区转化，精度为秒(timestamp(3)精度为毫秒)，其值作为数字显示。

问题5：union与union all的区别

• 准备测试数据

create database if not exists bak;
create table if not exists bak.t1 (id int, name varchar(20));
create table if not exists bak.t2 (id int, name varchar(20));
insert into bak.t1 values (1,'姚羽'),(2,'边兵兵');
insert into bak.t2 values (1,'姚羽'),(2,'柳春平'),(3,'张永超');

select * from bak.t1 union select * from bak.t2;
select * from bak.t1 union all select * from bak.t2;

• 执行 union 与 union all 所返回的结果如下

#.union结果             #.union all 结果
id  name                id  name
1    姚羽                1    姚羽
2    边兵兵              2    边兵兵
2    柳春平              1    姚羽
3    张永超              2    柳春平
                        3    张永超

• 结论：union在表连接后会对所产生的结果集进行排序计算，删掉重复的记录再返回结果，增加CPU、内存等消耗。基于此：

如无必要，建议 union all 替代 union
实际业务一般不会产生重复的记录，建议使用 union all 对多个结果合并后直接返回。

使用小技巧

• 待补充...