MySQL数据库：批量生成50万帖子数据方案

使用MySQL存储过程

这个存储过程是针对 Discuz! 论坛系统的 pre_forum_post表设计的，该表存储论坛的帖子数据。

注意：本操作在测试环境进行，建议备份数据库或者打快照再操作。

1. 创建批量插入的存储过程

DELIMITER //
DROP PROCEDURE IF EXISTS generate_posts//

CREATE PROCEDURE generate_posts(IN num_posts INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE next_pid INT;
    DECLARE start_time BIGINT;
    DECLARE batch_size INT DEFAULT 5000; -- 每批插入5000条

    SET start_time = UNIX_TIMESTAMP();
    SELECT IFNULL(MAX(pid), 0) INTO next_pid FROM pre_forum_post;

    -- 优化设置（移除了sql_log_bin设置）
    SET SESSION unique_checks = 0;
    SET SESSION foreign_key_checks = 0;
    SET SESSION autocommit = 0;

    -- 禁用索引（加速插入）
    ALTER TABLE pre_forum_post DISABLE KEYS;

    WHILE i < num_posts DO
        SET @batch_values = '';
        SET @batch_count = 0;

        -- 构建当前批次数据
        WHILE i < num_posts AND @batch_count < batch_size DO
            SET next_pid = next_pid + 1;

            SET @batch_values = CONCAT(@batch_values, 
                IF(@batch_count > 0, ',', ''),
                '(',
                next_pid, ',1,', FLOOR(1 + RAND() * 100), ',0,',
                IF(i % 20 = 0, '1', '0'), ',',
                '''user', FLOOR(1 + RAND() * 100), ''',',
                FLOOR(1 + RAND() * 100), ',',
                '''测试主题-', i, ''',',
                UNIX_TIMESTAMP() - FLOOR(RAND() * 31536000), ',',
                UNIX_TIMESTAMP(), ',0,''''', ',',
                '''测试内容-', i, REPEAT(' 内容填充', 5), ''',',
                '''192.168.', FLOOR(RAND() * 255), '.', FLOOR(RAND() * 255), ''',',
                FLOOR(RAND() * 65535), ',',
                '0,0,1,0,-1,-1,0,0,0,0,0,''''', ',',
                '0,0,', i, ')'
            );

            SET i = i + 1;
            SET @batch_count = @batch_count + 1;
        END WHILE;

        -- 执行当前批次插入
        SET @sql = CONCAT('INSERT INTO pre_forum_post VALUES ', @batch_values);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        -- 每批提交一次
        COMMIT;

        -- 进度报告
        IF i % 10000 = 0 THEN
            SELECT CONCAT('已插入 ', i, '/', num_posts, ' 条 (', 
                         ROUND(i/num_posts*100,1), '%)') AS progress;
        END IF;
    END WHILE;

    -- 恢复设置
    ALTER TABLE pre_forum_post ENABLE KEYS;
    COMMIT;
    SET SESSION unique_checks = 1;
    SET SESSION foreign_key_checks = 1;
    SET SESSION autocommit = 1;

    -- 性能报告
    SELECT CONCAT('插入完成! 共 ', num_posts, ' 条，耗时: ', 
                 ROUND((UNIX_TIMESTAMP()-start_time)/60,2), ' 分钟') AS result;
END//
DELIMITER ;

如果写错用这个删除数据过程

DROP PROCEDURE IF EXISTS generate_posts;

2. 执行存储过程生成50万数据

-- 插入50万数据
CALL generate_posts(500000);

-- 清空表（可选）
TRUNCATE TABLE pre_forum_post;

3. 验证数据量

SELECT COUNT(*) FROM pre_forum_post;