【刘建】MySQL遇见GROUP|【刘建】MySQL遇见GROUP BY clause and contains nonaggregated column 【刘建】MySQL遇见GROUPBYclausea

MySQL遇见GROUP BY clause and contains nonaggregated column 解决办法报错如下：

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'chinese.tenant_message.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

问题出现的原因：
MySQL 5.7.5及以上功能依赖检测功能。如果启用了ONLY_FULL_GROUP_BY SQL模式（默认情况下），MySQL将拒绝选择列表，HAVING条件或ORDER BY列表的查询引用在GROUP BY子句中既未命名的非集合列，也不在功能上依赖于它们。（5.7.5之前，MySQL没有检测到功能依赖关系，默认情况下不启用ONLY_FULL_GROUP_BY。有关5.7.5之前的行为的说明，请参见“MySQL 5.6参考手册”。）
解决方法二
成功的步骤
打开

sudo vim /etc/my.cnf

滚动到文件底部复制并粘贴

[mysqld] 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遇见GROUP|【刘建】MySQL遇见GROUP BY clause and contains nonaggregated column】

文章图片

保存并退出输入模式

service mysqld stopservice mysqld start

重启MySQL。
分享一份数据库的配置

# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html[client] default-character-set = utf8mb4[mysql] default-character-set = utf8mb4[mysqld] character-set-client-handshake = FALSE character-set-server=utf8mb4 collation-server = utf8mb4_unicode_ci init_connect='SET NAMES utf8mb4' max_connect_errors=1000 innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G# # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

刘建个人介绍
百度搜索【前端刘建】或【全栈刘建】或【全栈工程师简历】可以搜到更多本人写的技术文章