MySQL存储过程拼接多字符串至变量返回0的问题排查

编写存储过程拼接动态查询时，使用CONCAT()函数拼接字符串，执行结果始终返回0。存储过程代码如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `getSearchCounts`(IN fDate varchar(20), IN tDate varchar(20), IN byName varchar(255), IN byInn varchar(255), IN byTrademark varchar(255), IN byDist varchar(255), IN byDf varchar(255), IN byDFG varchar(255), IN byDt varchar(255), IN byDTG varchar(255), IN byCompany varchar(255), IN byMf varchar(255), IN byCountry varchar(255), IN isActive tinyint(1), IN isDeleted tinyint(1))
BEGIN
  DECLARE qWhere varchar(255);
  DECLARE lJoin varchar(255);
  DECLARE gBy varchar(255);
  set qWhere = '';
  SET lJoin = '';
  set gBy = ' dr.drug_id';
  IF (byName != '') THEN
    set qWhere := CONCAT(' AND dr.drug_id IN(', byName, ')');
  END IF;
  IF (byInn != '') THEN
    SET qWhere = CONCAT(@qWhere, ' AND d.di_id IN (', byInn, ')');
    SET gBy = CONCAT(gBy, ', d.di_id');
  END IF;
  IF (byTrademark != '') THEN
    SET qWhere = CONCAT(qWhere, ' AND d.trademark_id IN (', byTrademark, ')');
    set gBy = CONCAT(gBy, ', d.trademark_id');
  END IF;

  IF (byDist != '') THEN
    set qWhere = CONCAT(qWhere, ' AND dr.m40d_id IN (', byDist, ')');
    set gBy = CONCAT(gBy, ', dr.m40d_id');
  END IF;
  IF (byDf != '') THEN
    set qWhere = CONCAT(qWhere, ' AND d.df_id IN (', byDf, ')');
    set gBy = CONCAT(gBy, ', d.df_id');
  END IF;

  IF (byDFG != '') THEN
    set qWhere = CONCAT(qWhere, ' AND d.dfg_id IN (', byDFG, ')');
    set gBy = CONCAT(gBy, ', d.dfg_id');
  END IF;

  IF (byDt != '') THEN
    set qWhere = CONCAT(qWhere, ' AND d.dt_id IN (', byDt, ')');
    set gBy = CONCAT(gBy, ', d.dt_id');
  END IF;

  IF (byDTG != '') THEN
    set qWhere = CONCAT(qWhere, ' AND d.dtg_id IN (', byDTG, ')');
    set gBy = CONCAT(gBy, ', d.dtg_id');
  END IF;

  IF (byMf != '') THEN
    set qWhere = CONCAT(qWhere, ' AND dr.mf_id IN (', byMf, ')');
    set gBy = CONCAT(gBy, ', dr.mf_id');
  END IF;

  IF (byCompany != '') THEN
    set qWhere = CONCAT(qWhere, ' AND dr.sc_id IN (', byCompany, ')');
    set gBy = CONCAT(gBy, ', dr.sc_id');
  END IF;
  IF (byCountry != '') THEN
    SET lJoin = ' LEFT JOIN manufacturers as mf ON dr.mf_id = mf.id ';
    set qWhere = CONCAT(qWhere, ' AND mf.country_id IN (', byDist, ')');
    set gBy = CONCAT(gBy, ', mf.country_id');
  END IF;

  SET @q = CONCAT('SELECT COUNT(resData) AS counts FROM (SELECT dr.drug_id AS resData FROM drug_reports dr 
  LEFT JOIN drugs as d ON dr.drug_id = d.id ', lJoin,
  ' WHERE dr.mode_40_date BETWEEN STR_TO_DATE("', fDate, '", "%m.%d.%Y") AND STR_TO_DATE("', tDate, '", "%m.%d.%Y") AND dr.is_active = ', isActive, ' AND dr.is_deleted = ', isDeleted, qWhere,' GROUP BY ', gBy, ' ) AS dd');
  PREPARE stmt FROM @q;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END

执行后查看生成的SQL语句如下：

"SELECT COUNT(resData) AS counts FROM (SELECT dr.drug_id AS resData FROM drug_reports dr 
  LEFT JOIN drugs as d ON dr.drug_id = d.id  LEFT JOIN manufacturers as mf ON dr.mf_id = mf.id  WHERE dr.mode_40_date BETWEEN STR_TO_DATE(""01.01.2018"", ""%m.%d.%Y"") AND STR_TO_DATE(""01.01.2023"", ""%m.%d.%Y"") AND dr.is_active = 1 AND dr.is_deleted = 0 AND dr.drug_id IN(4468,4469) AND d.di_id IN ('') AND d.trademark_id IN ('') AND dr.m40d_id IN ('') AND d.df_id IN ('') AND d.dfg_id IN ('') AND d.dt_id IN ('') AND d.dtg_id IN ('') AND dr.mf_id IN ('') AND dr.sc_id IN ('') AND mf.country_id IN ('') GROUP BY dr.drug_id, d.di_id, d.trademark_id, dr.m40d_id, d.df_id, d.dfg_id, d.dt_id, d.dtg_id, dr.mf_id, dr.sc_id, mf.country_id ) AS dd"

该查询始终返回0，尝试使用会话变量@var仅首次执行有效，后续均返回0，请问如何正确拼接多个字符串到单个变量中？

从生成的SQL和原代码可定位核心问题：

