MySQL 存储过程插入的问题

发布时间: 2017/1/9 22:02:24
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

所以以下情况适用︰

...

CREATE TEMPORARY TABLE IF NOT EXISTS `smth_table` (
  `login` VARCHAR(20),
  `password` VARCHAR(20),
  `type` INT(11),
  `account_state` DECIMAL(12,4)
);

PREPARE Selection FROM
   "INSERT INTO `smth_table`
    (SELECT ta.`login`, ta.`password`, ta.`type`, ta.`account_state`
    FROM tableA ta
    INNER JOIN tableB tb ON tb.id_client = ta.id_client
    WHERE tb.id_lot = ? AND ta.`type` MOD 2 = 0
    AND ta.first_use = '0000-00-00 00:00:00'
    AND ta.account_state = 0
    LIMIT ?)";
SET @WHERE = var1;
SET @LIMIT = var2;
EXECUTE Selection USING @WHERE, @LIMIT;
DEALLOCATE PREPARE Selection;

DECLARE curs CURSOR FOR
  SELECT `password` FROM `smth_table`;

DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;


OPEN pin_curs;

get_pass: LOOP
  FETCH curs INTO pass;
  IF v_finished = 1 THEN
     LEAVE get_pass;
  END IF;

  UPDATE tableA ta INNER JOIN tableB tb
  ON tb.id_client = ta.id_client
  SET `type` = `type` | 1,
  `account_state` = `account_state` + 5
  WHERE tb.id_lot = var1
  AND `password` = pass;


END LOOP get_pass;

 CLOSE curs;

END

我的问题是︰ 为什么,当我运行该存储的过程时,会临时表填充更多然后限制吗?牢记,我设置的限制与在变量传递槽程序......它的 10,顺便说一句......,但在运行程序时它将插入临时表中更 100 行...我不明白为什么......当它应插入只有 10...

谁能帮?

解决方法 1:

解决了 !

问题转播这一事实,我不删掉后再次,创建表因此反复插入相同的值。

DROP TABLE IF EXISTS `smth_table`; 

这在创建它之前插入和查询的运行顺利:-)

希望它也能帮助别人 !
祝你好运
战神一号。

赞助商