[草稿]db小课堂
易混知识
问题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 对多个结果合并后直接返回。
使用小技巧
- 待补充...