gpt4 book ai didi

mysql - 如何从准备好的语句中获取标量结果?

转载 作者:可可西里 更新时间:2023-11-01 06:32:57 27 4
gpt4 key购买 nike

是否可以将准备好的语句的结果设置为变量?我正在尝试创建以下存储过程,但它失败了:

ERROR 1064 (42000) at line 31: 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 'stmt USING @m, @c, @a;

DROP PROCEDURE IF EXISTS deleteAction;

DELIMITER $$
CREATE PROCEDURE deleteAction(
IN modul CHAR(64),
IN controller CHAR(64),
IN actn CHAR(64))

MODIFIES SQL DATA

BEGIN

PREPARE stmt FROM 'SELECT id
FROM actions
WHERE `module` = ?
AND `controller` = ?
AND `action` = ?';

SET @m = modul;
SET @c = controller;
SET @a = actn;

SET @i = EXECUTE stmt USING @m, @c, @a;

DEALLOCATE PREPARE stmt;

DELETE FROM acl WHERE action_id = @i;
DELETE FROM actions WHERE id = @i;

END
$$
DELIMITER ;

最佳答案

这可能看起来很奇怪,但您可以直接在准备好的语句字符串中为变量赋值:

PREPARE stmt FROM 'SELECT @i := id FROM ...';

-- ...

EXECUTE stmt USING @m, @c, @a;

-- @i will hold the id returned from your query.

测试用例:

CREATE TABLE actions (id int, a int);

INSERT INTO actions VALUES (1, 100);
INSERT INTO actions VALUES (2, 200);
INSERT INTO actions VALUES (3, 300);
INSERT INTO actions VALUES (4, 400);
INSERT INTO actions VALUES (5, 500);

DELIMITER $$
CREATE PROCEDURE myProc(
IN p int
)

MODIFIES SQL DATA

BEGIN

PREPARE stmt FROM 'SELECT @i := id FROM actions WHERE `a` = ?';

SET @a = p;

EXECUTE stmt USING @a;

SELECT @i AS result;

DEALLOCATE PREPARE stmt;

END
$$
DELIMITER ;

结果:

CALL myProc(400);

+---------+
| result |
+---------+
| 4 |
+---------+
1 row in set (0.00 sec)

关于mysql - 如何从准备好的语句中获取标量结果?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/3992454/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com