自用的单表的大批量随机数据插入的方法(过程procedure和函数function)

说明

因为win下没有找到合适的批量随机插入的工具，也懒的在后端代码里写一套插入方法
直接在mysql下写个过程、函数来实现，当前版本比较死板和基础，记录下方便自己以后扩展！

表结构

过程

DROP PROCEDURE if exists insert_recycle_order;
CREATE PROCEDURE insert_recycle_order (
IN total INT,
IN third_id INT,
OUT insertCount INT
)
BEGIN
DECLARE i INTEGER;
DECLARE newid INTEGER;
SET i = 0;
SET insertCount = 0;
SET newid = 0;
WHILE i -- ignore 忽略无法插入的数据
INSERT IGNORE INTO recycle_order (
third_id,
third_recycle_id,
order_num,
third_brand,
third_brand_model,
actual_price,
payment_way,
payment_num,
items,
`STATUS`,
create_time
)
VALUES
(
third_id,
i,
order_rand (),
CEIL(rand() * 100),
CEIL(rand() * 100),
CEIL(rand() * 500),
CEIL(rand() * 8),
CEIL(rand() * 100),
items_rand (),
CEIL(rand() * 8),
UNIX_TIMESTAMP(now()) + i
);
set newid = LAST_INSERT_ID();
IF newid > 0 THEN
SET insertCount = insertCount + 1;
END IF;
SET i = i + 1;
END
WHILE;
END

函数

DROP FUNCTION IF EXISTS order_rand;
CREATE FUNCTION order_rand () RETURNS VARCHAR (16)
BEGIN
DECLARE order_num VARCHAR (16);
DECLARE uuid VARCHAR (50);
DECLARE tmpLast VARCHAR (50);
DECLARE i INTEGER;
SET order_num = DATE_FORMAT(now(), '%Y%m%d');
SET uuid = uuid();
SET tmpLast = '';
SET i = 1;
/*
WHILE i <= 8 DO
SET tmpLast = CONCAT(
tmpLast,
ORD(SUBSTRING(uuid, CEIL(rand()*36),1))
);
SET i = i + 1;
END
WHILE;
SET order_num = CONCAT(order_num, SUBSTRING(tmpLast, 1, 8));
*/
SET tmpLast = CONCAT(
tmpLast,
REPLACE(SUBSTRING(uuid, CEIL(rand()*24),8), '-', CEIL(rand()*9))
);
SET order_num = CONCAT(order_num, tmpLast);
RETURN order_num;
END

DROP FUNCTION IF EXISTS items_rand;
CREATE FUNCTION items_rand () RETURNS VARCHAR (50);
BEGIN
DECLARE items VARCHAR (50);
DECLARE i INTEGER;
SET items = '';
SET i = 0;
WHILE i <= 12 DO
SET items = CONCAT(items, '#',ceil((RAND() * 100)));
SET i = i + 1;
END
WHILE;
RETURN items;
END

调用方法

set @count = 0;
call insert_recycle_order(100000,1001,@count);
select @count;