mysql - 如何查看准备好的语句或者我应该放弃动态表名称吗？

Question

我正在更新一些几年前有人编写的 PHP/MySQL 报告代码。单个 PHP 页面中有超过 50 条 SQL 语句。其中一些创建临时表，所有这些都引用至少一个动态表(例如 Report_201701)，这在 PHP 中使用变量很简单。

但是当我将所有这些语句调整到 MySQL Workbench 进行故障排除和增强时，这会导致问题，因为我无法查看准备好的语句的输出，因为我使用变量作为动态表名称。

我不熟悉动态 SQL。有什么方法可以查看/输出查询结果吗？ According to this post看来我不能。

一些语句返回单个值，我可以通过分配给这样的变量来查看该值:

set @sqlQuery = 
    Concat(
        'SELECT 1 as "A"
        into @sqlOut;'
    );
    #$opentickets = mysql_result($sqlQuery, 0);
PREPARE stmt1 from @sqlQuery;
EXECUTE stmt1;
deallocate prepare stmt1;
select @sqlOut;

# @sqlOut
-----------
1

但是，如果我尝试返回多列或多行的结果，则会收到如下错误:

set @sqlQuery = 
    Concat(
        'SELECT 1 as "A", 2 as "B", 3 as "C"
        into @sqlOut;'
    );
    #$opentickets = mysql_result($sqlQuery, 0);
PREPARE stmt1 from @sqlQuery;
EXECUTE stmt1;
deallocate prepare stmt1;
select @sqlOut;

Error Code: 1222. The used SELECT statements have a different number of columns


set @sqlQuery = 
    Concat(
        'SELECT 1 as "A"
        UNION SELECT 2 as "A"
        UNION SELECT 3 as "A"
        into @sqlOut;'
    );
    #$opentickets = mysql_result($sqlQuery, 0);
PREPARE stmt1 from @sqlQuery;
EXECUTE stmt1;
deallocate prepare stmt1;
select @sqlOut;

Error Code: 1172. Result consisted of more than one row

我最好的方法是什么？现在将动态表名称替换为静态名称？ (我正在考虑永久这样做，因为在这种情况下临时表不需要具有指示日期的名称。)

Answer 1

嗯，我偶然发现 MySQL Workbench 确实输出准备好的 SELECT 语句。无需分配给变量。不确定这是否会对某人有帮助，或者是否应该删除它以说明显而易见的事情。

set @sqlQuery = 
    Concat(
        'SELECT 1 as "A", 2 as "B", 3 as "C"
        UNION SELECT 10 as "A", 20 as "B", 30 as "C"
        UNION SELECT 100 as "A", 200 as "B", 300 as "C";'
    );
PREPARE stmt1 from @sqlQuery;
EXECUTE stmt1;
deallocate prepare stmt1;

返回:

A       B       C
'1'     '2'     '3'
'10'    '20'    '30'
'100'   '200'   '300'