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 stop

service 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=0

log-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

刘建

集体介绍

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