0%

group by 之sql_mode

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'xiaohe.a.id'; this is incompatible with sql_mode=only_full_group_by.

一、准备工作

  1. 建表并初始化记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    CREATE TABLE `student` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(20) NOT NULL DEFAULT '',
    `phone` char(11) NOT NULL DEFAULT '',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

    INSERT INTO `student` VALUES (1, '张三', '18812345678');
    INSERT INTO `student` VALUES (2, '李四', '18856781234');
    INSERT INTO `student` VALUES (3, '王五', '18811111111');
    INSERT INTO `student` VALUES (4, '马六', '18811112222');

    CREATE TABLE `score` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sid` int(11) NOT NULL,
    `score` float(4,2) NOT NULL,
    `subject` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

    INSERT INTO `score` VALUES (1, 1, 80.00, '语文');
    INSERT INTO `score` VALUES (2, 1, 85.00, '数学');
    INSERT INTO `score` VALUES (3, 1, 89.00, '英语');
    INSERT INTO `score` VALUES (4, 2, 50.00, '语文');
    INSERT INTO `score` VALUES (5, 2, 30.00, '数学');
    INSERT INTO `score` VALUES (6, 2, 90.00, '英语');
    INSERT INTO `score` VALUES (7, 3, 60.00, '语文');
    INSERT INTO `score` VALUES (8, 3, 30.00, '数学');
    INSERT INTO `score` VALUES (9, 3, 79.00, '英语');
    INSERT INTO `score` VALUES (10, 4, 70.00, '语文');
    INSERT INTO `score` VALUES (11, 4, 35.00, '数学');
    INSERT INTO `score` VALUES (12, 4, 95.00, '英语');
  2. 查询总分排名前三的记录

    1
    2
    3
    4
    5
    select a.id,a.name,sum(b.score) total_score from score b 
    join student a on a.id=b.sid
    group by b.sid
    order by total_score desc
    limit 0,3

  • 尝试以student表为主表关联查询

    1
    2
    3
    4
    5
    select a.id,a.name,sum(b.score) total_score from student a 
    left join score b on a.id=b.sid
    group by b.sid
    order by total_score desc
    limit 0,3

    报错:In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'xiaohe.a.id'; this is incompatible with sql_mode=only_full_group_by

二、问题原因

      在MySQL5.7.5后,默认开启了ONLY_FULL_GROUP_BY,所以导致了之前的一些SQL无法正常执行。对于GROUP BY聚合操作,如果在SELECT中的列没有在GROUP BY中出现,那么这个SQL是不合法的。
参考

sql_mode介绍

  1. ONLY_FULL_GROUP_BY:在该模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值。
  2. STRICT_TRANS_TABLES:在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制。
  3. NO_ZERO_IN_DATE:在严格模式下,不允许日期和月份为零。
  4. NO_ZERO_DATE:设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。
  5. ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如果未给出该模式,那么数据被零除时MySQL返回NULL。
  6. NO_AUTO_CREATE_USER:禁止GRANT创建密码为空的用户。
  7. NO_ENGINE_SUBSTITUTION:如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常。
  8. PIPES_AS_CONCAT:将”||”视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似。
  9. ANSI_QUOTES:启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符。
  10. NO_AUTO_VALUE_ON_ZERO:该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。

三、解决方案

  1. 方案一、修改配置文件my.cnf,文件最后加入以下设置,重启mysql,永久有效
    sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"

  2. 方案二、修改当前会话配置,mysql重启后失效

    • 查看当前模式

      1
      2
      3
      show variables like '%sql_mode';
      show session variables like '%sql_mode%';
      show global variables like '%sql_mode%';
    • 修改当前模式

      1
      2
      set global sql_mode='';
      set session sql_mode='';