我在一个新环境里部署程序时遇到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后,不能用双引号来引用字符串,因为它被解释为识别符