MySQL隐式转换带来的BUG

2024-06-01 21:27:40 2

问题sql

SELECT
IF(o.type = 'A', '优惠', '原价') AS type,
SUM(amount) AS sum
FROM order o
WHERE
GROUP BY IF(o.type = 'A', '优惠', '原价')

实际type是tinyint 0-优惠 1-原价

这里用o.type做判断,  A 实际执行了隐式类型转换

类似这样

SELECT 'A' + 0

为0;

歪打正着, 也能"正常"运行

我基于上面的惯性思维, 写下了下面这段sql

SELECT
DATE_FORMAT(create_date, '%Y-%m') AS month,
SUM(
IF(o.type = 'A',
	IF(amount IS NULL, 0, amount), 
	0)
) AS 优惠金额,
SUM(
IF(o.type = 'B',
	IF(amount IS NULL, 0, amount), 
	0)
) AS 原价金额
FROM order o
GROUP BY DATE_FORMAT(create_date, '%Y-%m');

结果原价金额与优惠金额一致了, 因为SELECT 'A' + 0 和 SELECT 'B' + 0  结果都是0

• sql