用MySQL碰到的一些“坑”
本篇文章持续更新。
这里说坑,也不算坑,只是对我一个经常用SQL Server的来说有点不习惯而已。
一、GroupBy 的不同
create table Customer ( CustomerNumber varchar(50), PayMentDate datetime, Amount int ) insert into Customer( CustomerNumber, PayMentDate, Amount ) select '103','2018-01-02','123.01' union all select '103','2018-01-08','123.01' union all select '115','2018-01-04','123.01' union all select '112','2018-01-05','123.01' union all select '115','2018-01-08','123.01' union all select '112','2018-01-10','123.01' union all select '114','2018-01-06','123.01' ;
根据上面的数据,我们执行如下SQL语句:
select CustomerNumber,PayMentDate from Customer group by CustomerNumber
第一反应,看到这条SQL,我说这不是会报错吗(按照以前在SQLServer的逻辑)。
实际上是不会报错的。
虽然大家不推荐这么干,但是上面的写法确实是不会报错的,因为MySQL会给我们(非分组字段)加默认的聚合函数。
真正的原因:MySQL里面是通过sql_Model来提供SQL语句的合法检查,在mysql的sql_model=default的情况下
是允许刚刚上面这种做法的,也就是说一条select语句,除了聚合函数和group by column以外的表达式(这个表达式的值无法确定)
实际上是MySQL的分组内第一行对应列的值)
而很多对语义限制比较严的多家数据库,如SQLServer,Oracle,PostgreSql都不支持select trage list 中出现语义不明确的列。
所以MySQL在后续的版本中出现了一个修正的语义,也就是我们说的ONLY_FULL_GROUP_BY的语义。
set sql_mode=ONLY_FULL_GROUP_BY; select CustomerNumber,PayMentDate from Customer group by CustomerNumber;
上面这样写就会报错:说PayMentDate不在Group By 的列中。
不管我们是否设置:
set sql_mode=ONLY_FULL_GROUP_BY
我们都应该按照严格的语义来写,不然给后面维护带来不方便。
二、删除数据
一开始的删除数据,直接写了下面个子查询,然后就交给DBA,去正式的库执行,之后反馈说,报错了,不能执行。
delete from Customer where CustomerNumber in ( select CustomerNumber from payments );
上面这个做法为什么错呢,因为MySQL不允许在安全模式下,删除数据不通过主键来删除。
正确的做法:
把安全更新设置为零,用完再设置回1。
SET SQL_SAFE_UPDATES = 0; delete from Customer where CustomerNumber in ( select CustomerNumber from payments ); SET SQL_SAFE_UPDATES=1;
从这里也可以看出,我们进行删除数据这些操作,还是尽量的用主键好。主键能够保证不会多删,并且效率也高。