Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lyjp_contract.dic.value'

背景:

由于疫情影响,居家办公,数据库移植到家里的本地数据库,项目运行起来以后报错

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'lyjp_contract.dic.value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

 

代码:

SELECT
    con.STATUS AS STATUS,
    dic.VALUE
    AS statusName,
    COUNT( STATUS ) AS contractCount 
FROM
    contract con
    LEFT JOIN sys_dict dic ON con.STATUS = dic.CODE 
    AND dic.type = 'home_contract_status' 
WHERE
    con.STATUS <> 5 
GROUP BY
    con.STATUS

 

 

原因:

是由于默认的 MySQL 配置中 sql_mode 配置了 only_full_group_by，需要 GROUP BY 中包含所有 在 SELECT 中出现的字段。

only_full_group_by：使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行。

 

解决方案:

1.先查询配置

select @@global.sql_mode

 

2.修改配置

Linux系统:

sudo vim /etc/mysql/conf.d/mysql.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

sudo service mysql restart

 

Windows:

set @@global.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后数据不改变

我直接在my.ini配置文件中添加了配置,之后就正常使用了

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\mysql-8.0.17-winx64 
# 设置mysql数据库的数据的存放目录
datadir= D:\mysql-8.0.17-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 跳过登录密码验证
#skip-grant-tables
#日志过期时间
expire_logs_days=3

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION