mysql 5.7 默认开启了sql_mode的条件限制, 但是我修改该后,仍然无效, 不管是通过控制台里面输入 set sql_mode='', set gloale sql_mode = '', set session sql_mode = '', 还是在my.cfn文件里面的 [mysqld] 选项下加入 sql_mode='', 都无法生效,
直接贴图吧
存储过程如下, 存储过程中有对select * 出来的东西进行group by操作, mysql 5.7默认不支持了, 需要group by的字段,必须要select该字段才可以, 查看过mysql的官方手册,他们就是说通上面的几个set命令就可以取消这个限制, 真是无解了,折腾两天了,求大神指教DELIMITER $$
USE `qp_game_loggers`$$
DROP PROCEDURE IF EXISTS `MB_GR_GetBoxResult`$$
CREATE PROCEDURE `MB_GR_GetBoxResult`(IN iKindId INT(11), IN iUserId INT(11))
LABEL_PROC:
BEGIN
IF iKindId = 0 THEN
SELECT *, SUM(Score) AS TotalScore,MAX(curgame) AS RoundCount FROM gamebox_game_result WHERE boxid IN
(SELECT boxid FROM gamebox_game_result WHERE userid =iUserId
AND CollectTime >= NOW() - INTERVAL 3600*24*5 SECOND
ORDER BY CollectTime DESC) GROUP BY userid, boxid ORDER BY CollectTime DESC;
ELSE
SELECT *,SUM(Score) AS TotalScore, MAX(curgame) AS RoundCount FROM gamebox_game_result WHERE boxid IN
(SELECT boxid FROM gamebox_game_result WHERE userid =iUserId
AND kindid=iKindId AND CollectTime >= NOW() - INTERVAL 3600*24*5 SECOND
ORDER BY CollectTime DESC) GROUP BY userid, boxid ORDER BY CollectTime DESC;
END IF;
END LABEL_PROC$$
DELIMITER ;
直接贴图吧
存储过程如下, 存储过程中有对select * 出来的东西进行group by操作, mysql 5.7默认不支持了, 需要group by的字段,必须要select该字段才可以, 查看过mysql的官方手册,他们就是说通上面的几个set命令就可以取消这个限制, 真是无解了,折腾两天了,求大神指教DELIMITER $$
USE `qp_game_loggers`$$
DROP PROCEDURE IF EXISTS `MB_GR_GetBoxResult`$$
CREATE PROCEDURE `MB_GR_GetBoxResult`(IN iKindId INT(11), IN iUserId INT(11))
LABEL_PROC:
BEGIN
IF iKindId = 0 THEN
SELECT *, SUM(Score) AS TotalScore,MAX(curgame) AS RoundCount FROM gamebox_game_result WHERE boxid IN
(SELECT boxid FROM gamebox_game_result WHERE userid =iUserId
AND CollectTime >= NOW() - INTERVAL 3600*24*5 SECOND
ORDER BY CollectTime DESC) GROUP BY userid, boxid ORDER BY CollectTime DESC;
ELSE
SELECT *,SUM(Score) AS TotalScore, MAX(curgame) AS RoundCount FROM gamebox_game_result WHERE boxid IN
(SELECT boxid FROM gamebox_game_result WHERE userid =iUserId
AND kindid=iKindId AND CollectTime >= NOW() - INTERVAL 3600*24*5 SECOND
ORDER BY CollectTime DESC) GROUP BY userid, boxid ORDER BY CollectTime DESC;
END IF;
END LABEL_PROC$$
DELIMITER ;
解决方案 »
- 有关BitKeeper和存储引擎的问题
- mysql下 group by、sum 语句占CPU高问题!@mysql版主啊!!~
- 如何处理mysql中表损坏问题
- 关于MYSQL多条件查询orderby优化的问题
- mysql 最大的connect_timeout时间是多少
- 在Delphi中连接到Mysql后,如何作Dbgrid将表中数据显示出来
- 什么命令可以看有多少数据库??
- mysql中的一个select语句求教(详细版)
- MySql中建表语句
- Mysql Connector/C++,得到了ResultSet,如何取date类型的数据
- InnoDB行锁实现方式问题
- mysql workbench 6.3ce:语法没错可运行但是一直显示红叉,重启后消失,怎么回事啊,强迫症忍不了啊T_T
貌似不对吧, http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
mysql官网解释说"To clear the SQL mode explicitly, set it to an empty string using --sql-mode="" on the command line, or sql-mode="" in an option file."