DDL
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
createDate DATE,
userName VARCHAR(255),
phone VARCHAR(15),
age INT,
sex CHAR(1),
introduce TEXT
);
DML
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-01-01', '张三', '12345678901', 20, '男', '我是张三,喜欢读书。'),
('2023-02-01', '李四', '23456789012', 21, '男', '我是李四,喜欢运动。'),
('2023-03-01', '王五', '34567890123', 22, '女', '我是王五,喜欢音乐。'),
('2023-04-01', '赵六', '45678901234', 23, '女', '我是赵六,喜欢画画。'),
('2023-05-01', '孙七', '56789012345', 24, '男', '我是孙七,喜欢旅行。'),
('2023-06-01', '周八', '67890123456', 25, '女', '我是周八,喜欢摄影。'),
('2023-07-01', '吴九', '78901234567', 26, '男', '我是吴九,喜欢编程。');
DQL
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(num_students INT)
BEGIN
DECLARE counter INT DEFAULT 0;
DECLARE random_date DATE;
DECLARE random_user_name VARCHAR(255);
DECLARE random_phone VARCHAR(15);
DECLARE random_age INT;
DECLARE random_sex CHAR(1);
DECLARE random_introduce TEXT;
WHILE counter < num_students DO
-- 生成随机日期(例如:过去10年内的随机日期)
SET random_date = MAKEDATE(YEAR(CURDATE()) - FLOOR(RAND() * 10), 1) + INTERVAL FLOOR(RAND() * 365) DAY;
-- 生成随机用户名(示例:使用现有的姓名库或随机字符串)
SET random_user_name = CONCAT('User', FLOOR(RAND() * 100000)); -- 这里简化为随机编号的用户名
-- 生成随机手机号
SET random_phone = CONCAT('1', FLOOR(RAND() * (999999999 - 100000000 + 1)) + 100000000);
-- 生成随机年龄(例如:18到30岁)
SET random_age = FLOOR(RAND() * (30 - 18 + 1)) + 18;
-- 随机性别(M或F)
SET random_sex = IF(RAND() > 0.5, 'M', 'F');
-- 生成随机介绍(示例:使用随机字符串或预定义的介绍列表)
SET random_introduce = CONCAT('Introduction for ', random_user_name); -- 这里简化为基于用户名的介绍
-- 插入数据
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES (random_date, random_user_name, random_phone, random_age, random_sex, random_introduce);
-- 增加计数器
SET counter = counter + 1;
END WHILE;
END //
CALL InsertRandomStudents(200000);
DELIMITER ;