mysql 循环取数据

-- 示例：存储过程循环更新数据
DELIMITER //
CREATE PROCEDURE update_data()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE id INT;
  DECLARE cur CURSOR FOR SELECT id FROM your_table;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO id;
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- 执行更新操作
    UPDATE your_table SET status = 'updated' WHERE id = id;
  END LOOP;

  CLOSE cur;
END //
DELIMITER ;

-- 示例：分批处理数据
DELIMITER //
CREATE PROCEDURE batch_update_data()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE id INT;
  DECLARE batch_size INT DEFAULT 100;
  DECLARE cur CURSOR FOR SELECT id FROM your_table;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur;

  read_loop: LOOP
    FETCH cur INTO id;
    IF done THEN
      LEAVE read_loop;
    END IF;

    -- 开启事务
    START TRANSACTION;
    -- 执行更新操作
    UPDATE your_table SET status = 'updated' WHERE id = id;
    -- 提交事务
    COMMIT;

    -- 达到批量大小后，暂停一段时间
    IF id % batch_size = 0 THEN
      DO SLEEP(0.1);
    END IF;
  END LOOP;

  CLOSE cur;
END //
DELIMITER ;