DDL
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertStudent`(IN p_userName VARCHAR(255), IN p_introduce TEXT)
BEGIN
-- 检查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;
-- 插入数据,其他字段使用随机值
INSERT INTO student (createDate, userName, age, sex, introduce)
VALUES (
CURDATE() - INTERVAL FLOOR(RAND() * 365) DAY, -- 随机创建日期
p_userName, -- 传入的用户名
FLOOR(RAND() * 50 + 10), -- 随机年龄(10到59岁)
IF(RAND() > 0.5, 'Male', 'Female'), -- 随机性别
p_introduce -- 传入的介绍
);
END
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertRandomStudents`(num_students INT)
BEGIN
DECLARE counter INT DEFAULT 0;
WHILE counter < num_students DO
INSERT INTO student (createDate, userName, age, sex, introduce)
VALUES (
CURDATE() - INTERVAL FLOOR(RAND() * 365) DAY, -- 随机创建日期(过去一年内)
CONCAT('User', FLOOR(RAND() * 100000) + 1), -- 随机用户名
FLOOR(RAND() * 20 + 5), -- 随机年龄(5到24岁)
ELT(1 + FLOOR(RAND() * 3), 'Male', 'Female', 'Other'), -- 随机性别
CONCAT('Introduction for ', CONCAT('User', FLOOR(RAND() * 100000) + 1)) -- 随机介绍
);
SET counter = counter + 1;
END WHILE;
END