CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
createDate DATE NOT NULL,
userName VARCHAR(255) NOT NULL,
phone VARCHAR(15),
age INT,
sex ENUM('M', 'F', 'O') NOT NULL, -- 假设'M'代表男,'F'代表女,'O'代表其他
introduce TEXT
);
根据DDL生成一个添加20万字条随机数据的存储过程
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(num_students INT)
BEGIN
DECLARE i 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 ENUM('M', 'F', 'O');
DECLARE random_introduce TEXT;
WHILE i < num_students DO
-- 生成随机日期(过去5年内的日期)
SET random_date = DATE_SUB(CURDATE(), INTERVAL FLOOR(RAND() * 1825) DAY);
-- 生成随机用户名(简化版,你可以根据需要调整)
SET random_user_name = CONCAT('User', FLOOR(RAND() * 1000000));
-- 生成随机电话号码
SET random_phone = CONCAT(
FLOOR(RAND() * (999999999999999 - 100000000000000 + 1)) + 100000000000000
);
-- 生成随机年龄(假设年龄在10到50岁之间)
SET random_age = FLOOR(RAND() * (50 - 10 + 1)) + 10;
-- 生成随机性别
SET random_sex = ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O');
-- 生成随机介绍文本(你可以根据需要替换成更复杂的文本)
SET random_introduce = CONCAT('这是一个随机介绍:', FLOOR(RAND() * 1000000));
-- 插入随机生成的数据
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES (random_date, random_user_name, random_phone, random_age, random_sex, random_introduce);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
-- 调用存储过程,插入20万条数据
CALL InsertRandomStudents(200000);