我正在更新某些人几年前写过的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 ----------- 1But 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 rowWhat'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'更多推荐
发布评论