前景介绍
有个long long ago 的项目, 由于购买的阿里云数据库过期了没人管,最后悲催的事情发生了,数据库被释放了,纳尼,我也是有点懵逼。经理问能不能恢复,唉,不想吐槽,还是保持一颗戒骄戒躁的❤想办法去解决问题吧。
真的是庆幸,还好那个项目没有几个用户量,不然真的是要造雷劈的~还好之前参与过那个项目,本地有一份DB。
由于之前运维不是本人,也不太清楚具体的数据库版本,所以我装了一个比较老的版本:5.7.26
select version() from dual;
=== > 5.7.26
问题
有一段分组sql ,记得项目完工后,运行的一点毛病都没有,咋我这次恢复数据库,发现 竟然 跑不通。emm~~
SELECT
t1.match_id AS matchId,
t2.event_id AS eventId,
t2.logo AS eventLogo,
t1.match_stauts AS matchStatus,
t5.status_name AS statusName,
t1.start_game_time AS startGameTime,
t1.start_ball_time AS startBallTime,
t1.flash_flg AS flashFlg,
t3.name_zh AS homeTeamName,
t3.logo AS homeTeamLogo,
t1.home_team_score AS homeTeamScore,
t4.name_zh AS visitTeamName,
t4.logo AS visitTeamLogo,
t1.visit_team_score AS visitTeamScore,
t2.name_zh AS eventName,
t2.short_name_zh AS eventShortName
FROM
app_leisu_matchs t1
LEFT JOIN app_leisu_events t2 ON t1.event_id = t2.event_id
LEFT JOIN app_leisu_teams t3 ON t1.home_team_id = t3.team_id
LEFT JOIN app_leisu_teams t4 ON t1.visit_team_id = t4.team_id
LEFT JOIN app_leisu_match_status t5 ON t1.match_stauts = t5.match_status
LEFT JOIN app_leisu_odds t6 ON t1.match_id = t6.match_id
WHERE
t1.match_stauts IN (1, 2, 3, 4, 5, 6, 7)
AND FROM_UNIXTIME(
t1.start_game_time,
'%Y-%m-%d'
) = DATE_FORMAT(NOW(), '%Y-%m-%d')
GROUP BY
t1.match_id
ORDER BY
FIELD(
t1.match_stauts,
2,
3,
4,
5,
6,
7,
1
),
t1.start_game_time ASC,
t1.start_ball_time ASC
LIMIT 0, 10
报错异常:
[Err] 1055 - Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'qiuyoule.t5.status_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
报错分析:
其实翻译过来说的很明白了,就是说 select 取出来的字段 不属于group by 的条件, 违背了sql_mode =only_full_group_by 的 配置吗 ~ 看上面的sql , 会发现group by只有一个t1.match_id 呀 ,然鹅 , select 里的取了那么多字段,哈哈哈~ 报错的没毛病哈 ~
解决
配置mysql 的环境,印象中 好像是和数据库的版本有关系,百度了一下,的确是
在 版本5.7以及以上 ,默认的都是 only_full_group_by
,也就是 select 的字段只能是 group by 里的字段 。
所以,估摸着 ,默认之前这个项目的环境是 5.6 的么~ 想哭 ~
更改sql_mode
方法一(数据重启后无效):
直接取出掉 only_full_group_by
set @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
若未生效,重启下项目试试~
注意: 这种解决办法,如果数据库服务重启了,还是会恢复成默认的only_full_group_by的!!!亲测~
方法二:
修改my.ini 文件
在[mysqlld]节点下,
追加
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
记得重启数据库服务哦~~
只要在原来的sql_mode变量里去除only_full_group_by 即可!