php - Mysql过程执行两次

Question

我有以下程序

DELIMITER $$
CREATE PROCEDURE providerDistributor(IN cronHash VARCHAR(255), IN lastProccess DATETIME)
BEGIN
    SET @cronHashVar = cronHash;
    SET @lastProccess = lastProccess;

    SET @targetiDS = (SELECT `id` AS targetiDS FROM `messages_temp` WHERE 
              `direction` = "3" 
              AND `reciever_id` = 0 
              AND `status`="1" 
              AND `has_providers` = "1" 
                  AND `reserved_bebs` = 0 
                  AND `maximum_cost` > 0 
                  AND `count_numbers`>0 
                  AND (`app_mobile_numbers`>0 OR `sms_mobile_numbers`>0) 
                  AND (`cron_hash`="" OR `cron_hash` IS NULL) ORDER BY `id` ASC LIMIT 1);

    SET @sql_text = concat('UPDATE `messages_temp` SET `cron_hash` = "',@cronHashVar,'",`last_process` = "',@lastProccess,'",`cron_name` = "providerDestributor" WHERE id = @targetiDS AND (cron_hash="" OR cron_hash IS NULL)');

    PREPARE stmt FROM @sql_text;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT SQL_NO_CACHE * FROM messages_temp WHERE cron_hash=@cronHashVar;


END$$
DELIMITER ;

这应该只更新一条记录，但一旦我添加最后一个查询，它总是更新两条记录

'SELECT SQL_NO_CACHE * FROM messages_temp WHERE cron_hash=@cronHashVar;

你能帮忙吗，我只需要更新一次记录。

Answer 1

我没有理由期望存储过程只更新一行。如果我假设 messages_temp(id) 是主键(或至少是唯一的)，那么该假设是正确的。您的查询更新多行的事实表明情况并非如此。

如果您只想更新一行，请将 limit 1 添加到 update 语句的末尾。