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

2024-01-08 16:50:03

文章目录

一、单个表生成测试数据

  • 作者使用的表结构
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, '第一年级', '一班', '北京市海淀区')

在这里插入图片描述

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;

参考文档

文章来源:https://blog.csdn.net/m0_46085118/article/details/134952726
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。