mysql快速插入100万测试数据

向数据库添加100W条测试数据，直接在普通表中添加速度太慢，可以使用内存表添加，然后将内存表数据复制到普通表。

创建表

内存表

DROP TABLE IF EXISTS test_memory;
CREATE TABLE test_memory (
id INT (11) NOT NULL AUTO_INCREMENT,
item1 VARCHAR (20) NOT NULL,
item2 VARCHAR (100) NOT NULL,
PRIMARY KEY (id) USING HASH
) ENGINE = MEMORY AUTO_INCREMENT = 1 CHARACTER
SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Fixed STORAGE MEMORY;

普通表

DROP TABLE IF EXISTS test;
CREATE TABLE test (
id INT (11) NOT NULL AUTO_INCREMENT,
item1 VARCHAR (20) NOT NULL,
item2 VARCHAR (100) NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB AUTO_INCREMENT = 1 CHARACTER
SET = utf8;

编写函数

CREATE FUNCTION str_rand(n INT) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE char_str VARCHAR(255) DEFAULT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’;
DECLARE return_str VARCHAR(255) DEFAULT ‘’;
WHILE i < n DO
SET return_str = CONCAT(return_str,substring(char_str, FLOOR(1 + RAND()*62), 1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END

编写存储过程

CREATE PROCEDURE add_test_data(IN n int)
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < n DO
INSERT INTO test_memory(item1,item2) VALUES (str_rand(8),MD5(‘123456’));
SET i = i + 1;
END WHILE;
END

调用存储过程

CALL add_data(1000000);

调用过程可能报错,The table ‘test_memory’ is full。
解决办法：
查看内存表大小相关参数
show VARIABLES like ‘%max_heap_table_size%’;
查看临时表大小控制参数
show VARIABLES like ‘%tmp_table_size%’;

1）. 设置新的参数，这样设置的参数重启就失效了，
mysql> set global max_heap_table_size=1048576000
mysql> set global tmp_table_size=1048576000

执行完差不多3分钟。

将内存表数据复制到普通表

INSERT INTO test SELECT * FROM test_memory;
这个过程较快，差不多20秒内。