大概过程步骤
第1步、生成代码
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
createDate DATE NOT NULL,
userName VARCHAR(255) NOT NULL,
age INT NOT NULL,
sex ENUM('Male', 'Female', 'Other') NOT NULL,
introduce TEXT
);
根据生成的DDL写存储内容
第2步、
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(num_students INT)
BEGIN
DECLARE counter INT DEFAULT 0;
DECLARE random_username VARCHAR(255);
DECLARE random_age INT;
DECLARE random_sex ENUM('Male', 'Female', 'Other');
DECLARE random_introduce TEXT;
DECLARE random_create_date DATE;
-- 确保传入的学生数量是有效的
IF num_students <= 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid number of students';
END IF;
-- 开始循环插入数据
WHILE counter < num_students DO
-- 生成随机用户名(示例:User12345)
SET random_username = CONCAT('User', FLOOR(RAND() * 100000 + 1));
-- 生成随机年龄(示例:18到30岁之间)
SET random_age = FLOOR(RAND() * (30 - 18 + 1)) + 18;
-- 随机选择性别
SET random_sex = ELT(1 + FLOOR(RAND() * 3), 'Male', 'Female', 'Other');
-- 生成随机介绍文本(示例:随机长度的'A'字符串)
SET random_introduce = REPEAT('A', FLOOR(RAND() * 255));
-- 生成随机创建日期(示例:过去10年内的随机日期)
SET random_create_date = DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 3650) DAY);
-- 插入数据到student表中
INSERT INTO student (createDate, userName, age, sex, introduce)
VALUES (random_create_date, random_username, random_age, random_sex, random_introduce);
-- 更新计数器
SET counter = counter + 1;
END WHILE;
END //
DELIMITER ;
CALL InsertRandomStudents(200000);
第3步、
DELIMITER //
CREATE PROCEDURE InsertStudent(IN p_userName VARCHAR(255), IN p_introduce TEXT)
BEGIN
DECLARE random_age INT;
DECLARE random_sex ENUM('Male', 'Female', 'Other');
DECLARE random_create_date DATE;
-- 检查userName和introduce是否为空
IF p_userName IS NULL OR p_introduce IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'userName and introduce cannot be NULL';
END IF;
-- 检查userName是否以周、吴、郑、王开头
IF LEFT(p_userName, 1) = '周' OR LEFT(p_userName, 1) = '吴' OR LEFT(p_userName, 1) = '郑' OR LEFT(p_userName, 1) = '王' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'userName cannot start with 周, 吴, 郑, or 王';
END IF;
-- 生成随机年龄(例如:18到30岁之间)
SET random_age = FLOOR(RAND() * (30 - 18 + 1)) + 18;
-- 随机选择性别
SET random_sex = ELT(1 + FLOOR(RAND() * 3), 'Male', 'Female', 'Other');
-- 生成随机创建日期(例如:过去10年内的随机日期)
SET random_create_date = DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 3650) DAY);
-- 插入数据到student表中
INSERT INTO student (createDate, userName, age, sex, introduce)
VALUES (random_create_date, p_userName, random_age, random_sex, p_introduce);
END //
DELIMITER ;
CALL InsertStudent('JohnDoe', 'This is John Doe.');
第4步、
SELECT
id,
createDate,
userName,
age,
sex,
CASE
WHEN sex = 'Female' THEN '姑娘'
WHEN sex = 'Male' THEN '大老爷们'
WHEN sex = 'Other' THEN '泰国妹妹'
ELSE '未知' -- 可选,用于处理除Female、Male、Other之外的其他情况
END AS description,
introduce
FROM
student;