1DELIMITER $$
 2
 3USE `test`$$
 4
 5DROP PROCEDURE IF EXISTS `add_user_batch`$$
 6
 7CREATE DEFINER=`root`@`%` PROCEDURE `add_user_batch`(IN COUNT INT)
 8BEGIN
 9    DECLARE i INT;
10    DECLARE t_name VARCHAR(8);
11    DECLARE t_tag VARCHAR(20);
12    DECLARE t_age INT(2);
13    DECLARE t_sql_template VARCHAR(100);
14    DECLARE t_sql TEXT;   
15    DECLARE t_tag_mod_val INT DEFAULT(25);
16    DECLARE t_commit_mod_val INT DEFAULT(100);
17    
18    DECLARE t_start_time DATETIME;
19    DECLARE t_end_time DATETIME;    
20    
21    TRUNCATE TABLE `system_user`;
22    
23    SET t_start_time=NOW();
24    SET t_sql_template = "INSERT INTO `system_user`(NAME, age, tag) VALUES";
25    SET t_sql = t_sql_template;
26    SET i = 1;
27    WHILE i <= COUNT
28        DO
29            SET t_age = FLOOR(1 + RAND() * 60);
30            SET t_name = LEFT(UUID(), 8);
31            -- 给tag随机制造空值
32            IF MOD(i, t_tag_mod_val) = 0 THEN
33                SET t_tag = "NULL";
34            ELSE
35                SET t_tag = CONCAT("'",LEFT(UUID(), 8),"'");
36            END IF;
37 
38            SET t_sql = CONCAT(t_sql,"('",t_name,"',",t_age,",",t_tag,")");
39            
40            IF MOD(i,t_commit_mod_val) != 0 THEN
41              SET t_sql = CONCAT(t_sql,",");
42            ELSE
43              SET t_sql = CONCAT(t_sql,";");
44                   -- 只要达到t_commit_mod_val要求的次数，就执行并提交
45                   SET @insert_sql = t_sql;
46                   PREPARE stmt FROM @insert_sql;
47                   EXECUTE stmt;
48                   DEALLOCATE PREPARE stmt;
49                   COMMIT;
50              SET t_sql=t_sql_template;
51            END IF;
52            SET i = i + 1;
53        END WHILE;
54        
55        -- 不能被t_commit_mod_val整除时，余下的数据处理
56        IF LENGTH(t_sql) > LENGTH(t_sql_template) THEN
57                   SET t_sql=CONCAT(SUBSTRING(t_sql,1,LENGTH(t_sql)-1),';');
58                   SET @insert_sql = t_sql;
59                   PREPARE stmt FROM @insert_sql;
60                   EXECUTE stmt;
61                   DEALLOCATE PREPARE stmt;
62                   COMMIT;
63        END IF;
64        SET t_end_time=NOW();
65        SELECT CONCAT('insert data success,time cost ',TIMEDIFF(t_end_time,t_start_time)) AS finishedTag;
66END$$
67
68DELIMITER ;