在做業(yè)務(wù)查詢(xún)列表時(shí)候出現(xiàn)問(wèn)題，捕獲的異常如下
SQL state [HY000]; error code [3065]; Expression #1 of ORDER BY clause is not in SELECT list, references column 'nav_db.map.order_index' which is not in SELECT list; this is incompatible with DISTINCT; nested exception is java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column 'nav_db.map.order_index' which is not in SELECT list; this is incompatible with DISTINCT
在程序中運(yùn)行時(shí)會(huì)報(bào)錯(cuò)茎芋，但是使用naticat卻執(zhí)行成功。
通過(guò)查閱資料發(fā)現(xiàn)在mysql5.7.5及以上版本實(shí)現(xiàn)了對(duì)功能依賴(lài)的檢測(cè)。默認(rèn)啟用了ONLY_FULL_GROUP_BY SQL模式锈麸。在該模式下烁涌，
- 使用GROUP BY查詢(xún)時(shí)苍碟，出現(xiàn)在SELECT字段后面的只能是GROUP BY后面的分組字段，或使用聚合函數(shù)包裹著的字段撮执，否則會(huì)報(bào)錯(cuò)如下信息：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
- 當(dāng)使用ORDER BY查詢(xún)時(shí)微峰，不能使用SELECT DISTINCT去重查詢(xún)。否則會(huì)報(bào)錯(cuò)如下信息：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
解決方法
- 通過(guò)命令關(guān)閉ONLY_FULL_GROUP_BY SQL模式
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';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
- 通過(guò)修改mysql的配置文件關(guān)閉ONLY_FULL_GROUP_BY SQL模式
[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'
