MySQL用存储过程创建100W的数据

 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 ;

 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 ;