DDL
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
createDate DATE NOT NULL,
userName VARCHAR(255) NOT NULL,
age INT NOT NULL,
phone VARCHAR(15),
sex ENUM('Male', 'Female', 'Other') NOT NULL,
introduce TEXT
);
存储过程
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(num_students INT)
BEGIN
DECLARE counter INT DEFAULT 0;
DECLARE random_user_name VARCHAR(255);
DECLARE random_age INT;
DECLARE random_phone VARCHAR(15);
DECLARE random_sex ENUM('Male', 'Female', 'Other');
DECLARE random_introduce TEXT;
WHILE counter < num_students DO
-- 生成随机用户名(示例:User123)
SET random_user_name = CONCAT('User', FLOOR(RAND() * 100000));
-- 生成随机年龄(示例:18-30岁之间)
SET random_age = FLOOR(RAND() * (30 - 18 + 1)) + 18;
-- 生成随机电话号码(示例:1234567890)
SET random_phone = LPAD(FLOOR(RAND() * 10000000000), 10, '0');
-- 随机选择性别
SET random_sex = ELT(1 + FLOOR(RAND() * 3), 'Male', 'Female', 'Other');
-- 生成随机介绍文本(示例:随机长度的'A'字符串)
SET random_introduce = REPEAT('A', FLOOR(RAND() * 255));
-- 插入随机生成的学生数据
INSERT INTO student (createDate, userName, age, phone, sex, introduce)
VALUES (CURDATE(), random_user_name, random_age, random_phone, random_sex, random_introduce);
-- 增加计数器
SET counter = counter + 1;
END WHILE;
END //
DELIMITER ;
15万条数据
CALL InsertRandomStudents(150000);
随机生成
DELIMITER //
CREATE PROCEDURE InsertStudentData(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, 3) = '周武王' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'userName cannot start with "周武王"';
END IF;
-- 如果检查通过,则插入数据,其他字段随机生成
INSERT INTO student (createDate, userName, age, phone, sex, introduce)
VALUES (
CURDATE(), -- 当前日期
p_userName, -- 传入的userName
FLOOR(RAND() * (30 - 18 + 1)) + 18, -- 18到30之间的随机年龄
LPAD(FLOOR(RAND() * 10000000000), 10, '0'), -- 随机电话号码
ELT(1 + FLOOR(RAND() * 3), 'Male', 'Female', 'Other'), -- 随机性别
p_introduce -- 传入的introduce
);
END //
DELIMITER ;
开始
CALL InsertStudentData('JohnDoe', 'This is John Doe\'s introduction.');
结束
SELECT
id,
createDate,
userName,
age,
phone,
CASE sex
WHEN 'Female' THEN '姑娘'
WHEN 'Male' THEN '大老爷们'
WHEN 'Other' THEN '泰国妹妹'
ELSE '未知' -- 可选,用于处理除Female、Male、Other之外的其他情况
END AS description,
introduce
FROM
student;