mysql存储过程快速插入数据

创建测试数据库：
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

创建用户表
create table user(
`uid` bigint(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键用户ID',
`username` varchar(10) NOT NULL DEFAULT '' COMMENT '用户名称',
`password` varchar(10) NOT NULL DEFAULT '' COMMENT '用户密码',
`status` varchar(10) NOT NULL DEFAULT '' COMMENT '用户状态',
`addtime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
PRIMARY KEY(`uid`),
KEY `username_status_addtime` (`username`,`status`,`addtime`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除索引
DROP INDEX username_status_addtime ON user;

创建索引
ALTER TABLE user ADD INDEX username_status_addtime (username, status, addtime);

插入一条数据
insert into user (username, password, status, addtime) values ('1', '1', '1', now());

存储过程
DROP PROCEDURE IF EXISTS `proc_while`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_while`(IN n int)
BEGIN
    #Routine body goes here...
    DECLARE i int;
    DECLARE j int;
    DECLARE ausername VARCHAR(10);
    DECLARE apassword VARCHAR(10);
    DECLARE astatus VARCHAR(10);
    DECLARE asql Text;
    SET i = 0;
    WHILE i < n DO
            SET j = 0;
            SET asql = 'insert into user (username, password, status, addtime) VALUES ';
            WHILE j < 1000 DO
                set j = j + 1;
                set ausername = CONCAT(FLOOR(7 + (RAND() * 1*10000*10000)), '');
                set apassword = CONCAT(FLOOR(7 + (RAND() * 1*10000*10000)), '');
                set astatus = CONCAT(FLOOR(7 + (RAND() * 1*10000*10000)), '');
                SET asql = CONCAT(asql, '("', ausername, '","', apassword, '","', astatus, '","', now(), '"),');
            END WHILE;
            SET asql = SUBSTRING(asql, 1, LENGTH(asql)-1);
            SET asql = CONCAT(asql, ';');
            set @sql = asql;
            PREPARE s1 from @sql; 
            EXECUTE s1; 
            deallocate prepare s1;
            set i = i + 1;

    END WHILE;
END
;;
DELIMITER ;

调用存储过程
SET @type = 1000;
CALL proc_while(@type);

 

Macbook pro 运行结果：

mysql> SET @type = 100;

Query OK, 0 rows affected (0.00 sec)

 

mysql> CALL proc_while(@type);

Query OK, 0 rows affected (4.48 sec)

 

mysql> SET @type = 1000;

Query OK, 0 rows affected (0.00 sec)

 

mysql> CALL proc_while(@type);

Query OK, 0 rows affected (43.82 sec)

 

一百万条插入需要的时间是43.82秒