MySQL 中 ONLY_FULL_GROUP_BY 模式

ONLY_FULL_GROUP_BY 对 GROUP BY 查询的限制

在 SQL 中 利用 GROUP BY 聚合 可以起到将字段相同的记录合并的目的。但也常常被滥用,以及使用不当出现结果与预期不一致的情况。

在 sql_mode 中提供了 ONLY_FULL_GROUP_BY 用以限制在 select 字段中 使用 group by 之外的字段 但没有使用聚合函数的情况。

例如:

select type, price from products group by type 

结果根据 type 聚合,但 price 未使用聚合函数,在未开启 ONLY_FULL_GROUP_BY 的情况下 mysql 会依照聚合顺序 返回默认的结果,而开启之后,则会返回错误:

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘db.tbs.price’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

当前所使用的 sql_mode 可以使用 show variables 查看

show variables like 'sql_mode';

MySQL 5.7 新增 ANY_VALUE()

从 MySQL 5.7 版本开始 默认将 ONLY_FULL_GROUP_BY 设置为启用状态。因此 如果依旧按照原先的方式进行查询 将会出现错误,在开启 ONLY_FULL_GROUP_BY 时 如果对非 GROUP BY 字段需要获取同原先一样的结果,5.7 版本开始则提供了一个 ANY_VALUE() 函数:

select type, ANY_VALUE(price) from products group by type 

Laravel 的 strictMode

对于 Laravel 框架 在 config/database.php 文件启用严格模式(strict 为 true )时 ONLY_FULL_GROUP_BY 设为启用状态

strict 为 true
strictMode 下启用 ONLY_FULL_GROUP_BY

如果使用的 MySQL 版本高于 5.7 则 优先考虑使用 ANY_VALUE() 函数
如果还在使用低版本 MySQL 又不方便升级,可以考虑将 strict 设为 false 关闭 strictMode

MSSQL 删除特定标识开头的表

在使用数据库中,我们经常通过不同的开头,来区分不用应用说使用的表。
比如BBSMax会使用bx_,Discuz默认使用cdb_,这样做对于维护数据库是相当方便的(当然,不同应用如果能分库就更好了 XD)
如果某个应用不用了,只想删除这个应用的表怎么办呢?
在MSSQL,可以用执行下面的SQL语句来实现,只要把红色部分修改成需要的表开头名既可。
这个方法在MSSQL2005下测试成功(需要注意的事,如果表间存在依赖关系,那么不可能一次性删除所有的表,有的表会因为依赖关系无法删除,不过当他的依赖关系已经被删除了以后就可以删除了(比如B表依赖A表,那么第一次执行,A表无法删除,因为被B依赖,但是B没有被依赖,所以被删除了,于是再次执行,A没了依赖,就可以被删除了)

DECLARE cursorname cursor for select ‘drop table ‘+name from sysobjects where name like ‘bx%’ and xtype = ‘u’
open cursorname
declare @curname sysname
fetch next from cursorname into @curname
while(@@fetch_status=0)
begin
exec(@curname)
fetch next from cursorname into @curname
end
close cursorname
deallocate cursorname

以上 ><