项目部署mysql报错3065

报错:


### 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即可。

上一篇:Linux常用操作命令


下一篇:03-JAVA设计模式-迭代器模式