sql_mode=only_full_group_by以及ANY_VALUE()

MySQL报错：sql_mode=only_full_group_by解决方法

-- 查询自定义变量 sql_mode
SELECT @sql_mode;
-- 查询系统变量 sql_mode（会话变量）
SELECT @@sql_mode;
-- 查询当前会话变量
SELECT @@SESSION.sql_mode;
-- 查询全局变量
select @@GLOBAL.sql_mode;

SET @@SESSION.sql_mode='';
-- 设置当前会话变量 sql_mode='ONLY_FULL_GROUP_BY' 临时修改，当前客户端当次连接有效
SET @@SESSION.sql_mode='ONLY_FULL_GROUP_BY';

在变量中新增 ONLY_FULL_GROUP_BY

 SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

ONLY_FULL_GROUP_BY

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中；

在MySQL 5.7后，MySQL默认开启了SQL_MODE严格模式，对数据进行严格校验。如果代码中含有group by聚合操作，那么select中的列，除了使用聚合函数之外的，如max()、min()等，都必须出现在group by中。

解决方法一：直接修改数据库配置（关闭ONLY_FULL_GROUP_BY）（重启数据库会失效）

-- 获取sql_mode，去掉ONLY_FULL_GROUP_BY即可
select @@GLOBAL.sql_mode;

重启数据库会失效

解决方法二：直接修改数据库配置（关闭ONLY_FULL_GROUP_BY）（永久生效）

修改配置文件my.ini

在[mysqld]模块下新增一行配置：

sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

运行后重启，即可永久生效

方法三：使用 any_value() 或 聚合函数

1. any_value()：将分到同一组的数据里第一条数据的指定列值作为返回数据。 （any_value()函数就是MySQL提供的用来抑制ONLY_FULL_GROUP_BY值被拒绝的）

 原SQL

SELECT
 p.id AS idTicket,
 p.product_name AS ticketName, CONCAT(cp.product_id,"_",cp.allocation_id) AS sellPriceId,
 pp.id AS sellPlanId,
 pp.`name` AS sellPlanName,
 scenic.id AS idTheme,
 scenic.org_name AS themName,
 p.standard_price AS ticketPrice,
 pql.channel_price AS realPrice,
 pql.min_num AS ticketNum,
 pql.max_num AS ticketNumMax,
 p.product_type AS productClassName,
 pp.delay_valid_time AS delayTakeTicket,
 pp.today_sale_end_time AS timeLast,
 if(e.total_quota IS NULL, "10201020","10201010") isInventory,
 e.total_quota- IFNULL(SUM(x.num),0) AS inventory,
 if(LOCATE("notAllow",pp.return_rule)>0, "10201020","10201010") isReturn,
 if(LOCATE("noRequired",sr.need_id)>0, "10201020","10201010") isTeamTicketIDCard
FROM
 channel_product cp
INNER JOIN product p ON cp.product_id = p.id AND p.is_deleted = 'false' AND p.audit_status = 'enabled'
LEFT JOIN org_info oi ON oi.id = p.org_id
INNER JOIN product_policy_allocation a ON a.id = cp.allocation_id
INNER JOIN product_policy pp ON a.policy_id = pp.id AND pp.audit_status = 'enabled' AND pp.is_group = 'false' AND pp.is_deleted = 'false'
LEFT JOIN product_quota_ladder pql ON cp.quota_id = pql.quota_id AND pql.is_deleted='false'
LEFT JOIN calendar_price_policy cpp ON cp.policy_id = cpp.policy_id AND cpp.org_id = p.org_id AND cpp.product_id = p.id AND DATE(cpp.price_date) = DATE(NOW()) AND cpp.is_deleted = 'false'
INNER JOIN product_sale_rule sr ON sr.product_id =p.id AND sr.is_deleted = 'false'
LEFT JOIN product_policy_quota e ON e.policy_id = pp.id AND e.is_deleted = 'false'
LEFT JOIN product_quota_change x ON x.policy_id =pp.id AND x.product_id = p.id
LEFT JOIN org_info scenic ON pp.scenic_id = scenic.id
WHERE
 a.from_org_id = 1423089566218326016
