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

说明

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

表结构

Field	Type	Null	Key	Default	Extra
id	int(10) unsigned	NO	PRI	NULL	auto_increment
third_id	int(10) unsigned	NO	MUL	0
third_recycle_id	int(10)	NO		0
order_num	char(16)	NO	UNI
order_time	int(11)	NO		NULL
brand	int(10) unsigned	NO		0
brand_model	int(10) unsigned	NO		0
third_brand	int(10) unsigned	NO		0
third_brand_model	int(10) unsigned	NO	MUL	0
actual_price	decimal(8,2) unsigned	NO		0.00
payment_way	tinyint(1)	NO		0
items	varchar(300)	NO
status	tinyint(1)	NO		NULL
create_time	int(10) unsigned	NO	MUL	0

过程

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 < total DO
-- 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;