MySQL快速生成大量数据的SQL--存储过程

一个快速插入大量数据的存储过程sql

用于其他表改吧改吧基本能用

-- 整个执行包裹在一个事务中，每次循环insert插入一条数据
drop PROCEDURE if exists batchInsert;
DELIMITER $$
create PROCEDURE batchInsert(n int)
begin
declare v int default 1;
declare randTitle varchar(255) default '';
declare randContent varchar(255) default '';
declare randStatus int default 0;
-- 模拟数据随便找的图
declare fixdImg varchar(255) default 'https://img2.woyaogexing.com/2022/05/11/13a96b447bb44741bf1231472c41fd43!400x400.jpeg';
SET autocommit=0;
while v <= n
do
set randTitle = concat('标题-',substr(md5(rand()), 1, 10));
set randContent = uuid();
set randStatus = floor(rand()*6);
 
insert into t_bigdata(`title`, `content`, `status`, `img_url`) values(randTitle, randContent, randStatus, fixdImg);
 
set v = v + 1;
end while;
SET autocommit=1;
end $$
DELIMITER ;

简单说明：

• DELIMITER $$ ：修改分隔符，默认为分号，存储过程内部出现的分号会被认为是一条sql去执行，而不能正常创建存储过程了，修改下最后再恢复
• declare ：声明局部变量，必须写在begin紧接着的后面，随用随定义会报错无法编译通过
• SET autocommit=0; ：开启事务

调用：

-- 参数为生成数据条数
call batchInsert(1000000);

表结构：

CREATE TABLE `t_bigdata` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL COMMENT '标题',
  `content` varchar(255) DEFAULT NULL COMMENT '内容',
  `status` int(11) DEFAULT NULL COMMENT '状态',
  `img_url` varchar(255) DEFAULT NULL COMMENT '图片',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_title` (`title`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4601019 DEFAULT CHARSET=utf8mb4 COMMENT='大数据表';

再一个拼接values方式批量插入的存储过程：

drop procedure if exists batchInsertVals;
delimiter $$
-- 拼接values方式批量插入
create procedure batchInsertVals(n int, m int)
begin
-- 全局计数器
declare cnter int default 1;
-- 局部计数器
declare lcnter int default 1;
declare rand_title varchar(255);
declare rand_content varchar(255);
declare rand_status int;
-- 模拟个图片
declare fixdImg varchar(255) default 'https://img2.woyaogexing.com/2022/05/11/13a96b447bb44741bf1231472c41fd43!400x400.jpeg';
 
set @pre_sql := "insert into t_bigdata(`title`, `content`, `status`, `img_url`) values";
set @t_sql := "";
 
SET autocommit=0;
while cnter <= n
do
  set lcnter = 1;
  set @t_sql := "";
 
  while lcnter<=m and cnter<=n 
  do
    set rand_title = concat('标题-',substr(replace(uuid(),'-',''), 1, 10));
    set rand_content = uuid();
    set rand_status = floor(rand()*6);
    if lcnter > 1
    then
      set @t_sql := concat(@t_sql, ',');
    end if;
 
    set @t_sql := concat(@t_sql, "('", rand_title, "',", "'", rand_content, "',", rand_status, ",'", fixdImg, "'", ')');
    set cnter = cnter+1;
    set lcnter = lcnter+1;
  end while;
  set @t_sql := concat(@pre_sql, @t_sql);
  -- 这里不用全局变量还执行不了。。。
  prepare stmt from @t_sql;
  execute stmt;
  deallocate prepare stmt;
 
end while;
SET autocommit=1;
end $$
delimiter ;

调用：

-- 参数1为生成数据条数，参数2为values拼接多少行
call batchInsertVals(1000000, 50);