造數儲存過程

DELIMITER $$

CREATE PROCEDURE GenerateBulkRandomData(IN max_records INT)
BEGIN
    DECLARE current_record INT DEFAULT 1;
    DECLARE v_id INT DEFAULT 0;
    DECLARE v_gender TINYINT DEFAULT 0;
    DECLARE v_state TINYINT DEFAULT 0;
    DECLARE v_name CHAR(10);
    DECLARE v_uuid CHAR(36);
    DECLARE v_address VARCHAR(255);
    DECLARE v_score DECIMAL(5,2);
    DECLARE v_insert_timestamp DATETIME;

    DECLARE allowed_chars VARCHAR(64) DEFAULT 'abcdefghijklmnopqrstuvwxyz0123456789_';

    WHILE current_record <= max_records DO
        

        SET v_gender = FLOOR(RAND() * 2);
        SET v_name = '';
        WHILE LENGTH(v_name) < 10 DO
            SET v_name = CONCAT(v_name, SUBSTRING('abcdefghijklmnopqrstuvwxyz', FLOOR(RAND() * 26) + 1, 1));
        END WHILE;

                SET v_address = '';
        WHILE LENGTH(v_address) < 20 DO -- 假設我們想要生成長度為20的隨機地址
            SET v_address = CONCAT(v_address, SUBSTRING(allowed_chars, FLOOR(RAND() * LENGTH(allowed_chars)) + 1, 1));
        END WHILE;

       SET v_score = RAND() * 100;
       SET v_uuid = UUID();
       SET v_insert_timestamp = FROM_UNIXTIME(UNIX_TIMESTAMP());
       SET v_state = CASE 
            WHEN RAND() <= 0.1 THEN NULL -- 調整此處的RAND() <= X.YZ以控制生成NULL的機率
       ELSE FLOOR(RAND() * 6) + 1
END;


        INSERT INTO test ( gender, name, address, score,uuid,insert_timestamp,state)
        VALUES ( v_gender, v_name, v_address, v_score,v_uuid,v_insert_timestamp,v_state);

        SET current_record = current_record + 1;
    END WHILE;

END$$

DELIMITER ;

-- 呼叫儲存過程插入10000000條記錄
CALL GenerateBulkRandomData(10000000);