mysql 插入百万条数据

利用mysql内存表插入速度快的特点，先存储过程在内存表中生成数据，然后再从内存表插入普通表中，MyISAM 插入速度快于 innodb；mysql 5.7

一.创建内存表

CREATE TABLE `user_temp` (
  `id` int(6) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(50) NOT NULL COMMENT '名称',
  `pwd` varchar(32) NOT NULL COMMENT '密码',
  `create_time` datetime DEFAULT NULL COMMENT '创建日期',
  PRIMARY KEY (`id`)
) ENGINE=MEMORY  DEFAULT CHARSET=utf8 COMMENT='测试表';

 

二.创建普通表

CREATE TABLE `user` (
  `id` int(6) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(50) NOT NULL COMMENT '名称',
  `pwd` varchar(32) NOT NULL COMMENT '密码',
  `create_time` datetime DEFAULT NULL COMMENT '创建日期',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='测试表';

 

三.创建一个函数，用于生成随机字符串，show function status;  查看函数 

DELIMITER $$
DROP FUNCTION IF EXISTS rand_str;
-- 如果存在就删除
create FUNCTION rand_str(strlen SMALLINT ) 
-- 创建函数名 rand_str  参数为返回的长度
RETURNS VARCHAR(255)
-- 返回值
BEGIN
    DECLARE randStr VARCHAR(255) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';
--     声明的字符串
    DECLARE i SMALLINT DEFAULT 0;
-- 声明 i 循环变量
    DECLARE resultStr VARCHAR(255) DEFAULT '';
-- 声明返回变量
    WHILE i<strlen DO
        SET resultStr=CONCAT(SUBSTR(randStr,FLOOR(RAND()*LENGTH(randStr))+1,1),resultStr);
        SET i=i+1;
    END WHILE;
    RETURN resultStr;
END $$
DELIMITER ;

调用函数
select rand_str(10)

四.创建存储过程，show procedure status; 查看存储过程 DROP PROCEDURE add_user; 删除

DROP PROCEDURE IF EXISTS `add_user`;
DELIMITER $$
CREATE PROCEDURE `add_user`(IN n int)
BEGIN
    DECLARE i int unsigned DEFAULT 0;
    WHILE i < n DO
        INSERT INTO `user_temp`(name,pwd,create_time) VALUES (rand_str(15),MD5(123456),NOW());
        SET i = i+1;
    END WHILE;
END $$
DELIMITER ;

 

五.调用存储过程

CALL add_user(1000000);

由于mysql 默认内存允许容量小，会出现如下报错

   

windows 操作系统 找到 my.ini 修改  ，不同的集成环境，配置会有差别，修改max_heap_table_size = 1024M，或者添加进去即可，重启mysql，根据电脑性能不能所花时间不一样，thinkpad 8G 内存 i5 处理器  6分钟

六.插入到普通表中

INSERT into user SELECT * from user_temp;

DROP PROCEDURE add_user;