mysql存储过程实现 非均匀随机生成数字

# 非重复
DELIMITER  $$
CREATE PROCEDURE non_uniform_random1(IN json_str VARCHAR(255), IN number INT)
BEGIN

    DECLARE i INT;
    DECLARE j INT;
    DECLARE no_uniformity_reduce INT;
    DECLARE max_min INT;
    DECLARE proSum INT;
    DECLARE no_uniformity_rand_number INT;
    DECLARE no_uniformity_rand_number_id INT;
    DECLARE bl_every INT;
    DECLARE bl INT;
    DECLARE max INT;
    DECLARE min INT;
    DECLARE itemcount INT;
    DECLARE sql_select varchar(100);
    DECLARE itemlist_two varchar(200);
    DECLARE itemlist_one varchar(200);
    DECLARE itemlist VARCHAR(200);
    DECLARE itemlist_child VARCHAR(200);
    DECLARE arr VARCHAR(100);
    DECLARE exist_place INT;
    DECLARE m INT;
    DECLARE n INT;
    SET @itemlist = json_str ->> "$.itemlist";
    SET @itemcount = JSON_LENGTH(json_str ->> "$.itemlist");
    set @k = 0;
    SET @arr = '';
    while @k < number
        do
            set @i = 0;
            SET @proSum = 0;
            set @j = 0;
            while @i < @itemcount
                do
                    set @itemlist_child = concat('$[', @i, ']');
                    select JSON_EXTRACT(@itemlist, @itemlist_child) into @itemlist_one;
                    set @bl_every = JSON_EXTRACT(@itemlist_one, "$.bl");
                    set @proSum = @proSum + @bl_every;
                    SET @i = @i + 1;
                end while;
            while @j < @itemcount
                do
                    set @no_uniformity_reduce = @proSum - 1;
                    set @no_uniformity_rand_number = FLOOR(1 + RAND() * (@no_uniformity_reduce + 1));
                    set @itemlist_child_two = concat('$[', @j, ']');
                    select JSON_EXTRACT(@itemlist, @itemlist_child_two) into @itemlist_two;
                    set @bl = JSON_EXTRACT(@itemlist_two, "$.bl");
                    set @max = JSON_EXTRACT(@itemlist_two, "$.fw_max");
                    set @min = JSON_EXTRACT(@itemlist_two, "$.fw_min");
                    SET @n = 1;
                    if @no_uniformity_rand_number <= @bl then
                        set @max_min = @max - @min;
                        while @n > 0
                            do
                                set @no_uniformity_rand_number_id = FLOOR(@min + RAND() * (@max_min + 1));
                                if @k = 0 then
                                    set @arr = concat(@arr, @no_uniformity_rand_number_id);
                                    set @n = 0;
                                else
                                    SELECT find_in_set(@no_uniformity_rand_number_id,concat("'",@arr,",'")) into @exist_place;
                                    if @exist_place >0 then
                                        set @n = @n + 1;
                                        if @n>(@max_min+1) then
                                            set @n =0;
                                        end if;
                                    else
                                        set @arr = concat(@arr, ',', @no_uniformity_rand_number_id);
                                        set @n = 0;
                                    end if;
                                end if;
                            end while;
                        set @j = @itemcount;
                    else
                        set @proSum = @proSum - @bl;
                        SET @j = @j + 1;
                    end if;
                end while;
            set @k = @k + 1;
        end while;
    select @arr;
END
$$

CALL non_uniform_random1(
        '{"itemlist":[{"fw_min":1,"fw_max":10,"bl":10},{"fw_min":31,"fw_max":40,"bl":20}, {"fw_min":11,"fw_max":20,"bl":40},{"fw_min":21,"fw_max":30,"bl":30}]}',
        20);

DROP PROCEDURE non_uniform_random1;