通过 PHP 调用的 MySQL 存储过程无法正确执行 [英] Called MySQL Stored Procedure via PHP does not execute properly
问题描述
这个让我很困惑，我希望解决方案是你知道为什么这不起作用，不是吗?"回复类型.
This one has got me foxed, and I'm hoping the solution is a "you know why that isn't working, don't you?" type of reply.
这里是:通过 PHP，我调用了一个 MySQL 存储过程:
Here goes: Via PHP I am calling a MySQL stored procedure thus:
$sql = "CALL sp_test;";
$mysqli->query($sql);
没问题.我知道存储过程被执行.
No problem there. I know the Stored Procedure gets executed.
这是我为这个例子分割的存储过程，它仍然产生同样的问题.
Here's the Stored Procedure that I have striped for this example, and it is still producing the same problem.
DELIMITER \\
DROP PROCEDURE IF EXISTS sp_test\\
CREATE PROCEDURE sp_test()
DETERMINISTIC
BEGIN
DECLARE exit_loop TINYINT(1) DEFAULT 0;
DECLARE v_TableName VARCHAR(50); DECLARE v_ScanID INT(11); DECLARE v_ScanType TINYINT(4);
DECLARE v_TitleID INT(11); DECLARE v_VolumeNo INT(11); DECLARE v_IssueNo VARCHAR(10); DECLARE v_IssueDate INT(11); DECLARE v_FIDateUnknown TINYINT(4); DECLARE v_PureHavocScan TINYINT(4);
-- declare cursor for employee email
DECLARE storeroom_cursor CURSOR FOR SELECT TableName, ScanID, ScanType, FIDateUnknown, TitleID, VolumeNo, REPLACE(IssueNo, "\'", "") AS IssueNo, IssueDate FROM t_storeroom ts JOIN t_issueguide tig ON ts.TitleID = tig.ComicTitleID;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
OPEN storeroom_cursor;
get_purehavoc: LOOP
FETCH storeroom_cursor INTO v_TableName, v_ScanID, v_ScanType, v_FIDateUnknown, v_TitleID, v_VolumeNo, v_IssueNo, v_IssueDate;
IF exit_loop = TRUE THEN
LEAVE get_purehavoc;
END IF;
CASE v_ScanType
WHEN '1' AND v_FIDateUnknown = 0 THEN
SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND IssueDate = ", v_IssueDate, " AND DiggerExclusive = 1;");
WHEN '1' AND v_FIDateUnknown = 1 THEN
SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND DiggerExclusive = 1;");
-- WHEN '2' THEN
-- Do something
END CASE;
PREPARE statement FROM @sql_text;
EXECUTE statement;
-- Setting the PureHavocScan variable to enter into the PREPARE STATEMENT.
IF @RowCount = 0 THEN
SET @PureHavocScan = 1;
ELSE
SET @PureHavocScan = 0;
END IF;
SET @ScanID = v_ScanID;
SET @TableName = v_TableName;
-- SET @sql_text = concat("UPDATE t_storeroom SET PureHavocScan = ? WHERE TableName = ? AND ScanID = ? ");
SET @sql_text = concat("UPDATE t_storeroom SET PureHavocScan = ", @PureHavocScan, " WHERE TableName = '", @TableName, "' AND ScanID = ", @ScanID, ";");
PREPARE statement FROM @sql_text;
EXECUTE statement;
END LOOP get_purehavoc;
DEALLOCATE PREPARE stmt;
CLOSE storeroom_cursor;
END//
DELIMITER ;
当我在我的 MySQL 数据库管理工具(在我的例子中是 Navicat)中运行这个确切的存储过程时，存储过程按预期工作 100%，所有应该更新的行都会更新.
When I run this exact Stored Procedure within my MySQL database admin tool (Navicat in my case), the stored procedure works 100% as intended and ALL the rows that should get updated DO get updated.
但是，当我通过 PHP 运行这个确切的存储过程时，只有 1(有时 2)行得到更新.
However, when I run this exact Stored Procedure via PHP, ONLY 1 (sometimes 2) rows get updated.
以完全相同的方式调用的其他存储过程工作正常.就这一个.与失败的唯一区别是 MySQL 命令是一个 UPDATE，而不是正常工作的 SELECTS 和 INSERTS.
Other stored procedures that get called in exactly the same way work fine. It's just this one. The ONLY difference with the one that's failing, is that the MySQL command is an UPDATE, as opposed to the SELECTS and INSERTS that work fine.
我已经在这里呆了两天，已经用尽了所有我的"知识.因此，如果有人能让我摆脱痛苦，我将不胜感激.
I've been at this for two days now and have exhausted all 'my' knowledge. So if someone can put me out of my misery, I'd be mightily grateful.
推荐答案
这个CASE语句不正确:
CASE v_ScanType
WHEN '1' AND v_FIDateUnknown = 0 THEN
SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND IssueDate = ", v_IssueDate, " AND DiggerExclusive = 1;");
WHEN '1' AND v_FIDateUnknown = 1 THEN
SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND DiggerExclusive = 1;");
-- WHEN '2' THEN
-- Do something
END CASE;
CASE <表达式>当<条件>THEN ... 是 CASE WHEN ( 的快捷方式.因此，您的代码不是将 v_ScanType 与 '1' 或 '2' 进行比较，而是将其与 ('1' AND v_FIDateUnknown= 0).所以它只是将变量与 v_FIDateUnknown 比较的 TRUE 或 FALSE 结果进行比较.
CASE <expression> WHEN <condition> THEN ... is a shortcut for CASE WHEN (<expression>) = (<condition>) THEN .... So your code is not comparing v_ScanType to '1' or '2', it's comparing it to ('1' AND v_FIDateUnknown = 0). So it's just comparing the variable to the TRUE or FALSE result of the comparison with v_FIDateUnknown.
你需要在没有快捷方式的情况下编写它.
You need to write it without the shortcut.
CASE
WHEN v_ScanType = '1' AND v_FIDateUnknown = 0 THEN
SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND IssueDate = ", v_IssueDate, " AND DiggerExclusive = 1;");
WHEN v_ScanType = '1' AND v_FIDateUnknown = 1 THEN
SET @sql_text = CONCAT("SELECT @RowCount := COUNT(*) FROM ", v_TableName, " WHERE ComicTitleID = ", v_TitleID, " AND VolumeNo = ", v_VolumeNo, " AND IssueNo = '", v_IssueNo, "' AND DiggerExclusive = 1;");
-- WHEN v_ScanType = '2' THEN
-- Do something
END CASE;
我不知道为什么这只会在您从 PHP 调用该过程时导致它失败.也许这只是一个巧合.但是这样一个明显错误的程序很难推理.
I'm not sure why this would only cause it to fail when you call the procedure from PHP. Maybe it's just a coincidence. But it's hard to reason about a procedure with such a blatant error.
这篇关于通过 PHP 调用的 MySQL 存储过程无法正确执行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
