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
);
查询
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(num_students INT)
BEGIN
DECLARE counter INT DEFAULT 0;
WHILE counter < num_students DO
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES (
-- createDate: 随机日期(例如:过去10年内的随机日期)
DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 3650) DAY),
-- userName: 随机用户名(你可以根据需要调整生成逻辑)
CONCAT('User', FLOOR(RAND() * 100000)),
-- phone: 随机电话号码
CONCAT('1', FLOOR(RAND() * 800000000 + 100000000)),
-- age: 18到30岁之间的随机年龄
FLOOR(RAND() * (30 - 18 + 1)) + 18,
-- sex: 随机性别('M'或'F')
IF(RAND() > 0.5, 'M', 'F'),
-- introduce: 随机介绍文本(你可以替换为实际的文本或更复杂的逻辑)
CONCAT('This is a random introduction for User', FLOOR(RAND() * 100000))
);
SET counter = counter + 1;
END WHILE;
END //
DELIMITER ;
CALL InsertRandomStudents(200000);