调用存储过程后MySQL存储过程的OUT参数为空 [英] OUT parameter of MySQL stored procedure is null after calling stored procedure

查看:160
本文介绍了调用存储过程后MySQL存储过程的OUT参数为空的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我创建了一个存储过程来返回我作为IN"参数传入的任何表的行数的值,并将该行数输出到 OUT 参数

I've created a stored procedure to return a value for row count of any table I pass in as an "IN" parameter, and output that rowcount to an OUT parameter

PROCEDURE `GetCount`(in tblname varchar(255), out rowcount int)
BEGIN
    SET @sql_text1 = concat('SELECT COUNT(*) FROM ',tblname);
    SET @sql_text2 = concat(@sql_text1,' INTO ');
    SET @sql_final = concat(@sql_text2,  rowcount);

    PREPARE stmt1 FROM @sql_text1;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;

END

当我在 MySQL 工作台中打开一个查询窗口并执行以下操作时:

when I open a query window in MySQL workbench and do the following:

set @tablename = 'my_table_name;
set @cnt = -9998;
call GetCount(@tablename,@cnt);
SELECT @cnt;

@cnt 的值为 NULL.

the value of @cnt is NULL.

是我测试存储过程的方法不正确,还是存储过程有问题?

Is my method of testing the stored proc incorrect, or is the stored proc to blame?

推荐答案

你的测试方法是有效的,但你在过程中犯了三个错误.

Your test methodology is valid, but you have made three errors in the procedure.

错误 #1 您为准备好的语句使用了错误的变量.

Error #1 you are using the wrong variable for the prepared statement.

PREPARE stmt1 FROM @sql_text1;

这应该是...

PREPARE stmt1 FROM @sql_final;

错误 #2 这不符合您的意图:

Error #2 This doesn't do what you intend:

SET @sql_final = concat(@sql_text2,  rowcount);

这将@sql_text2 的值与 rowcount 的值连接起来.由于 rowcount 是一个 out 参数,所以此时它为空.如果 CONCAT() 的任何参数为 null,则结果也为 null,因此您实际上是将 @sql_final 设置为 null.如果不是错误 #1,PREPARE 或随后的 EXECUTE 将抛出一个关于 的错误......在第 1 行使用近 NULL 的正确语法.

This concatenates the value of @sql_text2 with the value of rowcount. Since rowcount is an out parameter, it is null at this point. If any argument to CONCAT() is null, then the result is also null, so you are actually setting @sql_final to null. If not for error #1, either the PREPARE or subsequent EXECUTE would have thrown an error about ...the right syntax to use near NULL at line 1.

这是您真正想要的,文字字符串rowcount":

Here's what you actually intended, the literal string 'rowcount':

SET @sql_final = concat(@sql_text2,  'rowcount');

...但这也会失败,因为 rowcount 是一个程序变量.准备好的语句在会话范围内运行,而不是程序范围,因此程序变量在准备好的语句中超出范围.

...but that would also fail, because rowcount is a program variable. Prepared statements run with session scope, not program scope, so program variables are out of scope in a prepared statement.

修复要求您使用具有会话作用域的用户定义变量,然后将其复制到程序变量中,如@nbk 在他们的回答中所示.

The fix requires you to use a user-defined variable, which has session scope, and then copy it into the program variable, as @nbk illustrated in their answer.

SET @sql_final = concat(@sql_text2,  '@rowcount');
PREPARE stmt1 FROM @sql_text1;
EXECUTE stmt1;
SET rowcount = @rowcount;

请注意,像 rowcount 这样的程序变量和像 @rowcount 这样的用户定义变量来自完全不同的命名空间,因此名称不需要相同,也不需要名称需要不同.

Note that program variables like rowcount and user-defined variables like @rowcount are from completely different namespaces, so there's no need for the names to be the same and no need for the names to be different.

错误 #3 并不是严格意义上的错误,因为它不会阻止您的代码工作,但这里有一个关于潜在危险做法的说明.

Error #3 is not strictly an error, in the sense that it isn't stopping your code from working, but here's a note about a potentially dangerous practice.

您接受一个表名作为输入,这会打开一个名为 SQL 注入的安全漏洞,恶意输入可能会导致意外/未经授权的结果.即使可以论证该输入来自可信来源,该论证也被视为最佳实践问题,因为未来的变化可能会使该假设无效.花时间学习这样做是值得的,并且始终如一地这样做,让它成为您的第二天性.

You are accepting a table name as input, which opens up a security vulnerability called SQL Injection, where malicious input can cause unexpected/unauthorized results. Even if the argument can be made that this input comes from a trusted source, that argument is disregarded as a matter of best practice, because future changes could invalidate that assumption. It is worth your time to learn to do this, and do it consistently so that it becomes second-nature to you.

您可以安全地转义 MySQL 中的表名、列名或其他对象标识符,方法是将任何嵌入的反引号替换为双反引号,然后在每一端添加一个反引号.

You can safely escape a table name, column name, or other object identifier in MySQL by replacing any embedded backtick with a double backtick, then prepending and appending a single backtick on each end.

您可以在程序顶部执行此操作...

You can do this at the top of the procedure...

SET tblname = CONCAT('`',REPLACE(tblname,'`','``'),'`');

...或内联...

SET @sql_text1 = concat('SELECT COUNT(*) FROM ',CONCAT('`',REPLACE(tblname,'`','``'),'`'));

...但当然不能两者兼而有之.在第二个例子中,嵌套的 CONCAT() 不是绝对必要的,所以这也可以,但意图不太明显:

...but of course not both. In the second example, the nested CONCAT() isn't strictly necessary, so this would also work, but the intention is less obvious:

SET @sql_text1 = concat('SELECT COUNT(*) FROM ','`',REPLACE(tblname,'`','``'),'`');

这篇关于调用存储过程后MySQL存储过程的OUT参数为空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