資料庫遷移為什麼「測一測沒壞」不夠？

因為跨 DB 遷移最危險的錯誤不會 crash。字串串接、DISTINCT ON、JSON 抽值、collation 大小寫、upsert 語法在 PostgreSQL 和 MySQL 行為不同，遷移後查詢照樣能跑、只是默默回錯數字。「測一測沒壞」只證明沒噴錯，不證明新舊行為一致。要用行為等價（parity）驗證：對舊 DB 和新 DB 跑同一批查詢，逐筆比對結果。

parity 驗證的三個階段各做什麼？

一、讀路徑 golden snapshot——挑約 40 支高風險查詢（JSON 抽值、LIKE 文字比對、DISTINCT ON、回 Decimal 的聚合），對舊 DB 跑一輪序列化成 JSON fixtures，切一個環境變數換 target 對新 DB 再跑。二、寫路徑場景驗證——設計 upsert 冪等、JSON round-trip、CRUD 等場景，斷言值寫在程式碼裡不從 DB 反推。三、三級 diff 分類——先正規化掉合法的非決定性，再把每筆分 IDENTICAL / TRIVIAL / SIGNIFICANT，只有 SIGNIFICANT 才判失敗。

PostgreSQL 遷到 MySQL 最容易踩哪些坑？

最陰的是 `||`——在 PG 是字串串接、在 MySQL 是邏輯 OR，不會報錯只會算錯。其他：DISTINCT ON 要改寫成 ROW_NUMBER 子查詢、STRING_AGG 改 GROUP_CONCAT、JSON path 要加 $. 前綴、CAST AS BIGINT 不存在（用 SIGNED）、upsert 從 ON CONFLICT 改 ON DUPLICATE KEY UPDATE、預設 collation utf8mb4_0900_ai_ci 大小寫加重音都不敏感。

怎麼確認遷移後資料沒被默默弄壞？

三招。一、pin 每張表的 row count 當 bulk sanity check。二、頭號寫入場景測 upsert 冪等性，專抓「conflict target 沒對到真的 unique key → 默默雙插」。三、diff 工具要先正規化合法的非決定性（排序所有 list 消 GROUP_CONCAT 順序噪音、截斷浮點吸收時間漂移），剩下的差異才是真的行為改變——這步是在論證「為什麼一個 diff 不算 bug」，不是無腦比對。

遷移資料庫最可怕的不是 crash，是默默算錯

資料庫遷移出事，你會期待它大聲地壞——查詢報錯、pod 起不來、CI 紅一片。那種其實好處理，因為你看得到。

真正可怕的是默默算錯：查詢照樣跑、頁面照樣開、沒有一行紅字，只是回來的數字悄悄錯了。字串串接變成布林、大小寫比對突然不分、upsert 靜默插了重複——這些在 PostgreSQL 和 MySQL 之間全是行為差異，而且都不會 crash。

所以「遷移完測一測、沒壞就上」是危險的。你需要的是行為等價（parity）驗證：證明「新舊 DB 對同一批操作的行為一致」，而不是「跑起來沒噴錯」。

核心：不是測「有沒有壞」，是測「行為一不一致」

flowchart TB
    subgraph OLD[舊 DB · PostgreSQL]
        Q1[跑同一批操作]
    end
    subgraph NEW[新 DB · MySQL]
        Q2[跑同一批操作]
    end
    Q1 --> F1[(JSON fixtures)]
    Q2 --> F2[(JSON fixtures)]
    F1 --> DIFF{逐筆比對}
    F2 --> DIFF
    DIFF -->|正規化後仍不同| BUG[遷移 bug ❌]
    DIFF -->|一致| OK[行為等價 ✅]

    style OLD fill:#3b82f6,color:#fff
    style NEW fill:#f59e0b,color:#fff
    style BUG fill:#ef4444,color:#fff
    style OK fill:#10b981,color:#fff

做成一條 CI 可跑的 pass/fail，而不是人肉盯畫面。分三階段。

階段一：讀路徑 golden snapshot

證明「同一個查詢在新舊 DB 回一樣的結果」。

挑約 40 支代表性且高風險的查詢——專挑方言差異大的：JSON 抽值、LIKE 文字比對、DISTINCT ON、回 Decimal 的聚合。不是隨便挑，是挑最容易在方言差異上翻車的。
對舊 DB 跑一輪，結果序列化成 JSON fixtures。
切一個環境變數把輸出目錄換掉，對新 DB 再跑同一批。
順手 pin 每張表的 row count 當 bulk sanity check——整批數量對不上，先別談細節。

精髓：golden snapshot 挑的是「高風險 query」不是「常用 query」。常用的通常簡單、不會出事；出事的都是那些用到方言特性的角落查詢。

階段二：寫路徑場景驗證

讀路徑對了，不代表寫路徑對。設計 5 個寫入場景：upsert 冪等性、JSON round-trip（unicode / 巢狀）、CRUD + 稽核表、欄位鎖。