• 变量混用：byInn分支错误使用会话变量@qWhere而非局部变量qWhere，导致条件拼接中断，甚至生成无效内容。
• 空参数无效拼接：参数为空时仍拼接IN ('')，数据库无对应空值记录，导致查询结果为空。
• 参数传错：byCountry分支误用byDist参数，国家筛选条件完全错误。
• 变量长度不足：qWhere定义为varchar(255)，动态SQL拼接后易被截断，生成无效查询。
• 会话变量残留：@q是会话级变量，执行后未清空，后续调用会使用旧值导致结果异常。

修改后的存储过程代码如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `getSearchCounts`(IN fDate varchar(20), IN tDate varchar(20), IN byName varchar(255), IN byInn varchar(255), IN byTrademark varchar(255), IN byDist varchar(255), IN byDf varchar(255), IN byDFG varchar(255), IN byDt varchar(255), IN byDTG varchar(255), IN byCompany varchar(255), IN byMf varchar(255), IN byCountry varchar(255), IN isActive tinyint(1), IN isDeleted tinyint(1))
BEGIN
  -- 扩大变量类型避免截断
  DECLARE qWhere TEXT;
  DECLARE lJoin TEXT;
  DECLARE gBy TEXT;
  set qWhere = '';
  SET lJoin = '';
  set gBy = ' dr.drug_id';
  
  IF (byName IS NOT NULL AND byName != '') THEN
    set qWhere := CONCAT(qWhere, ' AND dr.drug_id IN(', byName, ')');
  END IF;
  
  -- 修正为局部变量qWhere
  IF (byInn IS NOT NULL AND byInn != '') THEN
    SET qWhere = CONCAT(qWhere, ' AND d.di_id IN (', byInn, ')');
    SET gBy = CONCAT(gBy, ', d.di_id');
  END IF;
  
  IF (byTrademark IS NOT NULL AND byTrademark != '') THEN
    SET qWhere = CONCAT(qWhere, ' AND d.trademark_id IN (', byTrademark, ')');
    set gBy = CONCAT(gBy, ', d.trademark_id');
  END IF;

  IF (byDist IS NOT NULL AND byDist != '') THEN
    set qWhere = CONCAT(qWhere, ' AND dr.m40d_id IN (', byDist, ')');
    set gBy = CONCAT(gBy, ', dr.m40d_id');
  END IF;
  
  IF (byDf IS NOT NULL AND byDf != '') THEN
    set qWhere = CONCAT(qWhere, ' AND d.df_id IN (', byDf, ')');
    set gBy = CONCAT(gBy, ', d.df_id');
  END IF;

  IF (byDFG IS NOT NULL AND byDFG != '') THEN
    set qWhere = CONCAT(qWhere, ' AND d.dfg_id IN (', byDFG, ')');
    set gBy = CONCAT(gBy, ', d.dfg_id');
  END IF;

  IF (byDt IS NOT NULL AND byDt != '') THEN
    set qWhere = CONCAT(qWhere, ' AND d.dt_id IN (', byDt, ')');
    set gBy = CONCAT(gBy, ', d.dt_id');
  END IF;

  IF (byDTG IS NOT NULL AND byDTG != '') THEN
    set qWhere = CONCAT(qWhere, ' AND d.dtg_id IN (', byDTG, ')');
    set gBy = CONCAT(gBy, ', d.dtg_id');
  END IF;

  IF (byMf IS NOT NULL AND byMf != '') THEN
    set qWhere = CONCAT(qWhere, ' AND dr.mf_id IN (', byMf, ')');
    set gBy = CONCAT(gBy, ', dr.mf_id');
  END IF;

  IF (byCompany IS NOT NULL AND byCompany != '') THEN
    set qWhere = CONCAT(qWhere, ' AND dr.sc_id IN (', byCompany, ')');
    set gBy = CONCAT(gBy, ', dr.sc_id');
  END IF;
  
  -- 修正参数为byCountry
  IF (byCountry IS NOT NULL AND byCountry != '') THEN
    SET lJoin = ' LEFT JOIN manufacturers as mf ON dr.mf_id = mf.id ';
    set qWhere = CONCAT(qWhere, ' AND mf.country_id IN (', byCountry, ')');
    set gBy = CONCAT(gBy, ', mf.country_id');
  END IF;

  SET @q = CONCAT('SELECT COUNT(resData) AS counts FROM (SELECT dr.drug_id AS resData FROM drug_reports dr 
  LEFT JOIN drugs as d ON dr.drug_id = d.id ', lJoin,
  ' WHERE dr.mode_40_date BETWEEN STR_TO_DATE("', fDate, '", "%m.%d.%Y") AND STR_TO_DATE("', tDate, '", "%m.%d.%Y") AND dr.is_active = ', isActive, ' AND dr.is_deleted = ', isDeleted, qWhere,' GROUP BY ', gBy, ' ) AS dd');
  
  PREPARE stmt FROM @q;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
  -- 清空会话变量避免残留
  SET @q = NULL;
END

• 修正变量混用：将@qWhere替换为局部变量qWhere，保证条件拼接的连续性。
• 过滤空参数：增加IS NOT NULL判断，仅参数非空时才拼接对应条件，避免生成IN ('')无效条件。
• 修正参数错误：byCountry分支中替换参数为byCountry，确保筛选逻辑正确。
• 扩大变量长度：将变量类型改为TEXT，避免动态SQL拼接被截断。
• 清空会话变量：执行后将@q设为NULL，避免后续调用受旧值影响。

内容的提问来源于stack exchange，提问作者Oktam Yaqubov