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
刘建
集体介绍
百度搜寻【前端刘建】或【全栈刘建】或【全栈工程师简历】能够搜到更多自己写的技术文章