mysql - 如何防止select语句返回输出

Question

在一个查询中，我有多行语句，如下所示:

set @query = '';
select @query := concat(@query, ' union (select \'',bi.param2,'\' as tableName, ',bi.param3,' id, ', bi.param4, ' text from ', bi.param2,' where ',if(TRIM(bi.param5) is null, '1=1', bi.param5),')')
from itmanagement.BasicInfo bi where bi.param2 != '';
set @query := concat(SUBSTR(@query, 7, LENGTH(@query)));
set @wholeQuery := concat('select pe.*, bi.title label
, (case when bi.param1 = \'textfield\' then pe.ElemValue else od.text end) text
 from PRC_PcdElements pe join BasicInfo bi on pe.ElemType = bi.InfoID join 
 (',@query,")
 od on bi.param2 = od.tableName and pe.ElemValue = od.id 
 where bi.TypeID = 3 and pe.ProcedureID = '2';");
prepare stmt1 from @wholeQuery ;
execute stmt1 ;

此查询返回两列。一个用于第一个 select 语句，它只是一个赋值语句，而 exec 语句的输出是所需的。

我想防止第一个 select 语句打印出一个表到输出或用更好的命令替换它(如果可用)。

Answer 1

您可以尝试以下解决方案，使用 SELECT ... INTO语法:

SET @query = '';
SELECT CONCAT(@query, ' UNION(SELECT \'',bi.param2,'\' AS tableName, ',bi.param3,' id, ', bi.param4, ' text FROM ', bi.param2,' WHERE ', IF(TRIM(bi.param5) IS NULL, '1=1', bi.param5),')') INTO @query
FROM itmanagement.BasicInfo bi WHERE bi.param2 != '' LIMIT 1;
SET @query := CONCAT(SUBSTR(@query, 7, LENGTH(@query)));
SET @wholeQuery := CONCAT('SELECT pe.*, bi.title label
 , (CASE WHEN bi.param1 = \'textfield\' THEN pe.ElemValue ELSE od.text END) text
 FROM PRC_PcdElements pe JOIN BasicInfo bi ON pe.ElemType = bi.InfoID JOIN 
 (',@query,")
 od ON bi.param2 = od.tableName AND pe.ElemValue = od.id 
WHERE bi.TypeID = 3 AND pe.ProcedureID = '2';");
PREPARE stmt1 FROM @wholeQuery;
EXECUTE stmt1;

您可以使用如下语句(无输出)设置 @query 变量:

SELECT column_name INTO @query FROM table_name WHERE condition = true LIMIT 1