mysql批量创建测试数据

古城微笑少年丶 2022-11-21 04:07 143阅读 0赞

**1、测试表**

CREATE TABLE `test` (
      `id` bigint(20) NOT NULL,
      `name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `address` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='测试表'

**2、生成随机字符串**

DELIMITER $$
    CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
    BEGIN
        DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
        DECLARE return_str VARCHAR(255) DEFAULT '';
        DECLARE i INT DEFAULT 0;
        WHILE i < n DO
        SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
        SET i = i + 1;
        END WHILE;
        RETURN return_str;
    END $$

**3、生成随机数字**

DELIMITER $$
    CREATE FUNCTION rand_num() RETURNS INT(5)
    BEGIN
        DECLARE i INT DEFAULT 0;
        SET i = FLOOR(100 + RAND() * 10);
        RETURN i;
    END $$

**4、插入数据库脚本**

DELIMITER $$
    CREATE PROCEDURE insert_test(IN START INT(10),IN max_num INT(10))
    BEGIN
    DECLARE i INT DEFAULT 0;
        SET autocommit = 0;
        REPEAT
        SET i = i + 1;
        INSERT INTO test(id,name,age,address) VALUES((START + i),rand_string(10),rand_num(),rand_string(20));
        UNTIL i = max_num
        END REPEAT;
        COMMIT;
    END $$

**5、执行存储过程**

DELIMITER ;
    CALL insert_test(11,50000);

**6、结果展示**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c1OTIzNzY1Njg_size_16_color_FFFFFF_t_70_pic_center]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c1OTIzNzY1Njg_size_16_color_FFFFFF_t_70_pic_center 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c1OTIzNzY1Njg_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/8c9edec66bed44deb7ccc64bcae63765.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3c1OTIzNzY1Njg_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/9390f738a1a44f3caf4818a683c55095.png