今天写sql,用到group by,出现了异常,异常如下:
[Err] 1055 - Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘mfms.wt.WORKPIECE_TYPE_NUMBER’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
问题原因:
在mysql 5.7版本及以上,默认的配置是: sql_mode=“ONLY_FULL_GROUP_BY”,这个设置将会导致,如果在一个sql中,一个字段没有在target list后面(select后面)和group by后面同时出现,或者聚合函数的值,那么这条sql会出异常。
解决方法:
第一种: any_value()函数
在sql中除了group by后面的字段, 其他出现在select后面的字段中 ,加入
any_value() 函数,如:
第二种: 配置my.ini文件:
修改mysql配置文件, my.ini
分别在[mysqld]和[mysql]下面添加一段:
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4
[mysql]
prompt="MySQL [\\d]> "
no-auto-rehash
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[mysqld]
port = 3306
socket = /tmp/mysql.sock
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
成功!