「DISTINCT 识别不了这个关键字」this is incompatible with DISTINCT 之Mysql报错问题 - 无名 - CSDN博客 龙美珍

[](http://creativecommons.org/licenses/by-sa/4.0/)版权声明：本文为博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。

本文链接：https://blog.csdn.net/qq_37022150/article/details/79020488

mysql经常使用关键字,5.7以前的版本能正常使用，但是之后版本遇到这样的错误:

Caused by: java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column ‘xxxxxxxxxxxxxxx’ which is not in SELECT list; this is incompatible with DISTINCT

错误提示DISTINCT不兼容，要么更改SQL，但是对于开发者来讲，sql运行一直都是正常的，可能是mysql 版本升级导致的安全问题，5.7.x安全性提升了很多，解决办法可以考虑修改MySQL配置文件,找到对应的my.cnf或者my.ini

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

添加该行就ok!

还有一种错也是不兼容的问题导致的

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘nctest.pivot.id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

环境
mysql 5.7

问题SQL

SELECT DISTINCT
    f.org_id orgId,
    o. NAME orgName,
    f.build_id buildId,
    b. NAME buildName,
    (
        CASE
        WHEN f.unit_id IS NULL THEN
            ''
        ELSE
            f.unit_id
        END
    ) unitId,
    (
        CASE
        WHEN f.unit_id IS NULL THEN
            ''
        ELSE
            u. NAME
        END
    ) unitName,
    f.id floorId,
    f. NAME floorName,
    r.id roomId,
    r. NAME roomName,
    r.area
FROM
    tb_organization o
LEFT JOIN tb_build b ON o.id = b.org_id
AND b.enabled = 1
LEFT JOIN tb_unit u ON u.org_id = o.id
AND u.build_id = b.id
LEFT JOIN tb_floor f ON f.org_id = o.id
AND (
    f.build_id = b.id
    AND (
        CASE
        WHEN f.unit_id IS NULL THEN
            1 = 1
        ELSE
            f.unit_id = u.id
        AND u.enabled = 1
        END
    )
)
AND f.enabled = 1
LEFT JOIN tb_room_no r ON r.floor_id = f.id
AND r.enabled = 1
WHERE
    o.id = 1
AND f.org_id IS NOT NULL
ORDER BY
    o.id,
    b.id,
    f.unit_id,
    f.id,
    r.id

解决办法
server_variables
变量
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

改

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Original url: Access
Created at: 2019-08-23 15:07:51
Category: default
Tags: none

未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到

请先后发表评论
  • 最新评论
  • 总共0条评论