mysql存储过程批量提交

DROP PROCEDURE IF EXISTS `insert_hlooc`$$

CREATE DEFINER=`hlooc`@`%` PROCEDURE `insert_hlooc`(IN max_num INT(10))
BEGIN  
DECLARE bbb VARCHAR(100) DEFAULT '我是谁';   
DECLARE i INT DEFAULT 0;   
 SET autocommit = 0;    
 REPEAT  
 SET i = i + 1;  
 SET c1 = CONCAT(bbb,LPAD(i,6,'0'));
 
 INSERT INTO `hlooc` (xxx) values(xxx);

   IF MOD(i,10000)=0 THEN
       COMMIT;              
   END IF;

 UNTIL i = max_num  
 END REPEAT;  
 COMMIT;  
 END$$

DELIMITER ;

使用了CONCAT与LPAD函数

LPAD函数

1	LPAD(str,len,padstr)

返回字符串str，将其左填充字符串padstr至len个字符的长度。如果str大于len，则返回值缩短为len个字符。

CONCAT

CONCAT函数用于连接两个字符串以形成单个字符串

mmysql> SELECT CONCAT('FIRST ', 'SECOND');
+----------------------------+
| CONCAT('FIRST ', 'SECOND') |
+----------------------------+
|        FIRST SECOND        |
+----------------------------+
1 row in set (0.00 sec)