记录一下mysql不能group by的问题
由于在自己电脑上测试的时候不能group by,显示报错,但是又确定自己的sql没有问题,sql语句如下:
select * from information group by sex;
这是我测试的表结构:
执行语句报错如下:
1055 - Error querying database. Cause: java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sex' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
咱英文也不好,反正感觉大致是意思就是sex这个字段和mysql的sql模式不兼容,于是寻求百度爸爸,试了好多都不对,最后这种方法正确了:
SHOW SESSION VARIABLES;
SHOW GLOBAL VARIABLES;
select @@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 session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
最后设置一下这两个参数就行了