this is my query in MySQL on table tbl_T367
mysql> SELECT
oID,
xName
FROM
`tbl_T367`
WHERE
oID IN ('2')
AND xName IN ('T367');
+-----+-------+
| oID | xName |
+-----+-------+
| 2 | T367 |
+-----+-------+
1 row in set
我需要为上面的相同输出创建mysql存储过程
我的下面的代码
DELIMITER $$
DROP PROCEDURE IF EXISTS my_sqrt$$
CREATE PROCEDURE my_sqrt(xName char(4), oID INT (11))
BEGIN
DECLARE xNamenew CHAR (4);
DECLARE IDnew INT (11);
SET @xNamenew = xName;
SET @IDnew = oID;
SET @s = CONCAT('SELECT * FROM tbl_', @xNamenew,
' WHERE oID IN (' + @IDnew + ') AND xName IN (' + @xNamenew + ')');
PREPARE stmt FROM @s;
EXECUTE stmt;
END
DELIMITER ;
但是结果
Procedure execution failed
1146 - Table 'tbl_T3672' doesn't exist
为什么将第二个变量的值添加到表名？
mysql> SELECT
oID,
xName
FROM
`tbl_T3672`
WHERE
oID IN ('2')
AND xName IN ('T367');
1146 - Table 'tbl_t3672' doesn't exist
mysql>
该如何解决呢？
更新
SET @s = CONCAT('SELECT * FROM tbl_', @xNamenew,
' WHERE oID IN (' , @IDnew , ') AND xName IN (' , @xNamenew ,')');
PREPARE stmt FROM @s;
EXECUTE stmt;
Procedure execution failed
1054 - Unknown column 'T367' in 'where clause'
一些问题：
+is the addition operator thus your strings will be cast to numbers and added.Alvaro的答案向您展示了如何使用参数化查询来减少注入的机会。下面的过程仅出于教育目的，向您显示您所拥有的东西与达到正确目的的距离之间的区别。
您不需要一些变量。您可以接受输入并创建一个串联的字符串。由于您将发送“ 2”，因此它可能不会与单引号连接在一起。因此，您必须在串联中包括单引号，如下所示。
同样，这是出于教育目的。使用Alvaro的方法。