版本说明

本文写于 2022-03。
存储过程语法未变，经 2026-07 复核仍适用。

模拟表结构

CREATE TABLE `sbtest`.`sbtest` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `money` decimal(16,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '金额',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
  KEY `idx_money` (`money`) COMMENT '金额'
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试表'

批量插入语句

use sbtest;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into `sbtest`.`cloudcanal_test` values(i,0.00,now(),now());
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();
drop procedure idata;

循环修改语句

use sbtest;
delimiter ;;
create procedure idata()
begin
  declare i int;  declare j int;
  set i=1;
  while(i<=100000)do --10万条数据
    set j=1;
            while(j<=500)do --更新500次，每次money+1
                UPDATE `sbtest`.`sbtest` set money = money+1 where id =i;
                set j=j+1;
            end while;    
  set i=i+1;
  end while;
end;;
delimiter ;
call idata();
drop procedure idata;