需要向数据库添加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分钟左右。