mysql导入百万数据测试

1、生成思路
利用mysql内存表插入速度快的特点，先利用函数和存储过程在内存表中生成数据，然后再从内存表插入普通表中

-- 1创建内存表
CREATE TABLE vote_record_memory (
id INT (11) NOT NULL AUTO_INCREMENT,
user_id VARCHAR (20) NOT NULL,
vote_id INT (11) NOT NULL,
group_id INT (11) NOT NULL,
create_time datetime NOT NULL,
PRIMARY KEY (id),
KEY index_id (user_id) USING HASH
) ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

-- 2创建数据表
CREATE TABLE vote_record (
id INT (11) NOT NULL AUTO_INCREMENT,
user_id VARCHAR (20) NOT NULL,
vote_id INT (11) NOT NULL,
group_id INT (11) NOT NULL,
create_time datetime NOT NULL,
PRIMARY KEY (id),
KEY index_user_id (user_id) USING HASH
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

-- 3创建字符串随机函数
CREATE FUNCTION rand_string(n INT) RETURNS varchar(255)
BEGIN
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '' ;
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*62 ),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END

-- 4创建循环存储过程
CREATE PROCEDURE add_vote_memory(IN n int)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= n ) DO
INSERT into vote_record_memory (user_id,vote_id,group_id,create_time ) VALUES (rand_string(20),FLOOR(RAND() * 1000),FLOOR(RAND() * 100) ,now() );
set i=i+1;
END WHILE;
END

-- 5调用存储过程
CALL add_vote_memory(1000000)

-- 6将内存表数据复制到数据表
INSERT into vote_record SELECT * from vote_record_memory