MySQL使用存储过程生成一千万条数据-优快云博客

本文介绍了一个使用MySQL存储过程批量插入数据到数据库表的示例。该存储过程能够高效地插入大量数据，并提供了具体的SQL代码实现，有助于理解存储过程的工作原理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建表tb_1kw

id为自动增加

CREATE TABLE tb_1kw (
id serial,
uname varchar(50),
ucreatetime datetime,
age bool,
info double)
ENGINE=MYISAM
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT;

存储过程如下

delimiter $$
SET AUTOCOMMIT = 0$$
create procedure tb_1kw_pro()
begin
declare v_cnt decimal (10) default 0;
dd:loop
insert into tb_1kw values
(null,'用户1','2011-01-02 00:00:00',1,1.32),
(null,'用户2','2012-02-11 00:00:00',0,81.43),
(null,'用户3','2013-03-21 00:00:00',1,5.09),
(null,'用户4','2014-04-05 00:00:00',1,40.28),
(null,'用户5','2015-05-06 00:00:00',1,03.16),
(null,'用户6','2016-06-08 04:00:00',0,6.64),
(null,'用户7','2017-07-09 00:00:00',0,2.33),
(null,'用户8','2018-08-13 03:00:00',1,45.24),
(null,'用户9','2019-09-18 00:00:00',1,25.35),
(null,'用户0','2020-10-20 10:00:00',2,45.14);
commit;
set v_cnt = v_cnt + 10;
if v_cnt = 10000000 then leave dd;
end if;
end loop dd;
end;$$
delimiter;