兩個關鍵原則：

斷言值寫在程式碼裡，不是從 DB 反推。 同一份斷言在新舊 DB 都跑，不一致 = 遷移 bug。如果你從 DB 讀值再拿去比 DB，那是自我循環，測不到東西。
頭號場景是冪等性。 專抓「upsert 的 conflict target 沒對到真的 unique key → 默默雙插」——這是遷移最陰的靜默 bug 之一。

用 TEST-* 前綴的合成資料，走 SETUP → ACT → ASSERT → CLEANUP 自清，不污染真實資料。

flowchart LR
    S[SETUP<br>TEST-* 合成資料] --> A[ACT<br>upsert/CRUD]
    A --> AS[ASSERT<br>比對程式碼裡的期望值]
    AS --> C[CLEANUP<br>自清]

    AS -.新舊不一致.-> BUG[遷移 bug]
    style AS fill:#06b6d4,color:#fff
    style BUG fill:#ef4444,color:#fff

階段三：三級 diff 分類（精髓所在）

直接比對兩份 fixtures 會被合法的非決定性淹沒——時間戳、排序、浮點誤差每次都不同，但那些不算行為改變。所以先正規化：

排序所有 list——消掉 GROUP_CONCAT / 聚合的順序噪音。
截斷浮點——吸收 NOW() 之類的時間漂移。
數字字串統一——"1" 與 1 別當成不同。

正規化之後，每個 fixture 分三級：

級別	意思	動作
IDENTICAL	完全一樣	通過
TRIVIAL	只差合法的非決定性	通過（但記錄）
SIGNIFICANT	真的行為不同	失敗，exit 非零

只有 SIGNIFICANT / MISSING 才讓 CI 紅。

這一步的精髓：你是在「論證為什麼一個 diff 不算行為改變」，不是無腦比對。每一條被判 TRIVIAL 的差異，背後都有一個「因為它是排序噪音 / 時間漂移，所以不算 bug」的理由。這才是 parity 驗證跟「diff 兩個檔案」的本質差別。

附錄：PostgreSQL → MySQL 方言對照表

遷移時逐一踩過的坑。這些錯多半不會 crash、只會默默算錯：

情境	PostgreSQL	MySQL 8.0	備註
字串串接	`a \\|\\| b`	`CONCAT(a, b)`	⚠️ MySQL 的 `\\|\\|` 是邏輯 OR 不是串接——最陰的一條
取每組最新一筆	`DISTINCT ON`	`ROW_NUMBER() OVER (PARTITION BY…)` 子查詢	大表用 `INNER JOIN (MAX… GROUP BY)` 更快
字串聚合	`STRING_AGG`	`GROUP_CONCAT`
epoch 秒	`EXTRACT(EPOCH FROM ts)`	`UNIX_TIMESTAMP(ts)`
JSON 抽值	`col->'a'->>'b'`	`col->>'$.a.b'`	MySQL 要 `$.` 前綴
型別轉整數	`CAST(x AS BIGINT)`	`CAST(x AS SIGNED)`	MySQL 沒有 BIGINT 這個 cast 目標
Upsert	`ON CONFLICT … DO UPDATE`	`INSERT … AS new ON DUPLICATE KEY UPDATE col=new.col`	別用已 deprecated 的 `VALUES(col)`
大小寫	預設 case-sensitive	`utf8mb4_0900_ai_ci` 預設大小寫 + 重音都不分	嚴格比對用 `LIKE BINARY`

三個致命隱形陷阱

ON DUPLICATE KEY UPDATE 的 conflict target 必須對到真的 PRIMARY / UNIQUE KEY，否則不觸發、默默插重複。
ai_ci collation 讓 WHERE name = 'Hotfix' 也吃到 'hotfix'——unique key 會意外衝突。
Upsert 對「沒列出的欄位」天然保留舊值，別用子查詢自我 reference（MySQL 1093 錯誤）。

MySQL 環境還要注意

ONLY_FULL_GROUP_BY：MySQL 不會從 PK 推函數依賴（PG 會）→ PG 能跑的 GROUP BY 在 MySQL 丟 1055，補欄位或用 ANY_VALUE()。
STRICT_TRANS_TABLES：INT 欄收到 'abc' 會硬報錯而非默默變 0 → int(epoch) cast 要在 Python 先做。
RETURNING id 沒有 MySQL 對應 → 用 cursor.lastrowid。
時區：server time_zone=SYSTEM（=UTC）+ 連線後 SET time_zone='+00:00'，本機 my.cnf 也設 default-time-zone='+00:00'，否則遷移後時間全體偏移。

一句話

遷移最可怕的不是 crash，是默默算錯——因為 crash 你看得到，錯數字你看不到。用查詢快照逐筆比對證明新舊行為一致，而不是『測一測沒壞就上』。 做成一條 CI 的 pass/fail，讓「行為變了」在合併前就紅燈。

遷移資料庫最可怕的不是 crash，是默默算錯 — 行為等價驗證法