Mysql 订单生成10万条随机的测试数据

DELIMITER $$

CREATE PROCEDURE InsertLargeDataSet()
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE max_user_id INT DEFAULT 10000; -- 用户ID的最大值
    DECLARE min_amount DECIMAL(10,2) DEFAULT 1.00; -- 最小金额
    DECLARE max_amount DECIMAL(10,2) DEFAULT 1000.00; -- 最大金额
    DECLARE min_coupon_amount DECIMAL(10,2) DEFAULT 0.00; -- 最小优惠金额
    DECLARE max_coupon_amount DECIMAL(10,2) DEFAULT 50.00; -- 最大优惠金额
    DECLARE now DATETIME DEFAULT NOW(); -- 当前时间

    WHILE i <= 100000 DO
        INSERT INTO order_info (
            user_id, nick_name, order_no, coupon_id, total_amount, activity_amount,
            coupon_amount, original_total_amount, feight_fee, feight_fee_reduce,
            refundable_time, pay_type, source_type, order_status, process_status,
            leader_id, leader_name, leader_phone, take_name, receiver_name,
            receiver_phone, receiver_post_code, receiver_province, receiver_city,
            receiver_district, receiver_address, payment_time, delivery_time,
            take_time, receive_time, remark, cancel_time, cancel_reason,
            ware_id, commission_amount, create_time, update_time, is_deleted
        ) VALUES (
            FLOOR(1 + (RAND() * max_user_id)), -- 随机user_id
            CONCAT('User', i), -- 唯一的nick_name
            CONCAT('ORD20241217', LPAD(FLOOR(RAND() * 999999), 6, '0')), -- 随机order_no
            FLOOR(1 + (RAND() * 100)), -- 随机coupon_id
            ROUND(min_amount + (RAND() * (max_amount - min_amount)), 2), -- 随机total_amount
            ROUND((RAND() * 50), 2), -- 随机activity_amount
            ROUND(min_coupon_amount + (RAND() * (max_coupon_amount - min_coupon_amount)), 2), -- 随机coupon_amount
            ROUND(min_amount + (RAND() * (max_amount - min_amount)) + IFNULL(coupon_amount, 0), 2), -- 计算original_total_amount
            ROUND(1 + (RAND() * 50), 2), -- 随机feight_fee
            0, -- 随机feight_fee_reduce
            DATE_ADD(now, INTERVAL FLOOR(-30 + (RAND() * 60)) DAY), -- 随机refundable_time
            FLOOR(1 + (RAND() * 3)), -- 随机pay_type
            FLOOR(0 + (RAND() * 2)), -- 随机source_type
            FLOOR(0 + (RAND() * 4)), -- 随机order_status
            FLOOR(0 + (RAND() * 4)), -- 随机process_status
            FLOOR(1 + (RAND() * 1000)), -- 随机leader_id
            CONCAT('LeaderName', FLOOR(1 + (RAND() * 100))), -- 随机leader_name
            11111, -- 随机leader_phone
            CONCAT('TakePointName', FLOOR(1 + (RAND() * 100))), -- 随机take_name
            CONCAT('ReceiverName', FLOOR(1 + (RAND() * 100))), -- 随机receiver_name
            CONCAT('138', LPAD(FLOOR(RAND() * 999999999), 9, '0')), -- 随机receiver_phone
            LPAD(FLOOR(RAND() * 999999), 6, '0'), -- 随机receiver_post_code
            ELT(FLOOR(1 + RAND() * 4), 1, 2, 3, 4), -- 随机receiver_province
            ELT(FLOOR(1 + RAND() * 4), 1, 2, 3, 4), -- 随机receiver_city
            ELT(FLOOR(1 + RAND() * 4), 1, 2, 3, 4), -- 随机receiver_district
            CONCAT('No.', FLOOR(1 + (RAND() * 100)), ' Street'), -- 随机receiver_address
            DATE_ADD(now, INTERVAL FLOOR(-30 + (RAND() * 60)) DAY), -- 随机payment_time
            DATE_ADD(now, INTERVAL FLOOR(-30 + (RAND() * 60)) DAY), -- 随机delivery_time
            DATE_ADD(now, INTERVAL FLOOR(-30 + (RAND() * 60)) DAY), -- 随机take_time
            DATE_ADD(now, INTERVAL FLOOR(-30 + (RAND() * 60)) DAY), -- 随机receive_time
            CONCAT('Order Remark', i), -- 独特的remark
            IF(RAND() < 0.1, DATE_ADD(now, INTERVAL FLOOR(-30 + (RAND() * 60)) DAY), NULL), -- 有10%的概率为非空cancel_time
            IF(RAND() < 0.1, CONCAT('Cancel Reason', FLOOR(1 + (RAND() * 100))), NULL), -- 有10%的概率为非空cancel_reason
            FLOOR(1 + (RAND() * 100)), -- 随机ware_id
            ROUND((RAND() * 50), 2), -- 随机commission_amount
            now, -- create_time
            now, -- update_time
            0 -- is_deleted
        );
        SET i = i + 1;
    END WHILE;
END$$

DELIMITER ;