文章目录
- 环境
- 问题
- 分析
- 解决
- 方法一
- 方法二
- 扩展
- sql_mode属性含义
- sql_mode 三种作用域
环境
windows系统
问题
程序运行时遇到下面的问题:
Error Code: 3065
Expression #1 of ORDER BY clause is not in SELECT list, references column 'xxxx' which is not in SELECT list; this is incompatible with DISTINCT
Error Code: 1055
Expression #15 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xxxx' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
分析
- 查看当前使用的MySQL版本:
SELECT VERSION();
查询结果为:5.7.30
- 查询sql_mode:
select @@global.sql_mode;
查询sql_mode,本地优先级最高
select @@sql_mode
或者
SHOW VARIABLES LIKE 'sql_mode%';
查询结果为:
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
换了一个5.6 版本的MySQL数据库,查询sql_mode,结果如下:
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
分析发现,MySQL 5.7 多了很多限制。
解决
解决方法:
去除ONLY_FULL_GROUP_BY
方法一
通过命令:设置全局的sql_mode
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';
设置session级别的的sql_mode,不用加上面的global。
注意,该方法在重启Mysql服务后会失效,重启服务后会失效
方法二
修改mysql的配置文件,来关闭ONLY_FULL_GROUP_BY SQL模式
先停止mysql windows 服务
net stop mysql
打开本地的MySQL 配置文件:
修改:
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
保存并启动mysql服务
net start mysql
扩展
sql_mode属性含义
- ONLY_FULL_GROUP_BY
这个模式会对 GROUP BY 进行合法性检查,对于 GROUP BY 操作,如果在SELECT 中的列,没有在 GROUP BY 中出现,那么将认为这个 SQL 是不合法的,因为列不在 GROUP BY 从句中 - STRICT_TRANS_TABLES
这就是严格模式,在这个模式下会对数据进行严格的校验,错误数据不能插入,报error 错误。如果不能将给定的值插入到事务表中,则放弃该语句。对于非事务表,如果值出现在单行语句或多行语句的第1行,则放弃该语句。没有 STRICT_TRANS_TABLES,即非严格模式,int类型的字段,使用空字符串,存为 0; - NO_ZERO_IN_DATE
这个模式影响着日期中的月份和天数是否可以为 0(注意年份是非 0 的),这个模式也取决于严格模式是否被启用。如果这个模式未启用,那么日期中的零部分被允许并且插入没有警告。如果这个模式启用,那么日期中的零部分插入被作为 0000-00-00并且产生一个警告 。
这个模式需要注意下,如果启用的话,需要 STRICT_TRANS_TABLES 和 NO_ZERO_IN_DATE 同时启用,否则不起作用,也就是 - ERROR_FOR_DIVISION_BY_ZERO
如果这个模式未启用,那么零除操作将会插入空值并且不会产生警告;如果这个模式启用,零除操作插入空值并产生警告;如果这个模式和严格模式都启用,零除从操作将会产生一个错误。 - NO_AUTO_CREATE_USER
禁止使用 grant 语句自动创建用户,除非认证信息被指定。 - NO_ENGINE_SUBSTITUTION
此模式指定当执行 create 语句或者 alter 语句指定的存储引擎没有启用或者没有编译时,控制默认存储引擎的自动切换。默认是启用状态的。
sql_mode 三种作用域
分别是:会话级别、全局级别、配置(永久生效)级别。
无论设置全局,还是session的变量sql_mode,重启mysql后都会失效;