Mysql使用存儲(chǔ)過(guò)程快速添加百萬(wàn)數(shù)據(jù)的示例代碼

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存儲(chǔ)',
  `register_time` timestamp NULL DEFAULT NULL COMMENT '注冊(cè)時(shí)間',
  `type` int(1) DEFAULT NULL COMMENT '用戶類型 1,2,3,4 隨機(jī)',
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`) USING BTREE
)

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