• Expression #1 of ORDER BY clause is not in SELECT list, references column 'ekbX1.t0.name' which is not in SELECT list; this is incompatible with DISTINCT


    报错信息: 

    Expression #1 of ORDER BY clause is not in SELECT list, references column 'ekbX1.t0.name' which is not in SELECT list; this is incompatible with DISTINCT

    问题原因:

      mysql5.7.5及以上版本将sql_mode的ONLY_FULL_GROUP_BY模式默认设置为打开状态,会导致一些错误:

    1、我们使用GROUP BY查询时,出现在SELECT字段后面的只能是GROUP BY后面的分组字段,或使用聚合函数包裹着的字段,否则会报错如下信息:
      Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database.table.column' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 2、当使用ORDER BY查询时,不能使用SELECT DISTINCT去重查询。否则会报错如下信息:
    Expression #1 of ORDER BY clause is not in SELECT list, references column 'database.table.column' which is not in SELECT list; this is incompatible with DISTINCT

    查询验证:

    复制代码
    select version(); 查询版本
    select @@global.sql_mode   查询sql_mode
    复制代码

    解决方法:

       去除ONLY_FULL_GROUP_BY

    复制代码
    1、通过命令关闭:
    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';
    但该方法在重启Mysql服务后会失效,重启服务后会失效

    2、通过修改mysql的配置文件关闭ONLY_FULL_GROUP_BY SQL模式

    sudo vim /etc/mysql/conf.d/mysql.cnf

    文件底部追加:

    [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

    sudo service mysql restart

    复制代码
  • 相关阅读:
    高级I/O之存储映射I/O
    高级I/O之readn和writen函数
    高级I/O之readv和writev函数
    高级I/O之异步I/O
    高级I/O之I/O多路转接——pool、select
    高级I/O之STREAMS
    高级I/O之记录锁
    高级I/O之非阻塞I/O
    用于守护进程的出错处理函数
    守护进程之客户进程-服务器进程模型
  • 原文地址:https://www.cnblogs.com/curedfisher/p/13723187.html
Copyright © 2020-2023  润新知