MySql报错only_full_group_by的解决办法

我在一个新环境里部署程序时遇到MySql报错only_full_group_by，之前已经遇到过一次同样的问题，当时没有总结经验，导致这次解决时耗费了不少时间，这里把本次的处理过程进行记录总结，同时分享给大家

MySql数据库报错如下，从字面意思理解是select后面查询的字段没有出现在group by中

 

 原来导致不符合mysql8.默认配置的

ONLY_FULL_GROUP_BY

• ONLY_FULL_GROUP_BY

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

SELECT
            distinct score,radio_show,r.type as type
        FROM
            bbs_rank r, bbs_bank b, bbs_radio ra
        where r.id =b.rank_id and ra.rank_id= r.id  and  ra.type=#{period} and b.name = #{bankName}
          and ra.`year_month1`=#{year_mo} group by score

由于生产服务器不能改mysql的配置文件中的sql_mode. 只能修改sql。原来的没有写radio_show,r.type在group by 。又补充上了。这个一下子ok了。

  SELECT
            distinct score,radio_show,r.type as type
        FROM
            bbs_rank r, bbs_bank b, bbs_radio ra
        where r.id =b.rank_id and ra.rank_id= r.id  and  ra.type=#{period} and b.name = #{bankName}
          and ra.`year_month1`=#{year_mo} group by score,radio_show,r.type

如果修改sql比较多的。还是建议在mysql.conf添加一下配置sql_mode=

• STRICT_TRANS_TABLES,
• NO_ENGINE_SUBSTITUTION

[mysqld]
# skip-grant-tables
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_allowed_packet=32M
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

sql_mode常用值

• ONLY_FULL_GROUP_BY

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

• NO_AUTO_VALUE_ON_ZERO

该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

• STRICT_TRANS_TABLES

在该模式下，如果一个值不能插入到一个事务中，则中断当前的操作，对非事务表不做限制

• NO_ZERO_IN_DATE

在严格模式下，不允许日期和月份为零

• NO_ZERO_DATE

设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告

• ERROR_FOR_DIVISION_BY_ZERO

在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时Mysql返回NULL

• NO_AUTO_CREATE_USER

禁止GRANT创建密码为空的用户

• NO_ENGINE_SUBSTITUTION

如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

• PIPES_AS_CONCAT

将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样是，也和字符串的拼接函数Concat想类似

• ANSI_QUOTES

启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符