[MySQL] Mysql使用存储过程快速添加百万数据的示例代码

CREATE TABLE `t_sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '用户名',
  `password` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '密码 MD5存储',
  `register_time` timestamp NULL DEFAULT NULL COMMENT '注册时间',
  `type` int(1) DEFAULT NULL COMMENT '用户类型 1,2,3,4 随机',
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`) USING BTREE
)

• 用户名以常量和数字拼接
  
• 密码是MD5密码
  
• 注册时间是当前时间随机往前推几天
  
• type是取1-4随机范围值
  

create procedure salesAdd()
begin
 declare i int default 11;
   while i <= 4000000 do
     insert into blog.t_sales
     (`username`,`password`,`register_time`,type) values
     (concat("jack",i),MD5(concat("psswe",i)),from_unixtime(unix_timestamp(now()) - floor(rand() * 800000)),floor(1 + rand() * 4)); 
     set i = i + 1; 
   end while; 
end

call salesAdd()

create procedure salesAdd()
begin
 declare i int default 1;
 set autocommit = 0;   
   while i <= 4000000 do
     insert into blog.t_sales
     (`username`,`password`,`register_time`,type) values
     (concat("jack",i),MD5(concat("psswe",i)),from_unixtime(unix_timestamp(now()) - floor(rand() * 800000)),floor(1 + rand() * 4)); 
     set i = i + 1; 
   end while;
 set autocommit = 1;   
end

[SQL]
call salesAdd();
受影响的行: 0
时间: 387.691s