sql_mode查询与修改

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/FG24151110876/article/details/95514979
/*
修改my.ini:
sql_mode=NO_ENGINE_SUBSTITUTION
*/
SELECT @@GLOBAL.SQL_mode;
SET @@GLOBAL.sql_mode=
'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';	
SET @@GLOBAL.sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";

 

展开阅读全文

mysql 5.7 sql_mode修改后无效

10-14

mysql 5.7 默认开启了sql_mode的条件限制, 但是我修改该后,仍然无效, 不管是通过控制台里面输入 set sql_mode='', set gloale sql_mode = '', set session sql_mode = '', 还是在my.cfn文件里面的 [mysqld] 选项下加入 sql_mode='', 都无法生效,rn直接贴图吧rn[img=https://img-bbs.csdn.net/upload/201610/14/1476440115_580176.png][/img]rnrn存储过程如下, 存储过程中有对select * 出来的东西进行group by操作, mysql 5.7默认不支持了, 需要group by的字段,必须要select该字段才可以, 查看过mysql的官方手册,他们就是说通上面的几个set命令就可以取消这个限制, 真是无解了,折腾两天了,求大神指教rn[code=sql]rnDELIMITER $$rnUSE `qp_game_loggers`$$rnDROP PROCEDURE IF EXISTS `MB_GR_GetBoxResult`$$rnCREATE PROCEDURE `MB_GR_GetBoxResult`(IN iKindId INT(11), IN iUserId INT(11))rnLABEL_PROC:rnBEGINrnIF iKindId = 0 THENrn SELECT *, SUM(Score) AS TotalScore,MAX(curgame) AS RoundCount FROM gamebox_game_result WHERE boxid INrn (SELECT boxid FROM gamebox_game_result WHERE userid =iUserId rn AND CollectTime >= NOW() - INTERVAL 3600*24*5 SECOND rn ORDER BY CollectTime DESC) GROUP BY userid, boxid ORDER BY CollectTime DESC;rnELSErn SELECT *,SUM(Score) AS TotalScore, MAX(curgame) AS RoundCount FROM gamebox_game_result WHERE boxid INrn (SELECT boxid FROM gamebox_game_result WHERE userid =iUserId rn AND kindid=iKindId AND CollectTime >= NOW() - INTERVAL 3600*24*5 SECOND rn ORDER BY CollectTime DESC) GROUP BY userid, boxid ORDER BY CollectTime DESC;rnEND IF;rnEND LABEL_PROC$$rnDELIMITER ;rn[/code]rn 论坛

mysql 老是报this is incompatible with sql_mode=only_full_group_by

11-05

大大们,帮看下是怎么回事,本地MYSQL上运行没有问题,可是程序运行却报下面的错误。rnrn错误信息如下:rnExpression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'his.a.AttachPathId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_byrn----------------------------------------------------------------------------------------------------------------rnSQL如下:rnSELECT mpd.MaterialId VE_MaterialShopId,mpd.MaterialName,mpd.TypeId,mpd.TypeName,mpd.Unit,rnmpd.Standard,mpd.OutPrice,mpd.ProductName,rncase when aa.Path is null then 'http://localhost:4321/UploadFolder/Msg/Error.jpg' else aa.Path end as Path rnfrom (SELECT m.MaterialId,m.Name MaterialName,m.TypeId,d.`Value` TypeName,rnm.Unit,m.Standard,m.OutPrice,p.Name ProductName From material m rnLEFT JOIN product p ON m.ProductId=p.ProductId rnLEFT JOIN dict d ON m.TypeId=d.DictId) mpd rnLEFT JOIN (SELECT a.AttachPathId,a.EntName,a.EntId,CONCAT('http://localhost:4321',a.Path) Path rnFROM attachpath a WHERE EntName='Material' GROUP BY EntId) aa rnON mpd.MaterialId=aa.EntId rn-------------------------------------------------------------------------------------------------------------------rn本地my.ini配置了跟服务器一样的 sql_mode ,本地运行没有问题,服务器用Navicat运行也没有问题,可是程序执行却报上面的错误。rnrnsql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'rn 论坛

没有更多推荐了,返回首页