• 由group by引发的sql_mode的学习


    前言  

      在一次使用group by查询数据库时,遇到了问题。下面先搭建环境,然后让问题复现,最后分析问题。

    一 问题复现

      mysql版本

      

      建表插入数据

      

      表的结构

      

      现在问题来了:我想查询上面表中每个部门年龄最大的人。

      sql语句:select NAME,dept,MAX(age) from mytable group by dept;

      

      此时会发现:查询结果中,dept 和 max(age) 是正确的但是name却是错误的!!!因为34岁的101部门应该指向 li4 ,36岁的102部门应该是 tian7。

    二 分析原因

      对于上面的问题主要要注意两个方面:

      1.为什么很多网上的帖子会出现这个sql语句查询报错的情况?

      2.为什么这种sql语句能执行但是查询结果不正确?

      

      下面针对这两个方面进行展开说明:

      1.

        有很多人可能使用上面的sql语句查询会报错,抛出这样的错误

        

        翻译过来就是:select list的表达式1不在group by子句中,并且包含非聚合列“test.mytable.name”,该列在功能上不依赖于group by子句中的列;这与sql _mode=only_full _group _by不兼容

        既然这里提到了sql_mode,那我就来查查sql_mode,如果你抛出了上面的错误,那么你的sql_mode中一定包含 ONLY_FULL_GROUP_BY

        show variables like 'sql_mode';

        

        什么是sql_mode?

        官方解释:

          The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients, depending on the value of the sql_mode system variable. DBAs can set the global SQL mode to match site server operating requirements, and each application can set its session SQL mode to its own requirements

        中文翻译:

          mysql服务器可以在不同的SQL模式下运行,并且可以根据sql_模式系统变量的值,为不同的客户机应用这些模式。DBA可以设置全局SQL模式以匹配站点服务器操作需求,并且每个应用程序都可以将其会话SQL模式设置为自己的需求。

        同时:

          sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题。

        在我们使用group by进行查询语句时,如果查询字段不在聚合列中(也就是group by后面的列),实际上这种sql语句是错误的,也是没有意义的,mysql是可以不支持这种查询方式的。在我们生产境中,最好将sql_mode设置为严格模式,这样在开发阶段就能发现问题,排除错误、无效的sql语句。

        ONLY_FULL_GROUP_BY就是规定了你不能使用错误、无效的group by去查询数据,所以,如果你的sql_mode是ONLY_FULL_GROUP_BY,那么执行上面那条sql语句就会抛出错误了。

      2.

        那为什么我使用上面的sql语句查询时,没有抛出错误,自然也就得到了解释,因为我当时的sql_mode 是这样的:

        

        并不包含ONLY_FULL_GROUP_BY,同时,我们看到的查询结果是错误的,这也从反面说明了 mysql规定的一些sql_mode的必要性,就是因为某些sql语句会出现问题,但是又不能及时被发现。

        所以在开发环境中,使用合适的严格的sql_mode 至关重要。

    三 有哪些sql_mode?

      官方说明先放在这: https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html

      简单介绍如下:

      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后,不能用双引号来引用字符串,因为它被解释为识别符

     

    四 如何设置sql_mode

      sql_mode的设置分为局部(session)设置和全局(global)设置 下面是我从mysql官网直接拿过来的:

      session就是指 你设置的sql_mode只会在你当前会话有效,当你注销用户,重新登录mysql后,仍然是你默认的sql_mode

      global就是指全局的。

      设置GLOBAL变量需要该SUPER权限,并影响从该时间开始连接的所有客户端的操作。设置SESSION变量仅影响当前客户端。每个客户端都可以随时更改其会话sql_mode值

      

      另外的一种设置方式:

      直接修改mysql配置文件,打开mysql配置文件可以看到:(没有sql_mode就自己加)

      

      这个sql_mode也就是我默认的,也可以在此处修改。

      最后多说一点,虽然mysql规定了这么多sql_mode用来约束sql语句规范,但是并不是每一个sql_mode都要配上,我们要在合适的环境选择合适的配置

    另,原创,转载注明出处!

     

          

  • 相关阅读:
    Quartz.net 定时任务在IIS中未按时执行
    扩展方法
    mysql 实用语句
    jquery each map
    js匿名函数多时注意
    ASP.NET MVC3调用分部视图
    eclipse快捷键
    regular 点滴
    适配器模式
    php代码实现简单图片下载
  • 原文地址:https://www.cnblogs.com/BoildWater/p/11388511.html
Copyright © 2020-2023  润新知