MySQL将bigint类型b.ID转为字符串解决1292警告及性能问题

你遇到的警告和性能问题根源是类型不匹配导致的隐式转换：MySQL在比较varchar(30)类型的HBODBED和bigint(20)类型的b.ID时，会统一将字段值转为DOUBLE类型再做比对，HBODBED中的空字符串转DOUBLE时就会触发截断警告，同时隐式转换会导致HBODBED上的索引失效，查询速度大幅降低。

方案1：直接在原查询基础上做类型转换 #

你只需要在子查询的关联条件中，用CAST函数将b.ID转为字符串类型即可，修改后的查询如下：

SELECT b.ID,  
(SELECT SUM(case when ABBO != '0' then 1 else 0 end)
       FROM OCCASIONS where HBODBED = CAST(b.ID AS CHAR)) as BEOORDELINGEN,
(SELECT SUM(case when ABBO != '0' then ABBO else 0 end)
       FROM OCCASIONS where HBODBED = CAST(b.ID AS CHAR)) as TOTAAL
FROM BEDRIJVEN b WHERE ACTIVE = '1' AND GROEP = '0'
ORDER BY APERC DESC LIMIT 0, 50;

也可以用CONVERT(b.ID, CHAR)替代CAST(b.ID AS CHAR)，二者效果完全一致。

方案2：优化为关联聚合查询，性能更高 #

原写法的相关子查询需要对每条外层查询结果执行2次OCCASIONS表查询，总查询次数高达100次。你可以提前聚合OCCASIONS表再做关联，只需要查询1次OCCASIONS表，性能更好：

SELECT 
  b.ID,
  COALESCE(o.BEOORDELINGEN, 0) AS BEOORDELINGEN,
  COALESCE(o.TOTAAL, 0) AS TOTAAL
FROM BEDRIJVEN b
LEFT JOIN (
  SELECT 
    HBODBED,
    SUM(case when ABBO != '0' then 1 else 0 end) AS BEOORDELINGEN,
    SUM(case when ABBO != '0' then ABBO else 0 end) AS TOTAAL
  FROM OCCASIONS
  GROUP BY HBODBED
) o ON o.HBODBED = CAST(b.ID AS CHAR)
WHERE b.ACTIVE = '1' AND b.GROEP = '0'
ORDER BY b.APERC DESC LIMIT 0, 50;

这里的COALESCE函数是为了处理没有对应OCCASIONS记录的企业，返回0而非NULL，和原查询逻辑完全一致。

内容的提问来源于stack exchange，提问作者collin ter steege