文章目录

  • 环境
  • 问题
  • 分析
  • 解决
  • 方法一
  • 方法二
  • 扩展
  • 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

分析

  1. 查看当前使用的MySQL版本：
SELECT VERSION();

查询结果为：5.7.30

  1. 查询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属性含义

  1. ONLY_FULL_GROUP_BY
    这个模式会对 GROUP BY 进行合法性检查，对于 GROUP BY 操作，如果在SELECT 中的列，没有在 GROUP BY 中出现，那么将认为这个 SQL 是不合法的，因为列不在 GROUP BY 从句中
  2. STRICT_TRANS_TABLES
    这就是严格模式，在这个模式下会对数据进行严格的校验，错误数据不能插入，报error 错误。如果不能将给定的值插入到事务表中，则放弃该语句。对于非事务表，如果值出现在单行语句或多行语句的第1行，则放弃该语句。没有 STRICT_TRANS_TABLES，即非严格模式，int类型的字段，使用空字符串，存为 0;
  3. NO_ZERO_IN_DATE
    这个模式影响着日期中的月份和天数是否可以为 0（注意年份是非 0 的），这个模式也取决于严格模式是否被启用。如果这个模式未启用，那么日期中的零部分被允许并且插入没有警告。如果这个模式启用，那么日期中的零部分插入被作为 0000-00-00并且产生一个警告 。
    这个模式需要注意下，如果启用的话，需要 STRICT_TRANS_TABLES 和 NO_ZERO_IN_DATE 同时启用，否则不起作用，也就是
  4. ERROR_FOR_DIVISION_BY_ZERO
    如果这个模式未启用，那么零除操作将会插入空值并且不会产生警告；如果这个模式启用，零除操作插入空值并产生警告；如果这个模式和严格模式都启用，零除从操作将会产生一个错误。
  5. NO_AUTO_CREATE_USER
    禁止使用 grant 语句自动创建用户，除非认证信息被指定。
  6. NO_ENGINE_SUBSTITUTION
    此模式指定当执行 create 语句或者 alter 语句指定的存储引擎没有启用或者没有编译时，控制默认存储引擎的自动切换。默认是启用状态的。

sql_mode 三种作用域

分别是：会话级别、全局级别、配置（永久生效）级别。
无论设置全局，还是session的变量sql_mode，重启mysql后都会失效；