SQL批量造万级测试数据

前言

项目的一个导出功能，需要几十万条测试数据。哈哈，虽然有三年多的测试经历，但是批量造数据还是第一次，赶鸭子上架。

存储过程

存储过程填鸭式学习，找个模板套进去。开头结尾基本相同，主要是sql插入语句，自增字段以及外键需要注意下

delimiter //
DROP PROCEDURE
IF EXISTS proc_batch_insert_order_t ; CREATE PROCEDURE proc_batch_insert_order_t ()
BEGIN
    DECLARE
        pre_name BIGINT ; DECLARE
            ageVal INT ; DECLARE
                i INT ;
            SET i = 1;
            WHILE i <= 100000 DO
                
                INSERT INTO flow_order (
                    order_id,    #
                    trans_no,
                    order_name,
                    customer_no,
                    customer_name,
                    org_id,
                    tenant,
                    order_status,
                    pay_status,
                    total_amount,
                    pay_amount,
                    refund_amount,
                    cancel_reason,
                    refund_reason,
                    reject_reason,
                    groupon_type,
                    need_pay,
                    invite_id,
                    pay_time,
                    order_system_type,
                    close_time,
                    update_time,
                    create_time,
                    create_user,
                    update_user,
                    ext,
                    remark,
                    pay_timeout_time,
                    channel_type,
                    purchase_agreement
                )
            VALUES
                (
                    CONCAT('14257136101', i),
                    CONCAT('623692275684691122', i),
                    '初一寒假公益班',
                    1130105,
                    NULL,
                    36,
                    1,
                    0,
                    2,
                    1,
                    1,
                    0,
                    NULL,
                    NULL,
                    NULL,
                    0,
                    1,
                    NULL,
                    NULL,
                    2,
                    NOW(),
                    NOW(),
                    NOW(),
                    0,
                    0,
                    '{"channelType":"APP"}',
                    '',
                    NOW(),
                    0,
                    NULL
                ) ; INSERT INTO flow_order_item (
                    order_id,
                    order_item_name,
                    student_no,
                    intention_campus_id,
                    intention_campus_name,
                    student_expand_info,
                    order_type,
                    external_order_id,
                    item_amount,
                    item_actual_amount,
                    item_coupon_amount,
                    tenant,
                    update_time,
                    create_time,
                    create_user,
                    update_user,
                    ext,
                    student_name,
                    student_grade_name,
                    spec_json
                )
            VALUES
                (
                    CONCAT('14257136101', i),
                    '初一寒假公益班',
                    '5f03c2c2bf28a600011273a7',
                    69,
                    '城市广场校区',
                    '{"intentionCampusId":69,"intentionCampusName":"城市广场校区","publicSchoolName":"佛山市顺德区陈村碧桂花城学校（初中）","publicSchoolId":"01e2a7cd6ff042c1aa161c3b39c7d74c","externalStudentNo":"772911714611253997"}',
                    4,
                    '772911714611254019',
                    1,
                    1,
                    0,
                    1,
                    NOW(),
                    NOW(),
                    0,
                    0,
                    '',
                    '陈小朵',
                    '初一',
                    ''
                ) ; INSERT INTO flow_market_activity_order_item (
                    market_activity_order_item_id,
                    order_id,
                    order_item_id,
                    market_activity_id,
                    student_no,
                    market_product_id,
                    parent_id,
                    price,
                    actual_price,
                    item_coupon_amount,
                    son_total_price,
                    inventory_status,
                    is_unallocated_stock,
                    update_time,
                    create_time,
                    spec_matrix_id
                )
            VALUES
                (
                    CONCAT('1415716101', i),
                    CONCAT('14257136101', i),
                    721070+i,   #这个卡挺久的，外键
                    392,
                    '5f03c2c2bf28a600011273a7',
                    'PRO000000035056',
                    0,
                    0,
                    0,
                    0,
                    1,
                    0,
                    0,
                    NOW(),
                    NOW(),
                    0
                ) ;
            SET i = i + 1 ;
            END
            WHILE ;
            END//
 
delimiter ;
CALL proc_batch_insert_order_t ();

Python脚本

实际上，用Python造数据更方便，比较熟悉Python语法。雷同，写个循环语句，然后插入SQL语句即可

后记

这个脚本的效率不高，几十万条数据需要几个小时，哈哈，有待提升。