MySQL报错1055- Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated colum
去除 sql_mode 中的 ONLY_FULL_GROUP_BY
ONLY_FULL_GROUP_BY
Reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.
执行脚本 修改当前的sql_mode,但 mysql 重启之后将会失效
Mysql 版本小于 8
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';
mysql 版本大于 8 去除 NO_AUTO_CREATE_USER
set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
修改配置文件 windows my.ini,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'
mysql 版本大于 8 去除 NO_AUTO_CREATE_USER
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
NO_AUTO_CREATE_USER
In MySQL 8.0.11, several deprecated features related to account management have been removed, such as use of the GRANT statement to modify nonprivilege characteristics of user accounts, the NO_AUTO_CREATE_USER SQL mode, the PASSWORD() function, and the old_passwords system variable.
Replication from MySQL 5.7 to 8.0 of statements that refer to these removed features can cause replication failure. Applications that use any of the removed features should be revised to avoid them and use alternatives when possible, as described in Features Removed in MySQL 8.0.
To avoid a startup failure on MySQL 8.0, remove any instance of NO_AUTO_CREATE_USER from sql_mode system variable settings in MySQL option files.
Loading a dump file that includes the NO_AUTO_CREATE_USER SQL mode in stored program definitions into a MySQL 8.0 server causes a failure. As of MySQL 5.7.24 and MySQL 8.0.13, mysqldump removes NO_AUTO_CREATE_USER from stored program definitions. Dump files created with an earlier version of mysqldump must be modified manually to remove instances of NO_AUTO_CREATE_USER.
本文作者:qfluck
本文链接:https://www.cnblogs.com/qfluck/p/17097498.html
版权声明:本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步