MySQL20万条数据的存储过程

1、DDL与DML

CREATE TABLE student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    createDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    userName VARCHAR(255) NOT NULL,
    phone VARCHAR(15),
    age INT,
    sex CHAR(1) COMMENT 'M: 男, F: 女, O: 其他',
    introduce TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';
INSERT INTO student (userName, phone, age, sex, introduce) VALUES
('张三', '13800138000', 20, 'M', '我是张三，就读于计算机科学专业。'),
('李四', '13900139000', 21, 'F', '我是李四，喜欢阅读和旅行。'),
('王五', '13700137000', 22, 'M', '我是王五，对摄影有浓厚兴趣。'),
('赵六', '13600136000', 23, 'F', '我是赵六，热衷于时尚和设计。'),
('孙七', '13500135000', 20, 'O', '我是孙七，对科学探索充满好奇。'),
('周八', '13400134000', 21, 'M', '我是周八，喜欢运动和健身。'),
('吴九', '13300133000', 22, 'F', '我是吴九，对音乐和舞蹈很有感觉。');

2、创建函数

DELIMITER //

CREATE PROCEDURE InsertRandomStudents(IN num_students INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    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 i < num_students DO
        -- 生成随机用户名
        SET random_user_name = CONCAT('Student', LPAD(FLOOR(RAND() * 1000000), 6, '0'));
        
        -- 生成随机电话号码
        SET random_phone = CONCAT('1', LPAD(FLOOR(RAND() * 999999999), 9, '0'));
        
        -- 生成随机年龄
        SET random_age = FLOOR(RAND() * 50) + 10; -- 假设年龄在10到60岁之间
        
        -- 生成随机性别
        SET random_sex = IF(RAND() > 0.5, 'M', IF(RAND() > 0.5, 'F', 'O'));
        
        -- 生成随机介绍
        SET random_introduce = CONCAT('This is ', random_user_name, '\'s introduction.');
        
        -- 插入数据到student表中
        INSERT INTO student (userName, phone, age, sex, introduce)
        VALUES (random_user_name, random_phone, random_age, random_sex, random_introduce);
        
        -- 更新计数器
        SET i = i + 1;
        
        -- 可选：每插入一定数量的记录后提交事务，以减少内存使用
        -- IF i % 1000 = 0 THEN
        --     COMMIT;
        --     START TRANSACTION;
        -- END IF;
    END WHILE;
    
    -- 提交事务，确保所有数据都已保存
    COMMIT;
END //

DELIMITER ;

3、插入数据并查询

CALL InsertRandomStudents(200000);
select * from student;