MariaDB中使用变量与直接传值的差异及类型问题排查

核心问题：类型不匹配导致查询失效 #

你遇到的确实是数据类型不匹配的问题：

• @ids是字符串类型（即使内容是逗号分隔的数字），当你在IN (@ids)中使用它时，数据库会将a.id（数值类型）转换为字符串，然后与整个@ids字符串进行匹配。
• 比如a.id = 4094会被转成'4094'，然后和'4094,8562,...'这个完整字符串比较，显然不相等，因此没有任何数据被匹配到，结果为0。
• 而直接写IN (4094,8562,...)时，数据库会把每个值当成数值处理，能正确匹配a.id。

三种解决办法 #

1. 使用FIND_IN_SET函数 #

利用MySQL的FIND_IN_SET函数，它会自动将逗号分隔的字符串拆分为列表，并检查数值是否在其中：

SET @ids = '4094,8562,11144,3017,5815,11121,1957,4095,8563,11145,3018,5816,8527,11122,1959,4096,8564,3020,5817,8528,11123,1961,4097,8571,3021,6020,8535,11128,1962,5181,8572,3581,6021';

SELECT 
    IFNULL(SUM(CASE WHEN a.student IS NOT NULL THEN total END), 0)
FROM 
    tb_class a
WHERE 
    FIND_IN_SET(a.id, @ids)
    AND a.date >= '2023-02-01' AND a.date <= '2023-02-02';

2. 动态SQL拼接执行 #

通过拼接SQL语句，让数据库将@ids解析为多个数值参数：

SET @ids = '4094,8562,11144,3017,5815,11121,1957,4095,8563,11145,3018,5816,8527,11122,1959,4096,8564,3020,5817,8528,11123,1961,4097,8571,3021,6020,8535,11128,1962,5181,8572,3581,6021';
SET @sql = CONCAT('
    SELECT 
        IFNULL(SUM(CASE WHEN a.student IS NOT NULL THEN total END), 0)
    FROM 
        tb_class a
    WHERE 
        a.id IN (', @ids, ')
        AND a.date >= ''2023-02-01'' AND a.date <= ''2023-02-02''
');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

3. 直接使用子查询（推荐） #

如果@ids是通过子查询的GROUP_CONCAT获取的，完全可以跳过字符串拼接，直接将子查询放在IN条件中，既避免类型问题，又更高效：

SELECT 
    IFNULL(SUM(CASE WHEN a.student IS NOT NULL THEN total END), 0)
FROM 
    tb_class a
WHERE 
    a.id IN (SELECT id FROM your_source_table WHERE your_condition) -- 替换为原GROUP_CONCAT对应的子查询
    AND a.date >= '2023-02-01' AND a.date <= '2023-02-02';

内容的提问来源于stack exchange，提问作者Danny Choi