526互联

mysql 5.7 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ...报错

发布时间 2023-05-16 17:08:47作者: 四海骄阳

https://www.shuzhiduo.com/A/gGdX3BNp54/

https://blog.csdn.net/wufaqidong1/article/details/126263023

 

使用mysql在执行一条插入语句时

  1. insert into channel(channel_id, channel_no,channel_name) values(1, '', "hhh");

报错：Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因：sql _mode中only _full _group _by不兼容的问题

解决：去掉sql_mode中的only_full_group_by

先查看sql_mode:

  1. show variables like "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可以看到有ONLY_FULL_GROUP_BY，

设置去除ONLY_FULL_GROUP_BY：

  1. 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'

再次执行插入sql就不报错了

 

--实际操作代码

show variables like "sql_mode";
set sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

INSERT INTO `prescription` (`memo`) VALUES ('1063112');
alter table `prescription` AUTO_INCREMENT=1000000000;
INSERT INTO `prescription` (`memo`) VALUES ('1063112');