AND a.to_org_id = 1173855415096709120
AND cp.is_deleted = 'false' AND cp. STATUS = 'open' AND pql.is_deleted = 'false' AND a.is_deleted = 'false' AND cp.product_type in ("ticket","ice")
GROUP BY 
cp.policy_id,cp.product_id,cp.allocation_id

使用any_value()忽略当前查询字段不进行分组校验的SQL

SELECT
 p.id AS idTicket,
 p.product_name AS ticketName, CONCAT(cp.product_id,"_",cp.allocation_id) AS sellPriceId,
 pp.id AS sellPlanId,
 pp.`name` AS sellPlanName,
 scenic.id AS idTheme,
 scenic.org_name AS themName,
 p.standard_price AS ticketPrice, 
 ANY_VALUE(pql.channel_price) AS realPrice, 
 ANY_VALUE(pql.min_num) AS ticketNum, 
 ANY_VALUE(pql.max_num) AS ticketNumMax,
 p.product_type AS productClassName,
 pp.delay_valid_time AS delayTakeTicket,
 pp.today_sale_end_time AS timeLast, 
 ANY_VALUE(if(e.total_quota IS NULL, "10201020","10201010")) AS isInventory, 
 ANY_VALUE(e.total_quota - IFNULL(SUM(x.num),0)) AS inventory, 
 ANY_VALUE(if(LOCATE("notAllow",pp.return_rule)>0, "10201020","10201010")) isReturn, 
 ANY_VALUE(if(LOCATE("noRequired",sr.need_id)>0, "10201020","10201010")) isTeamTicketIDCard
FROM
 channel_product cp
INNER JOIN product p ON cp.product_id = p.id AND p.is_deleted = 'false' AND p.audit_status = 'enabled'
LEFT JOIN org_info oi ON oi.id = p.org_id
INNER JOIN product_policy_allocation a ON a.id = cp.allocation_id
INNER JOIN product_policy pp ON a.policy_id = pp.id AND pp.audit_status = 'enabled' AND pp.is_group = 'false' AND pp.is_deleted = 'false'
LEFT JOIN product_quota_ladder pql ON cp.quota_id = pql.quota_id AND pql.is_deleted='false'
LEFT JOIN calendar_price_policy cpp ON cp.policy_id = cpp.policy_id AND cpp.org_id = p.org_id AND cpp.product_id = p.id AND DATE(cpp.price_date) = DATE(NOW()) AND cpp.is_deleted = 'false'
INNER JOIN product_sale_rule sr ON sr.product_id =p.id AND sr.is_deleted = 'false'
LEFT JOIN product_policy_quota e ON e.policy_id = pp.id AND e.is_deleted = 'false'
LEFT JOIN product_quota_change x ON x.policy_id =pp.id AND x.product_id = p.id
LEFT JOIN org_info scenic ON pp.scenic_id = scenic.id
WHERE a.from_org_id = 1423089566218326016 AND a.to_org_id = 1173855415096709120 AND cp.is_deleted = 'false' AND cp. STATUS = 'open' AND pql.is_deleted = 'false' AND a.is_deleted = 'false' AND cp.product_type in ("ticket","ice")
GROUP BY 
cp.product_id,cp.allocation_id,
cp.policy_id,p.id,p.product_name, sellPriceId,pp.id,pp.`name`,scenic.id,scenic.org_name,p.standard_price,
p.product_type,pp.delay_valid_time,pp.today_sale_end_time

2、使用聚合函数，例如SUM()，MAX（）等，使用聚合函数的查询列不做分组校验

3、根据业务调整代码及其SQL

sql_mode其它模式

NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

STRICT_TRANS_TABLES
在该模式下，如果一个值不能插入到一个事务中，则中断当前的操作，对非事务表不做限制

 

NO_ZERO_IN_DATE
在严格模式下，不允许日期和月份为零

 

NO_ZERO_DATE
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告

 

ERROR_FOR_DIVISION_BY_ZERO
在insert或update过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时Mysql返回NULL

 

NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户

 

NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

 

PIPES_AS_CONCAT
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样是，也和字符串的拼接函数Concat想类似

 

ANSI_QUOTES
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符