报错:
### SQL: SELECT DISTINCT ct.serial_no AS serial_no FROM xzfy_case_table AS ct WHERE ct.case_status != ? AND ct.registrant_id = ? ORDER BY application_date DESC LIMIT ?, ?
### Cause: java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column 'xzfy.ct.application_date' which is not in SELECT list; this is incompatible with DISTINCT
; uncategorized SQLException; SQL state [HY000]; error code [3065]; Expression #1 of ORDER BY clause is not in SELECT list, references column 'xzfy.ct.application_date' 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 'xzfy.ct.application_date' which is not in SELECT list; this is incompatible with DISTINCT
本地 在application_date前加上ct.可以正常运行不报错
互联网 在application_date前加上ct.还是报错
原因:
通过show VARIABLES查看mysql的详细配置
sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
是因为ONLY_FULL_GROUP_BY的存在
解决办法:(永久关闭ONLY_FULL_GROUP_BY)
1.找到配置文件/etc/my.cnf(或则关联文件夹找到mysql-server.cnf)
2.在上述文件内的[mysqld]后追加
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
3.保存配置文件后,重启Mysql即可。