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

保留并退出输出模式

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 = FALSEcharacter-set-server=utf8mb4collation-server = utf8mb4_unicode_ciinit_connect='SET NAMES utf8mb4'max_connect_errors=1000innodb_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 = 2Mdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidsql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

刘建

集体介绍

百度搜寻【前端刘建】或【全栈刘建】或【全栈工程师简历】能够搜到更多自己写的技术文章