mysql 存储过程多次插入只有一次成功，请问这是什么原因？

Question

今天碰到一个奇葩的问题，同样的批量插入，一个可以批量插入成功，一个却不能插入成功，请大家看看。

以下是伪代码

-- 存储过程A (负责插入SQL)(有参数，这里就不写了，主要用于组装 SQL 并执行)
CREATE PROCEDURE `A`()
  BEGIN
    REPLACE INTO `table` (`num`) 
      SELECT SUM(`num`) AS `num`
      FROM `table`;
  END;

-- 存储过程B (负责循环处理参数，传递给 A)
CREATE PROCEDURE `B`()
  BEGIN
    WHILE 100 DO
      CALL `A`;
    END WHILE;
  END;

上面的两个存储过程就是我的代码逻辑，每次 REPLACE INTO ... SELECT ... 大概需要 0.2 秒的时间。
当我调用 CALL `B` 的时候，只能第一条插入成功，其他的均无插入，也没有报错。
生成的 SQL 语句是无错误的，因为打印的语句可以手动批量插入。

更奇葩的是，，我有一个同样的功能的其他 REPLACE INTO ... SELECT ... (相对执行时间短,0.01秒级)，却可以批量插入成功。

这个是不是锁导致的呢，我用的 InnoDB 存储引擎。

---

可能有些情况我说的不太明白：

• 上面的代码是伪代码，循环结构和生成执行 SQL 均无问题

• 这里主要讨论三个问题：

  • 是不是行锁导致的 (按说不应该)
  • 同样的表，同样的查询结构，为何执行时间短的可以插入(0.001级别)，执行时间长的不能插入(0.1级别)
  • 执行过程中调用执行过程，是同步执行还是异步执行 (即是否等 CALL 执行完毕后在走流程，还是不等执行完毕，就走下一个循环流程)

Answer 1

我想到了一个原因，正在测试，如果 CALL 是异步调用，而我 SQL 执行是这样的：

      SET @sql = $sql;
      PREPARE STMT FROM @sql;
      EXECUTE STMT;
      DEALLOCATE PREPARE STMT;

@sql 会相互覆盖。看看测试结论吧。

现在代码是这样的：

CREATE PROCEDURE `A`()
  BEGIN
    SET $sql = 'REPLACE INTO `table` (`num`) 
      SELECT SUM(`num`) AS `num`
      FROM `table`;';
      SET @sql = $sql;
    PREPARE STMT FROM @sql;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;
  END;

CREATE PROCEDURE `B`()
  BEGIN
    WHILE 100 DO
      CALL `A`;
    END WHILE;
  END;

Answer 2

你没有说明表的主键和唯一键, 猜测id是主键, 但replace into 代入的id始终是0, 所以最终数据库里只能有一条记录, 如果id 是自增长的, 试试设成null, 或改用insert into, 而不是replace into.

EDITED

我试了下, 按你给的存储过程会陷入死循环, 因为while 100一直为真.

可以试试改成这样:

delimiter $$

CREATE PROCEDURE `A`()
  BEGIN
    REPLACE INTO `sumtable` (`id`,`mysum`) 
      SELECT 0 AS `id`,SUM(`mysum`) AS `mysum`
      FROM `sumtable`;
  END;
$$

-- 存储过程B (负责循环处理参数，传递给 A)
CREATE PROCEDURE `B`()
  BEGIN
    DECLARE v1 INT DEFAULT 100;
    WHILE v1>0 DO
      CALL `A`;
    SET v1 = v1 - 1;
    END WHILE;
  END;
$$

运行

call B()

会得到100条记录.