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

一、单个表生成测试数据

• 作者使用的表结构

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
  

• 生成一个前缀被标记为 ljj 的UUID()。

  SELECT CONCAT('ljj', SUBSTRING(UUID(), 4))
  

• 也可以通过RAND()函数配合FLOOR()函数生成，如下生成5位数字，范围为0~99999

  SELECT FLOOR(RAND()*100000);
  

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

• 解决Mysql报错：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de
• 如果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

• 优化

DROP FUNCTION IF EXISTS randGetID;

CREATE FUNCTION randGetID() RETURNS text
BEGIN
	DECLARE tempID text;
	SELECT id INTO tempID FROM student ORDER BY RAND() LIMIT 1;
	IF( tempID IS NULL ) THEN
		RETURN UUID();
	END IF;
	RETURN tempID;
END

• 执行

SELECT randGetID()

其他

快速生成插入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;

常用的函数

• UUID() ：得到一个随机ID
• RAND()：生成随机数
• NOW()：获取当前时间
• CONCAT()：拼接字符串

参考文档

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