首先:表
聚合语句:
mysql> select country,SUM(fee) as tot from member group by country;
+---------+-------+
| country | tot |
+---------+-------+
| china | 19700 |
| UK | 7000 |
| US | 5000 |
+---------+-------+
3 rows in set (0.08 sec)
这条语句是正确的不会报错(5.6 5.7都不会)
区别:
1.mysql 5.6
mysql> select country,SUM(fee),name as tot from member group by country;
+---------+----------+--------+
| country | SUM(fee) | tot |
+---------+----------+--------+
| china | 19700 | 刘不二 |
| UK | 7000 | jack |
| US | 5000 | Tom |
+---------+----------+--------+
3 rows in set (0.07 sec)
2.mysql 5.7
mysql> select country,SUM(fee),name as tot from member group by country;
1055 - Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mysql.member.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
我们查看sql_mode
01 |
|
查询出来的值为:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
通俗解释:
当加入一个name时,5.6不会报错但是5.7会,因为一个国家里面有很多不同的name字段,你到底想展示哪一个呢?5.7不知道
,但是5.6会默认第一个。
解决办法:
修改my.cnf(windows下是my.ini)配置文件,删掉only_full_group_by这一项
若我们项目的mysql安装在ubuntu上面,找到这个文件打开一看,里面并没有sql_mode这一配置项,想删都没得删。
当然,还有别的办法,打开mysql命令行,执行命令
1 |
|
这样就可以查出sql_mode的值,复制这个值,在my.cnf中添加配置项(把查询到的值删掉only_full_group_by这个选项,其他的都复制过去):
1 |
|
如果 [mysqld] 这行被注释掉的话记得要打开注释。然后重重启mysql服务
注:使用命令
1 |
|
这样可以修改一个会话中的配置项,在其他会话中是不生效的。