无法在 MySQL 中创建预准备语句

发布于 2024-11-08 13:34:36 字数 583 浏览 0 评论 0原文

我正在尝试在 MySQL 中创建一个接受单个参数的准备好的语句。当我在命令行上尝试此操作时，出现语法错误。但是，当我准备好的语句中没有变量时，我可以很好地创建它们。下面是我在 MySQL 命令提示符处看到的内容的复制和粘贴：

mysql> PREPARE state_name FROM "select * from ? limit 1";

ERROR 1064 (42000): You have an error in your SQL syntax;检查与您的 MySQL 服务器版本相对应的手册，了解在 '? 附近使用的正确语法。 limit 1' at line 1

mysql> PREPARE state_name FROM "select * from documents limit 1";

Query OK, 0 rows affected (0.00 sec)
Statement prepared

我使用的MySQL版本是5.0.77-log。

是否存在我没有看到的语法错误？为了让准备好的语句发挥作用，我是否必须设置任何配置参数？

原文

I'm trying to create a prepared statement in MySQL that takes in a single parameter. When I try this on the command line I get a syntax error. However, when there are no variables in my prepared statement, I am able to create them fine. Below is a copy and paste of what I am seeing at the MySQL command prompt:

mysql> PREPARE state_name FROM "select * from ? limit 1";

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? limit 1' at line 1

mysql> PREPARE state_name FROM "select * from documents limit 1";

Query OK, 0 rows affected (0.00 sec)
Statement prepared

The version of MySQL I'm using is 5.0.77-log.

Is there a syntax error I'm not seeing? And are there any config parameters I have to set in order to get prepared statements to work?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

贩梦商人 2024-11-15 13:34:36

恐怕您不能将表名作为准备好的语句参数传递，也不能传递列进行分组或排序。您唯一可以参数化的是 where 子句或更新中的字段：

PREPARE state_name FROM "select * from documents where id = ?";

PREPARE state_name FROM "update documents set field = ? where id = ?";

You can't pass in table names as prepared statement parameters, I'm afraid, neither can you pass columns to group or sort by. The only thing you can parametrize are fields in where clauses or for updates:

PREPARE state_name FROM "select * from documents where id = ?";

PREPARE state_name FROM "update documents set field = ? where id = ?";

回复收藏 0 原文

哆兒滾 2024-11-15 13:34:36

如果您想创建一个准备语句并将其用于多个表，您实际上可以为每个表创建一个变量，然后使用该变量创建语句，如 MySQL 手册解释一下：

mysql> USE test;
mysql> CREATE TABLE t1 (a INT NOT NULL);
mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);

mysql> SET @table = 't1';
mysql> SET @s = CONCAT('SELECT * FROM ', @table);

mysql> PREPARE stmt3 FROM @s;
mysql> EXECUTE stmt3;
+----+
| a  |
+----+
|  4 |
|  8 |
| 11 |
| 32 |
| 80 |
+----+

mysql> DEALLOCATE PREPARE stmt3;

If you want to create a single prepare statement and use it for more than one table, you cand actually create a variable for every table, and then create the statement using this variable, as the MySQL manual explain it :

mysql> USE test;
mysql> CREATE TABLE t1 (a INT NOT NULL);
mysql> INSERT INTO t1 VALUES (4), (8), (11), (32), (80);

mysql> SET @table = 't1';
mysql> SET @s = CONCAT('SELECT * FROM ', @table);

mysql> PREPARE stmt3 FROM @s;
mysql> EXECUTE stmt3;
+----+
| a  |
+----+
|  4 |
|  8 |
| 11 |
| 32 |
| 80 |
+----+

mysql> DEALLOCATE PREPARE stmt3;

回复收藏 0 原文

~没有更多了~