MySQL存储过程批量生成数据原创

# 模拟表结构

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='测试表'

1
2
3
4
5
6
7
8

# 批量插入语句

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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 循环修改语句

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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

上次更新: 8/28/2024

← MySQL的事务隔离级别 MySQL insert on duplicate key update,replace into , insert ignore的理解→