Expression #1 of ORDER BY clause is not in SELECT list, references column ‘ccsemos_new.zc_user_menu_used.sort’ which is not in SELECT list; this is incompatible with DISTINCT
查看sql_mode
SELECT @@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
2、去掉ONLY_FULL_GROUP_BY,重新设置值
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';
3、上面是改变了全局sql_mode,对于新建的数据库有效。对于已存在的数据库,则需要在对应的数据下执行
SET 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数据库重启后依然无效,下列方式重启后依然生效
找到MySQL的配置文件,在linux系统上/etc/my.cnf文件,查询sql_mode字段,我并没有在配置文件中找到这个关键字,所以我手动添加进去:
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
需要注意的一点是一定要添加在[mysqld]配置内,这样添加完后重启mysql才会生效,退出数据库:exit,重启命令:
service mysqld restart