• mysql5.7之only_full_group_by


    mysql进入5.7版本之后,默认开启sql_mode的only_full_group_by模式。

    影响在于:

      sql语句中如果有group by部分,那么select部分不能出现group by框定的字段以外的字段(聚合函数除外)

    举个例子:

      select count(*),ORG_NAME,ORG_CODE from PUB_ORGAN group by ORG_CODE

      这个sql在5.7严格模式下就会执行报错,因为ORG_NAME不在group by字段中包含

    报错情况:

    1.ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ORG_NAME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

    问题根源:

    -- 使用navicate执行sql语句
    SELECT @@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

    修改方案:

    (1)全局修改

     

    #查找mysql初始化文件my.cnf
    ps -ef | grep mysql
    #找到mysqld脚本运行参数defaults-file,这里假设是:--defaults-file=/apps/my.cnf

    修改文件内容
    搜索sql_mode把ONLY_FULL_GROUP_BY删除、保存、重启mysql

    (2)线上修改

    -- 管理员登录
    -- 查询当前上下文
    SELECT @@GLOBAL.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
    
    -- 修改当前上下文
    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 ';

    参照网址:

      https://blog.csdn.net/wangyunfeis/article/details/77911704

  • 相关阅读:
    Xcode waring: no rule to process file *** 警告提示
    Assigning to "id<CALayerDelegate> _Nullable" from incompatible type "ZXCapture *const __strong" 的警告提示信息
    图片编码简介
    oc温习八:static、extern、const 的了解
    转:Objective-C新特性__nonnull和__nullable
    oc温习七:结构体与枚举
    oc温习六:预处理指令
    oc温习五:字符串
    OC温习四:数组
    oc温习三:常用函数
  • 原文地址:https://www.cnblogs.com/chendeming/p/9012785.html
Copyright © 2020-2023  润新知