mysql 新增一百万条数据_MySQL 快速添加百万条数据

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

创建表

# 内存表

DROP TABLE IF EXISTS `test_memory`;

CREATE TABLE `test_page_memory` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`create_time` datetime(0) NULL DEFAULT NULL,

`update_time` datetime(0) NULL DEFAULT NULL,

PRIMARY KEY (`id`) USING HASH

) ENGINE = MEMORY AUTO_INCREMENT = 1000001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Fixed STORAGE MEMORY;

SET FOREIGN_KEY_CHECKS = 1;

# 普通表

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test_page` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`password` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`create_time` datetime(0) NULL DEFAULT NULL,

`update_time` datetime(0) NULL DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 1000001 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

编写函数

CREATE DEFINER=`root`@`localhost` FUNCTION `rand_str`(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 DEFINER=`root`@`localhost` PROCEDURE `add_data`(IN `n` int)

BEGIN

DECLARE i INT DEFAULT 0;

WHILE i < n DO

INSERT INTO test_memory(username,`password`,create_time,update_time) VALUES (rand_str(8),MD5('123456'),NOW(),NOW());

SET i = i + 1;

END WHILE;

END

执行

# 调用存储过程

CALL add_data(1000000);

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

INSERT INTO test SELECT * FROM test_memory;

结果

可以看到添加数据一共只花了6分钟左右。