您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 模擬 PostgreSQL generate_series：三種生成序列數據的高效方案

編輯：MySQL 綜合教學

需求背景

PostgreSQL 強大的 generate_series 函數可輕鬆生成序列數據（如日期序列、數字序列），MySQL 8.0 前無原生對應功能。本文介紹三種在 MySQL 中生成測試數據、填補日期缺口、構建數字輔助表的實用技巧。

目標場景

向測試表 test_series(id, log_date) 插入 20 筆連續日期記錄：id 為 1~20，log_date 為當前日期往前推 1~20 天。

方案一：臨時表 + 會話變量迭代（最通用、無需建表）

步驟 1：建立播種預存程序

DELIMITER $$
CREATE PROCEDURE `sp_seed`(IN f_num INT UNSIGNED)
BEGIN
    DROP TEMPORARY TABLE IF EXISTS tmp_seed;
    CREATE TEMPORARY TABLE tmp_seed (id INT);
    
    DECLARE i INT DEFAULT 1;
    WHILE i <= f_num DO
        INSERT INTO tmp_seed VALUES (i);
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;

步驟 2：呼叫播種並插入目標表

CALL sp_seed(20);

INSERT INTO test_series 
SELECT @a := @a + 1 AS seq, 
       DATE_SUB(CURRENT_DATE(), INTERVAL @a DAY)
FROM tmp_seed, (SELECT @a := 0) AS init;

原理解析

tmp_seed 提供 20 行「佔位符」觸發 20 次選擇
(SELECT @a := 0) 衍生表初始化會話變量，確保每次查詢從 0 開始
@a := @a + 1 在每行計算時遞增，生成序號與日期間隔

方案二：永久數字輔助表（高頻複用、效能最佳）

建立通用序列表

CREATE TABLE `seq_1_to_10000` (
  `seq` INT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=InnoDB;

-- 一次性填充 1~10000
INSERT INTO seq_1_to_10000 (seq)
SELECT @i := @i + 1 FROM 
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4,
(SELECT @i := 0) init;

使用輔助表生成數據

INSERT INTO test_series 
SELECT s1.seq, DATE_SUB(CURRENT_DATE(), INTERVAL s2.seq DAY)
FROM seq_1_to_10000 AS s1
JOIN seq_1_to_10000 AS s2 ON s1.seq = s2.seq
WHERE s1.seq <= 20;

優勢

一次建立，全庫複用（報表填補、分頁測試、時間序列生成）
純 SQL 無變量依賴，優化器可產生最佳執行計畫
可擴展至百萬級序列，配合索引極快

方案三：MySQL 8.0+ 遞歸 CTE（現代標準寫法）

WITH RECURSIVE seq_cte(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq_cte WHERE n < 20
)
INSERT INTO test_series (id, log_date)
SELECT n, DATE_SUB(CURRENT_DATE(), INTERVAL n DAY)
FROM seq_cte;

適用版本：MySQL 8.0、MariaDB 10.2+。語法標準、可讀性最強、無臨時物件開銷。

方案對比與選型建議

維度	方案一：臨時表+變量	方案二：永久輔助表	方案三：CTE (8.0+)
MySQL 版本	5.0+	5.0+	8.0+
部署成本	需建立 Procedure	需建立永久表	零部署
執行效能	中（變量逐行計算）	高（索引掃描）	高（優化器原生支援）
並發安全	臨時表隔離、安全	唯讀、極安全	無狀態、最安全
適用場景	臨時腳本、遷移腳本	生產報表、高頻生成	新專案、現代化技術棧

實戰延伸：生成日期維度表

-- 利用方案二快速建立未來 10 年日期維度
INSERT INTO dim_date (date_key, full_date, year, month, day, weekday, is_weekend)
SELECT 
    DATE_FORMAT(d, '%Y%m%d'),
    d,
    YEAR(d), MONTH(d), DAY(d), WEEKDAY(d),
    CASE WHEN WEEKDAY(d) IN (5,6) THEN 1 ELSE 0 END
FROM (
    SELECT DATE_ADD('2024-01-01', INTERVAL seq DAY) AS d
    FROM seq_1_to_10000
    WHERE seq <= 3652  -- 10年含閏年
) t;