如题
1.100W条数据的插入,定义WHILE循环从1-100W,insert语句执行100W次,从晚上11点跑到第二天早上10点,共耗时11小时!!!
1 DELIMITER $$ 2 3 DROP PROCEDURE IF EXISTS `Havefun`.`create_100w_data` $$ 4 5 CREATE 6 /*[DEFINER = { user | CURRENT_USER }]*/ 7 PROCEDURE `Havefun`.`create_100w_data`() 8 /*LANGUAGE SQL 9 | [NOT] DETERMINISTIC 10 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } 11 | SQL SECURITY { DEFINER | INVOKER } 12 | COMMENT 'string'*/ 13 BEGIN 14 DECLARE i INT; 15 DROP TABLE IF EXISTS `Havefun`.`100w_data`; 16 CREATE TABLE `100w_data` ( 17 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主ID', 18 `name` CHAR(20) NOT NULL DEFAULT '' COMMENT '名字', 19 `status` INT(1) UNSIGNED NOT NULL DEFAULT '99' COMMENT '状态,1-审核通过 0-未通过 99-待审核', 20 `type` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '类型', 21 PRIMARY KEY (`id`) 22 ) ENGINE=INNODB DEFAULT CHARSET=utf8; 23 24 SET i=1; 25 WHILE (i<1000000) DO 26 INSERT INTO `100w_data`(`name`, `status`, `type`) VALUES(CONCAT('data_',i), '99', 'PROC'); 27 SET i=i+1; 28 END WHILE; 29 30 END$$ 31 32 DELIMITER ;
2.定义插入的开始点与结束点,这样可以多进程执行存储过程,提高速率。(也可以用PHP脚本来跑)
1 DELIMITER $$ 2 3 USE `Havefun`$$ 4 5 DROP PROCEDURE IF EXISTS `create_i_data`$$ 6 7 CREATE DEFINER=`root`@`%` PROCEDURE `create_i_data`(IN _start INT, IN _end INT) 8 BEGIN 9 DECLARE i INT; 10 DROP TABLE IF EXISTS `Havefun`.`amt_data`; 11 CREATE TABLE `amt_data` ( 12 `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主ID', 13 `name` CHAR(20) NOT NULL DEFAULT '' COMMENT '名字', 14 `status` INT(1) UNSIGNED NOT NULL DEFAULT '99' COMMENT '状态,1-审核通过 0-未通过 99-待审核', 15 `type` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '类型', 16 `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', 17 `update_time` INT(11) NOT NULL COMMENT '更新时间', 18 PRIMARY KEY (`id`) 19 ) ENGINE=INNODB DEFAULT CHARSET=utf8; 20 SET i=_start; 21 WHILE (i<_end) DO 22 INSERT INTO `amt_data`(`name`, `status`, `type`, `create_time`, `update_time`) VALUES(CONCAT('data_',i), '99', 'PROC',NOW(),UNIX_TIMESTAMP(NOW())); 23 SET i=i+1; 24 END WHILE; 25 END$$ 26 27 DELIMITER ;