---
title: 遷移資料庫最可怕的不是 crash，是默默算錯 — 行為等價驗證法
description: 跨資料庫遷移的錯誤多半不會 crash，只會默默算錯數字。這篇給一套可放進 CI 的行為等價（parity）驗證三階段：讀路徑 golden snapshot、寫路徑場景驗證、三級 diff 分類，外加 PostgreSQL → MySQL 方言對照表與三個致命隱形陷阱。
category: automation
tags: [database-migration, parity-testing, postgresql, mysql, data-validation]
date: 2026-07-27
faq:
  - q: 資料庫遷移為什麼「測一測沒壞」不夠？
    a: 因為跨 DB 遷移最危險的錯誤不會 crash。字串串接、DISTINCT ON、JSON 抽值、collation 大小寫、upsert 語法在 PostgreSQL 和 MySQL 行為不同，遷移後查詢照樣能跑、只是默默回錯數字。「測一測沒壞」只證明沒噴錯，不證明新舊行為一致。要用行為等價（parity）驗證：對舊 DB 和新 DB 跑同一批查詢，逐筆比對結果。
  - q: parity 驗證的三個階段各做什麼？
    a: 一、讀路徑 golden snapshot——挑約 40 支高風險查詢（JSON 抽值、LIKE 文字比對、DISTINCT ON、回 Decimal 的聚合），對舊 DB 跑一輪序列化成 JSON fixtures，切一個環境變數換 target 對新 DB 再跑。二、寫路徑場景驗證——設計 upsert 冪等、JSON round-trip、CRUD 等場景，斷言值寫在程式碼裡不從 DB 反推。三、三級 diff 分類——先正規化掉合法的非決定性，再把每筆分 IDENTICAL / TRIVIAL / SIGNIFICANT，只有 SIGNIFICANT 才判失敗。
  - q: PostgreSQL 遷到 MySQL 最容易踩哪些坑？
    a: 最陰的是 `||`——在 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 大小寫加重音都不敏感。
  - q: 怎麼確認遷移後資料沒被默默弄壞？
    a: 三招。一、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 對同一批操作的行為一致」，而不是「跑起來沒噴錯」。

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

```mermaid
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 回一樣的結果」。

1. 挑約 **40 支代表性且高風險**的查詢——**專挑方言差異大的**：JSON 抽值、`LIKE` 文字比對、`DISTINCT ON`、回 `Decimal` 的聚合。不是隨便挑，是挑最容易在方言差異上翻車的。
2. 對**舊 DB** 跑一輪，結果序列化成 **JSON fixtures**。
3. **切一個環境變數**把輸出目錄換掉，對**新 DB** 再跑同一批。
4. 順手 **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** 自清，不污染真實資料。

```mermaid
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 會被**合法的非決定性**淹沒——時間戳、排序、浮點誤差每次都不同，但那些**不算行為改變**。所以先正規化：

1. **排序所有 list**——消掉 `GROUP_CONCAT` / 聚合的順序噪音。
2. **截斷浮點**——吸收 `NOW()` 之類的時間漂移。
3. **數字字串統一**——`"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` |

### 三個致命隱形陷阱

1. **`ON DUPLICATE KEY UPDATE` 的 conflict target 必須對到真的 PRIMARY / UNIQUE KEY**，否則**不觸發、默默插重複**。
2. **`ai_ci` collation 讓 `WHERE name = 'Hotfix'` 也吃到 `'hotfix'`**——unique key 會意外衝突。
3. **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，讓「行為變了」在合併前就紅燈。