CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
createDate DATE NOT NULL,
userName VARCHAR(255) NOT NULL,
phone VARCHAR(20),
age INT,
sex ENUM('M', 'F', 'O') NOT NULL, -- 假设性别用M表示男,F表示女,O表示其它
introduce TEXT
);
根据DDL生成一个添加20万字条随机数据的存储过程
DELIMITER //
CREATE PROCEDURE InsertRandomStudents()
BEGIN
DECLARE i INT DEFAULT 0;
DECLARE randomUserName VARCHAR(255);
DECLARE randomPhone VARCHAR(20);
DECLARE randomAge INT;
DECLARE randomSex ENUM('M', 'F', 'O');
DECLARE randomIntroduce TEXT;
DECLARE randomCreateDate DATE;
-- 设置随机数种子,可选,为了每次生成的数据不同
-- SET @@rand_seed1 = RAND(NOW());
WHILE i < 200000 DO
-- 生成随机用户名,这里简化为随机长度的'user'后缀数字
SET randomUserName = CONCAT('user', FLOOR(RAND() * 1000000));
-- 生成随机电话号码,这里简化为11位数字
SET randomPhone = LPAD(FLOOR(RAND() * 10000000000), 11, '0');
-- 生成随机年龄,假设范围是1到100
SET randomAge = FLOOR(RAND() * 100) + 1;
-- 生成随机性别
SET randomSex = ELT(1 + FLOOR(RAND() * 3), 'M', 'F', 'O');
-- 生成随机介绍,这里简化为随机长度的文本
SET randomIntroduce = CONCAT('介绍', LPAD(FLOOR(RAND() * 1000000), 6, '0'));
-- 生成随机创建日期,这里假设从2000年到当前年份的随机日期
SET randomCreateDate = STR_TO_DATE(CONCAT(
FLOOR(2000 + RAND() * (YEAR(CURDATE()) - 2000)),
LPAD(FLOOR(RAND() * 12) + 1, 2, '0'),
LPAD(FLOOR(RAND() * 28) + 1, 2, '0')
), '%Y%m%d');
-- 插入随机数据
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES (randomCreateDate, randomUserName, randomPhone, randomAge, randomSex, randomIntroduce);
-- 更新计数器
SET i = i + 1;
END WHILE;
END //
DELIMITER ;