Mysql编写存储过程生成测试数据，大批量造数据

怼烎@ 2024-02-05 14:41 172阅读 0赞

#### 文章目录 ####

*  一、单个表生成测试数据
 *  二、向两个表插入测试数据，保证id相同
 *   *  初始化表脚本
     *  向两个表中插入数据
 *  三、mysql生成id的方法
 *   *  mysql自带自动生成id的方法
     *  编写一个函数从student表中随机取出一个id
 *  其他
 *   *  快速生成插入sql语句的方法
     *  mysql8创建带返回值的函数报错问题
 *  参考文档

## 一、单个表生成测试数据 ##

*  作者使用的表结构

CREATE TABLE `student` (
      `id` bigint NOT NULL,
      `user_name` varchar(255) DEFAULT NULL,
      `age` int DEFAULT NULL,
      `dept` varchar(255) DEFAULT NULL,
      `class` varchar(255) DEFAULT NULL,
      `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

*  生成数据的脚本

DROP PROCEDURE IF EXISTS insert_while;
    
    #创建存储过程
    delimiter //
    	CREATE PROCEDURE insert_while()
    	BEGIN
    		DECLARE i INT;
    		SET i = 1;
    		WHILE i <= 10 DO
    			INSERT INTO `student`(`id`, `user_name`, `age`, `dept`, `class`, `remark`)
    			VALUES(CONCAT(10000 + i), CONCAT('张先生', i), i, "第一年级", '一班', '北京市海淀区');
    			SET i = i + 1;
    		END WHILE;
    	END //
    delimiter;
    
    #执行存储过程
    CALL insert_while();
    
    #删除存储过程
    DROP PROCEDURE IF EXISTS insert_while;

## 二、向两个表插入测试数据，保证id相同 ##

### 初始化表脚本 ###

*  注意student的id类型被我改成了varchar

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student`  (
      `id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
      `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      `age` int NULL DEFAULT NULL,
      `dept` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      `class` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user`  (
      `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
      `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
    
    SET FOREIGN_KEY_CHECKS = 1;

### 向两个表中插入数据 ###

#删除存储过程
    DROP PROCEDURE IF EXISTS insert_while;
    
    #创建存储过程
    delimiter //
    	CREATE PROCEDURE insert_while()
    	BEGIN
    		DECLARE i INT;
    		DECLARE tempUUID text;
    		SET i = 1;
    		WHILE i <= 10 DO
    			SET tempUUID = UUID() ;
    			INSERT INTO `user`(`id`,`name`)
    			VALUES (tempUUID,CONCAT('张先生', i));
    			INSERT INTO `student`(`id`, `user_name`, `age`, `dept`, `class`, `remark`)
    			VALUES(tempUUID, CONCAT('张先生', i), i, "第一年级", '一班', '北京市海淀区');
    			SET i = i + 1;
    		END WHILE;
    	END //
    delimiter;
    
    #执行存储过程
    CALL insert_while();
    
    #删除存储过程
    DROP PROCEDURE IF EXISTS insert_while;

## 三、mysql生成id的方法 ##

### mysql自带自动生成id的方法 ###

*  UUID() 函数，数据格式为
    
        59ca714c-98c9-11ee-be30-000ec6c74e60
 *  UUID\_SHORT()函数，数据格式为
    
        100618135811391488
 *  也可以通过RAND()函数配合FLOOR()函数生成，如下生成5位数字，范围为0~99999
    
        SELECT FLOOR(RAND()*100000);

### 编写一个函数从student表中随机取出一个id ###

*  如果student表为空，则自动获取一个UUID

DROP FUNCTION IF EXISTS randGetID;
    
    CREATE FUNCTION randGetID() RETURNS text
    BEGIN
    	DECLARE i INT;
    	DECLARE j INT;
    	DECLARE tempID text;
    	SELECT COUNT(*) INTO i FROM student;
    	IF(i>0)THEN
    		SET j=FLOOR(RAND()*i);
    		SELECT id INTO tempID FROM student LIMIT j,1;
    	ELSE
    		SET tempID=UUID();
    	END IF;
    	RETURN tempID;
    END

## 其他 ##

### 快速生成插入sql语句的方法 ###

INSERT INTO `test`.`student` (`id`, `user_name`, `age`, `dept`, `class`, `remark`) 
    VALUES ('69e7e4ed-98cd-11ee-be30-000ec6c74e61', '张先生10', 10, '第一年级', '一班', '北京市海淀区')

![在这里插入图片描述][6085775cb0134134af45bd41afa2e20e.png]

### mysql8创建带返回值的函数报错问题 ###

*  错误原文

1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

*  解决办法

SET GLOBAL log_bin_trust_function_creators = 1;

## 参考文档 ##

*  [MySQL大批量造数据][MySQL]
 *  [mysql 生成随机数 —— 数字、字符串、手机号、日期、姓名、一段中文汉字][mysql _ _]

[6085775cb0134134af45bd41afa2e20e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/3f7edfa6b5c6465ca54ef511d3aeda27.png
[MySQL]: https://blog.csdn.net/wangmcn/article/details/123514883
[mysql _ _]: https://blog.csdn.net/xiaojin21cen/article/details/103661435