MySQL：将过程参数传递给 EXECUTE USING 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1139717/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Passing procedure params to EXECUTE USING statement
提问by
This is MySQL 5.1.
这是 MySQL 5.1。
(Note: I realize there are better ways of doing this particular example, this is not my real code)
（注意：我意识到有更好的方法来做这个特定的例子，这不是我真正的代码）
Here is what I want to do: The below procedure gets created, but when I CALL it, I get "ERROR 1210 (HY000): Incorrect arguments to EXECUTE"
这是我想要做的：创建了下面的过程，但是当我调用它时，我得到“错误 1210（HY000）：EXECUTE 的参数不正确”
DELIMITER //
CREATE PROCEDURE get_users_by_state(IN state CHAR(2))
READS SQL DATA
BEGIN
SET @mystate = state;
SET @sql = CONCAT('SELECT * FROM test_table WHERE state = "?"');
PREPARE stmt FROM @sql;
EXECUTE stmt USING @mystate;
END;
//
CALL get_users_by_state('AA')//
ERROR 1210 (HY000): Incorrect arguments to EXECUTE
Is there a way to pass the procedure's parameters to the EXECUTE USING statement? Here is a version that does indeed work, but irks me:
有没有办法将过程的参数传递给 EXECUTE USING 语句？这是一个确实有效的版本，但让我感到恼火：
CREATE PROCEDURE get_users_by_state(IN state CHAR(2))
READS SQL DATA
BEGIN
SET @sql = CONCAT('SELECT * FROM test_table WHERE state = "', state, '"')
PREPARE stmt FROM @sql;
EXECUTE stmt;
END;
//
As a side-question, does MySQL have any facilities for escaping strings, like Postgres' quote_literal() and quote_ident()?
作为一个附带问题，MySQL 是否有任何用于转义字符串的工具，例如 Postgres 的 quote_literal() 和 quote_ident()？
For a point of reference, here's something somewhat equivalent for Postgres:
作为参考，这里有一些与 Postgres 相当的东西：
CREATE OR REPLACE FUNCTION get_info_by_state(character)
RETURNS SETOF ret_type AS
$BODY$
DECLARE
sql text;
BEGIN
sql := 'SELECT uid, some_data FROM test_table WHERE state = ' || quote_literal();
RETURN QUERY EXECUTE sql;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
Thanks!
谢谢！
采纳答案by ChssPly76
I don't think you need double quotes around the parameter holder.
我认为您不需要在参数持有者周围加上双引号。
UpdateHere, lest there be no misunderstanding:
在此更新，以免引起误解：
DELIMITER //
CREATE PROCEDURE get_users_by_state(IN state CHAR(2))
READS SQL DATA
BEGIN
SET @mystate = state;
SET @sql = CONCAT('SELECT * FROM test_table WHERE state = ?');
PREPARE stmt FROM @sql;
EXECUTE stmt USING @mystate;
END;
//
