一、问题描述
- 自从博主更换
mysql-5.7.31-macos10.14-x86_64
之后出现Expression #11 of SELECT list is not in GROUP BY clause
这样的异常 - 这主要是因为在mysql5.7之后,
sql_model
默认了ONLY_FULL_GROUP_BY
,在select语句中跟sqlserver一样,如果使用group by语句,查询字段必须也得出现在group by之后或者使用聚合函数,否则将会报错。
select @@global.sql_mode
二、解决方案
- 临时解决方案
- 在数据库管理工具中重新设置sql_model。
将原先查询出来的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'
sql_model
去掉ONLY_FULL_GROUP_BY
即可临时解决。
- 在数据库管理工具中重新设置sql_model。
- 更改配置文件
- 首先停止MySQL服务,在etc下创建my.cnf文件
输入i进入插入模式,将以下文档复制进去sudo /usr/local/mysql/support-files/mysql.server stop sudo vim /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL
[client]
default-character-set=utf8
[mysqld]
log-error=/usr/local/mysql/data/mysqld.log
# skip-grant-tables
# 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
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
# 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
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
复制之后按esc,输入:wq保存退出即可。启动MySQL
sudo /usr/local/mysql/support-files/mysql.server restart
三、Sql_model
- 常用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
不能用双引号来引用字符串,因为它被解释为识别符