mysql - 错误 1292 - 截断了不正确的 DOUBLE 值 :'70,40,77,28,23,72,82,38,29'

Question

当我像这样执行这个函数时:

这是我定义的函数:

CREATE FUNCTION kyf_getFormationComat(heroSdIds VARCHAR(128), uid VARCHAR(128))
RETURNS INT(11)
BEGIN
    DECLARE comat INT(11);
    SET comat = (SELECT SUM(a.comat) FROM ky_heroinfo a WHERE a.uid = uid AND a.staticdata_id in (heroSdIds));
    RETURN comat;
END

Answer 1

heroSdIds 是一个字符串，因此您不能在 SELECT 查询中使用 IN():

SELECT SUM(a.comat) FROM ky_heroinfo a 
    WHERE a.uid = uid AND a.staticdata_id in (heroSdIds)

状况:

a.staticdata_id in (heroSdIds)

实际上给你:

a.staticdata_id in ("70,40,77,28,23,72,82,38,29") 

请注意引号。它仍然是一个字符串，这里是一个单个值。但为了使其工作，IN() 需要它是用逗号分隔的多个值:

a.staticdata_id in (70,40,77,28,23,72,82,38,29) 

要使其工作，您可以尝试使用字符串函数 FIND_IN_SET()函数而不是 IN()。

尝试:

FIND_IN_SET(a.staticdata_id, heroSdIds)

所以整个查询将是:

SELECT SUM(a.comat) FROM ky_heroinfo a 
    WHERE a.uid = uid AND FIND_IN_SET(a.staticdata_id, heroSdIds)