-- 1. 創(chuàng)建測試表
DROP TABLE IF EXISTS test_pagination;
CREATE TABLE test_pagination (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(20),
    created_at DATETIME,
    updated_at DATETIME
);

-- 2. 創(chuàng)建存儲過程
DELIMITER $$

DROP PROCEDURE IF EXISTS generate_pagination_test_data$$
CREATE PROCEDURE generate_pagination_test_data(
    IN total_rows INT,          -- 總記錄數(shù)
    IN batch_size INT,          -- 批次大?。ㄏ嗤瑫r間的記錄數(shù)）
    IN start_date VARCHAR(19),  -- 開始時間
    IN end_date VARCHAR(19)     -- 結(jié)束時間
)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE batch_count INT DEFAULT 0;
    DECLARE batch_time DATETIME;
    DECLARE start_time DATETIME;
    DECLARE end_time DATETIME;
    DECLARE total_seconds INT;
    DECLARE interval_seconds INT;
    DECLARE total_batches INT;
    DECLARE current_batch INT DEFAULT 0;

    -- 所有變量聲明
    SET start_time = STR_TO_DATE(start_date, '%Y-%m-%d %H:%i:%s');
    SET end_time = STR_TO_DATE(end_date, '%Y-%m-%d %H:%i:%s');
    SET total_seconds = TIMESTAMPDIFF(SECOND, start_time, end_time);

    -- 計算總批次數(shù)和批次時間間隔
    SET total_batches = CEIL(total_rows / batch_size);
    SET interval_seconds = FLOOR(total_seconds / total_batches);
    IF interval_seconds < 1 THEN SET interval_seconds = 1; END IF;

    -- 設(shè)置初始批次時間
    SET batch_time = start_time;
    SET current_batch = 0;

    -- 性能優(yōu)化設(shè)置
    SET autocommit = 0;
    SET unique_checks = 0;
    SET foreign_key_checks = 0;

    -- 開始生成數(shù)據(jù)
    WHILE i < total_rows DO
        -- 如果批次計數(shù)器為0，表示開始新批次
        IF batch_count = 0 THEN
            -- 為這個批次生成一個時間
            SET batch_time = DATE_ADD(start_time,
                INTERVAL current_batch * interval_seconds +
                FLOOR(RAND() * 60) SECOND  -- 加一些隨機(jī)偏移
            );
            SET current_batch = current_batch + 1;
        END IF;

        -- 插入記錄（同批次記錄時間相同）
        INSERT INTO test_pagination (status, created_at, updated_at)
        VALUES (
            CASE
                WHEN RAND() < 0.6 THEN 'active'
                WHEN RAND() < 0.9 THEN 'pending'
                ELSE 'deleted'
            END,
            batch_time,
            batch_time
        );

        -- 更新計數(shù)器
        SET i = i + 1;
        SET batch_count = batch_count + 1;

        -- 如果達(dá)到批次大小，重置批次計數(shù)器
        IF batch_count >= batch_size THEN
            SET batch_count = 0;
        END IF;

        -- 每5000條提交一次
        IF i % 5000 = 0 THEN
            COMMIT;
        END IF;
    END WHILE;

    -- 最終提交
    COMMIT;

    -- 恢復(fù)設(shè)置
    SET autocommit = 1;
    SET unique_checks = 1;
    SET foreign_key_checks = 1;

END$$

DELIMITER ;

-- 3. 調(diào)用存儲過程生成數(shù)據(jù)
-- 生成1萬條數(shù)據(jù)，每批次7條相同時間，時間從2026-01-01到2026-01-01
CALL generate_pagination_test_data(
    10000,      -- 總記錄數(shù)
    7,          -- 批次大?。ㄏ嗤瑫r間的記錄數(shù)）
    '2026-01-01 00:00:00',
    '2026-01-01 23:59:59'
);

-- 4. 清理存儲過程
DROP PROCEDURE IF EXISTS generate_pagination_test_data;

-- 優(yōu)化前
SELECT * FROM test_pagination ORDER BY created_at LIMIT 20, 10;

-- 優(yōu)化后
SELECT * FROM test_pagination ORDER BY created_at, id LIMIT 20, 10;