MySQL批量添加数据存储过程示例

DELIMITER $$
-- 如果该存储过程已存在则删除
DROP PROCEDURE IF EXISTS SAVERANDDATA;
-- 创建存储过程名称
CREATE PROCEDURE SAVERANDDATA()
BEGIN
	DECLARE i INT;
	SET i = 0;
	-- 定义MYSQL REPEAT语句
	REPEAT
		-- SQL内容
		INSERT INTO `lzhstore`.`almart_all` (
			`date_key`,
			`hour_key`,
			`client_key`,
			`item_key`,
			`account`,
			`expense`
		)
		VALUES
		(
			"2016-05-01",
			FLOOR(RAND() * 24),
			FLOOR(RAND() * 1000000) + 1,
			FLOOR(RAND() * 100000) + 1,
			FLOOR(RAND() * 20) + 1,
			FLOOR(RAND() * 10000) + 1
		);	
	SET i = i + 1;
  -- 执行一百万次则结束循环
  UNTIL i = 1000000 END REPEAT;
	END $$
	CALL SAVERANDDATA();