如何查看准备好的语句或者我应该放弃动态表名称？(How to view a prepared statement OR should I abandon dynamic table names?)

我正在更新某些人几年前写过的PHP / MySQL报告代码。在单个PHP页面中有超过50个SQL语句。它们中的一些创建临时表，它们全部引用至少一个动态表（例如Report_201701），这在使用变量的PHP中很简单。

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

我不熟悉动态SQL。有什么方法可以查看/输出查询结果吗？根据这篇文章，似乎我不能。

一些语句返回一个值，我可以通过赋值给一个变量来查看这个值：

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

我最好的办法是什么？现在用静态名称替换动态表名称？（我正在考虑这样做，因为在这种情况下临时表不需要有表明日期的名称。）

I'm working on updating some PHP/MySQL reporting code someone wrote a few years back. There are over 50 SQL statements in a single PHP page. Some of them create temporary tables, all of them refer to at least one dynamic table (e.g. Report_201701), which is simple in PHP using a variable.

But as I adapt all these statements to MySQL Workbench to troubleshoot and enhance, this is causing issues because I can't view the the output of the prepared statements since I'm using variables for the dynamic table name.

I'm not familiar with dynamic SQL. Is there some way I can view/output the query results? According to this post it appears I can't.

A few of the statements return a single value, which I have been able to view by assigning to a variable like this:

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

But if I try it with results that return multiple columns or multiple rows I get errors like this:

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

What's my best approach? Replace the dynamic table name with a static name for now? (I'm thinking of doing that permanently since temp tables in this situation don't need to have names that indicate date.)

最满意答案

那么，我偶然发现MySQL Workbench DOES输出准备好的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'

Well, I stumbled upon the finding that MySQL Workbench DOES output prepared SELECT statements. No need to assign to a variable. Not sure if this will help someone, or if it should be deleted for stating the obvious.

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;

Returns:

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

更多推荐