mysql存储过程小例子

DROP PROCEDURE IF EXISTS customview_logs_pro;
DELIMITER //
CREATE PROCEDURE customview_logs_pro(IN counts BIGINT)
BEGIN
DECLARE i BIGINT DEFAULT 1;
DECLARE total_count INT DEFAULT 1;
DECLARE timestr VARCHAR(19);
DECLARE from_source_id VARCHAR(64);
DECLARE daytime BIGINT;
DECLARE longtime BIGINT DEFAULT 0;
DECLARE answer_rand INT;
DECLARE answer_id INT;
DECLARE answer_name VARCHAR(64);
DECLARE input_model INT;
DECLARE input_rand INT;
WHILE i<=counts DO
  SET daytime = (SELECT ROUND(RAND()*30)) + 1;
  SET timestr=CONCAT('2017-04-',daytime,' 20:00:00');
  SET longtime=longtime+i;
  SET answer_rand = (SELECT ROUND(RAND()*5)) + 1;
  SET input_rand = (SELECT ROUND(RAND()*4)) + 1;
  
  IF input_rand=1
  THEN
	SET input_model = 0;
  ELSE
	SET input_model = 1;
  END IF;
  
  IF answer_rand=1
  THEN
	SET answer_id = 1;
	SET answer_name = 'xiaoxiao';
  ELSEIF answer_rand=2
  THEN
	SET answer_id = 3;
	SET answer_name = 'hasika';
  ELSEIF answer_rand=3
  THEN
	SET answer_id = 4;
	SET answer_name = 'tianlalu';
  ELSEIF answer_rand=4
  THEN	
	SET answer_id = 6;
	SET answer_name = 'shuren';
  ELSE 
  	SET answer_id = 8;
	SET answer_name = 'lianjin';
  END IF;
  
  SET from_source_id=CONCAT('userid_',(SELECT ROUND(RAND()*1000000)),'_',longtime);
  INSERT INTO table_name(ID,from_source_id) VALUES(i,191585688,2,from_source_id);
  IF MOD(i,10000)=0 THEN
   COMMIT;
  END IF;
  SET i = i + 1;
END WHILE;
COMMIT;
END

CALL service_logs_pro(10000000);

需要注意的是：

如果不需要将插入千万级数据的操作写在一个事务中，那么就需要如上所示，每隔10000条数据提交一次，否则会报“事务日志已满”的错误，导致无法完成插入操作；

如果需要写在一个事务中，那么可以考虑将事务日志的空间扩展。