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
对于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
View Code
使用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
View Code
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后，不能用双引号来引用字符串，因为它被解释为识别符