DDL
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`createDate` date NOT NULL,
`userName` varchar(50) NOT NULL,
`phone` varchar(15) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('男','女') DEFAULT NULL,
`introduce` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
DML
INSERT INTO student (createDate, userName, phone, age, sex, introduce) VALUES
('2023-10-23', '张三', '13800138000', 20, '男', '我是张三,喜欢读书。'),
('2023-10-22', '李四', '13900139000', 21, '女', '我是李四,喜欢运动。'),
('2023-10-21', '王五', '13700137000', 22, '男', '我是王五,喜欢音乐。'),
('2023-10-20', '赵六', '13600136000', 20, '女', '我是赵六,喜欢画画。'),
('2023-10-19', '孙七', '13500135000', 21, '男', '我是孙七,喜欢旅行。'),
('2023-10-18', '周八', '13400134000', 22, '女', '我是周八,喜欢摄影。'),
('2023-10-17', '吴九', '13300133000', 20, '男', '我是吴九,喜欢编程。');
DQL
DELIMITER //
CREATE PROCEDURE InsertRandomStudents(num_students INT)
BEGIN
DECLARE counter INT DEFAULT 0;
DECLARE random_user_name VARCHAR(50);
DECLARE random_phone VARCHAR(15);
DECLARE random_age INT;
DECLARE random_sex ENUM('男', '女');
DECLARE random_introduce TEXT;
-- 确保num_students是一个合理的数量
IF num_students <= 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Number of students must be positive';
END IF;
-- 开始事务,可以根据需要调整事务的大小来优化性能
START TRANSACTION;
WHILE counter < num_students DO
-- 生成随机用户名(示例:使用现有的用户名加上一些随机数字)
SET random_user_name = CONCAT('User', FLOOR(RAND() * 1000000));
-- 生成随机电话号码(示例:以1开头的11位数字)
SET random_phone = CONCAT('1', LPAD(FLOOR(RAND() * 10000000000), 10, '0'));
-- 生成随机年龄(示例:18到30岁之间)
SET random_age = FLOOR(18 + RAND() * 13);
-- 随机分配性别
IF RAND() > 0.5 THEN
SET random_sex = '男';
ELSE
SET random_sex = '女';
END IF;
-- 生成随机介绍(示例:一段简单的文本)
SET random_introduce = CONCAT('This is ', random_user_name, '\'s introduction.');
-- 插入数据到student表中
INSERT INTO student (createDate, userName, phone, age, sex, introduce)
VALUES (CURDATE(), random_user_name, random_phone, random_age, random_sex, random_introduce);
-- 更新计数器
SET counter = counter + 1;
-- 可以根据需要提交事务并重新开始新的事务,以防止事务过大
-- IF counter % 1000 = 0 THEN
-- COMMIT;
-- START TRANSACTION;
-- END IF;
END WHILE;
-- 提交事务
COMMIT;
END //
DELIMITER ;
CALL InsertRandomStudents(200000);