时间戳

首页 > 解决方案 > 将Mysql中的静态查询转换为动态

mysql - 将Mysql中的静态查询转换为动态

问题描述

我在理解 Mysql 中的动态查询时遇到了一些问题。我需要将我的示例查询从静态转换为动态。在示例中，我有 2 个模式，并且我已经知道名称。但是如果我不知道模式的名称和编号，我需要创建一个可以工作的动态查询。我能得到关于如何做或我需要学习什么的提示吗？

SELECT MIN(tmp.DATE47) as DATE47,
   MIN(tmp.HOUR47) as HOUR47,
   MIN(tmp.DATE49) as DATE49,
   MIN(tmp.HOUR49) as HOUR49

FROM (SELECT DATEIN AS DATE47, HOURIN AS HOUR47, NULL AS DATE49, NULL AS HOUR49
 FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
     UNION ALL
 SELECT NULL AS DATE47, NULL AS HOUR47, HOURIN AS DATE49, HOURIN AS HOUR49
 FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1)tmp

标签： mysqlsql

解决方案

关于准备好的语句的快速示例：

SET @statement = 'SELECT MIN(tmp.DATE47) as DATE47,
   MIN(tmp.HOUR47) as HOUR47,
   MIN(tmp.DATE49) as DATE49,
   MIN(tmp.HOUR49) as HOUR49

FROM (SELECT DATEIN AS DATE47, HOURIN AS HOUR47, NULL AS DATE49, NULL AS HOUR49
 FROM `schema1`.`table` WHERE STATE = 17 AND LEVEL = 1
     UNION ALL
 SELECT NULL AS DATE47, NULL AS HOUR47, HOURIN AS DATE49, HOURIN AS HOUR49
 FROM `schema2`.`table` WHERE STATE = 17 AND LEVEL = 1)tmp;'

PREPARE stmt FROM @statement;

现在您可以使用此语句来查询schema1和schema2：

EXECUTE stmt;

如果您需要使用 ieschema3并schema2执行以下操作：

DEALLOCATE PREPARE stmt;
PREPARE stmt FROM REPLACE(@statement,'schema1','schema3');

并开始做：EXECUTE stmt;再次......

当然，这将在存储过程中更有效地处理。

推荐阅读