DDL 難的不是語法，而是方言差異

CREATE TABLE 的語法本身很單純。之所以每次都要重新查，是因為同一個欄位在 MySQL、PostgreSQL、SQLite 得寫成不同樣子。「在 MySQL 跑得好好的 DDL，搬到 PostgreSQL 就在 AUTO_INCREMENT 出現語法錯誤」「本機 SQLite 通過的 CHECK 約束，到了正式環境根本沒生效」——DDL 的意外幾乎都能歸結到方言差異。

這篇文章是以跨三種資料庫的型別與約束對照表為主的參考手冊。談的不是設計本身（正規化或索引策略），而是把已經決定好的設計怎麼寫出來。

CREATE TABLE 的骨架

先看在三種資料庫都能通過的最小定義：

CREATE TABLE users (
    id          INTEGER PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    name        VARCHAR(100),
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

欄位定義依照 欄位名 → 型別 → 約束 的順序排列。約束分成欄位約束（像上面那樣寫在型別後面）與資料表約束（所有欄位定義完之後統一撰寫）兩種，複合主鍵與複合唯一鍵只能用資料表約束表達。

CREATE TABLE order_items (
    order_id   INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity   INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id),                      -- 複合主鍵
    FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products (id)
);

型別對照表

依用途列出三種資料庫實際該指定的型別。

用途MySQLPostgreSQLSQLite
整數（一般）INTINTEGERINTEGER
整數（大）BIGINTBIGINTINTEGER
金額、精確小數DECIMAL(10,2)NUMERIC(10,2)NUMERIC
短字串VARCHAR(255)VARCHAR(255) 或 TEXTTEXT
長文字TEXTTEXTTEXT
布林BOOLEAN（實際是 TINYINT(1)）BOOLEANINTEGER（0/1）
僅日期DATEDATETEXT
日期時間DATETIMETIMESTAMPTZTEXT
JSONJSONJSONBTEXT
UUIDCHAR(36) 或 BINARY(16)UUIDTEXT

型別選擇上有三點值得記住。

SQLite 幾乎沒有型別。 SQLite 是以值為單位的動態型別，欄位的型別宣告只是「型別親和性（type affinity）」的提示。寫 BOOLEAN 或 DATETIME 不會出錯，但內部只會存成數值或文字。這正是針對 SQLite 的 DDL 需要獨立方言轉換的主因。

PostgreSQL 的 VARCHAR(n) 沒有效能上的好處。 在 PostgreSQL 中 TEXT 與 VARCHAR 的實作相同，長度限制比較接近 CHECK 約束。不像 MySQL，短並不代表快；沒有明確的業務上限時用 TEXT 就好。

金額不要用 FLOAT / DOUBLE。 浮點數無法精確表示十進位小數，合計金額會偏差。請使用 DECIMAL / NUMERIC。

自動編號在三種方言中是完全不同的功能

這是日常 DDL 中方言差異最大的地方。

資料庫寫法
MySQLid INT NOT NULL AUTO_INCREMENT PRIMARY KEY
PostgreSQL（建議）id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY
PostgreSQL（舊式）id SERIAL PRIMARY KEY
SQLiteid INTEGER PRIMARY KEY

PostgreSQL 建議用 IDENTITY 取代 SERIAL。 SERIAL 是「INTEGER 型別＋在背後建立 sequence」的偽型別；PostgreSQL 10 之後可以使用 SQL 標準的 GENERATED ... AS IDENTITY。IDENTITY 讓 sequence 的擁有關係正確綁定在資料表上，刪除資料表時的收尾與權限管理都比較單純。

SQLite 的 AUTOINCREMENT 通常不需要。 只要寫成 INTEGER PRIMARY KEY，該欄位就成為內部 rowid 的別名，省略時會自動編號。加上 AUTOINCREMENT 關鍵字只多了「已刪除的 ID 絕不重複使用」的保證，代價是對 sqlite_sequence 資料表的額外寫入而變慢。官方文件也建議除非真的需要，否則避免使用。另外 AUTOINCREMENT 若加在 INTEGER PRIMARY KEY 以外的地方會是語法錯誤。

約束對照表與行為差異

約束意義方言上的注意事項
PRIMARY KEY唯一且不可為 NULL隱含 NOT NULL；每張表一個
NOT NULL禁止 NULL幾乎沒有差異
UNIQUE禁止重複值NULL 不算重複（多列都可以是 NULL）
DEFAULT省略時的值MySQL 的 TEXT/JSON 預設值運算式需要括號（8.0.13 以後）
CHECK只允許符合條件的值MySQL 8.0.16 之前只解析語法、完全忽略
FOREIGN KEY參照完整性SQLite 預設停用；InnoDB 以外的 MySQL 引擎會忽略

其中最容易釀成事故的是下面兩項。

MySQL 的 CHECK 約束：MySQL 8.0.16 之前的版本會接受 CHECK 子句的語法，卻完全不套用為約束。這是很安靜的失效模式：DDL 上寫著規則，不合法的資料卻持續寫入，往往幾個月後才有人發現。若 schema 可能跑在舊版伺服器上，請把應用程式端的驗證視為唯一真相來源。

SQLite 的外鍵：為了向後相容，SQLite 預設不強制外鍵約束，而且這個設定是以連線為單位，因此每次連線都要執行：

PRAGMA foreign_keys = ON;

忘了這行，即使 DDL 寫了 REFERENCES，參照到不存在父列的資料照樣插得進去。若是本機 SQLite、正式環境 PostgreSQL 的組合，就會變成「只有開發機的資料完整性壞掉」這種棘手情況。

外鍵的 ON DELETE / ON UPDATE

被參照的列被刪除或更新時，行為有四種。

選項行為
RESTRICT / NO ACTION只要子列還在就拒絕刪除／更新父列（預設）
CASCADE隨父列一起刪除／更新子列
SET NULL把子列的外鍵欄位設為 NULL（該欄位必須允許 NULL）
SET DEFAULT把子列設為預設值（MySQL/InnoDB 不支援）
-- 刪除使用者時一併刪除其貼文，但留下留言只是作者不明
CREATE TABLE posts (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE comments (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users (id) ON DELETE SET NULL  -- 不能設成 NOT NULL
);

CASCADE 很方便，但副作用是「會刪掉多少東西」不讀 DDL 就看不出來。稽核紀錄、銷售明細這類「父資料消失也要保留」的資料表請不要使用。

日期時間欄位怎麼選

想做的事MySQLPostgreSQL
自動填入建立時間TIMESTAMP DEFAULT CURRENT_TIMESTAMPTIMESTAMPTZ DEFAULT NOW()
自動更新修改時間TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP需要觸發器（沒有欄位層級的功能）

ON UPDATE CURRENT_TIMESTAMP 是 MySQL 獨有的功能。搬到 PostgreSQL 時必須改用 BEFORE UPDATE 觸發器，直接複製 DDL 會讓 updated_at 停在寫入當下的值，而且不會有任何錯誤提示。

在 MySQL 選日期時間型別時，要考慮 TIMESTAMP 只能保存 1970～2038 年且會依連線時區轉換，而 DATETIME 範圍較廣且不做時區轉換。在 PostgreSQL 則建議優先使用 TIMESTAMPTZ，而非不帶時區的 TIMESTAMP。

MySQL 的字元編碼是 utf8mb4，不是 utf8

由於歷史因素，MySQL 的 utf8 是每個字元最多只有 3 位元組的另一種編碼，放入表情符號或部分漢字就會出現 Incorrect string value 錯誤。要明確指定時請使用 utf8mb4：

CREATE TABLE posts (
    id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    body  TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

PostgreSQL 與 SQLite 是在資料庫／檔案層級處理 UTF-8，資料表定義不需要特別交代。

把設計直接變成 DDL 與 ER 圖

型別與約束決定之後，用手寫出三種方言只是單純的重工。DDL 產生器 只要在畫面上組裝資料表與欄位，就能同時產生符合 MySQL / PostgreSQL / SQLite 各自格式的 CREATE TABLE 語句，以及 Mermaid 格式的 ER 圖。也可以貼上 API 回應或日誌之類的 JSON 範例，自動推論欄位結構，適合從既有資料反推 schema。方言差異都由工具吸收，不必每次回來翻這份對照表。

如果已經有 DDL，貼進 SQL 轉 ER 圖 就能把資料表之間的參照關係看成圖。資料表齊全之後，可以用 Visual SQL Builder 組裝 SELECT 語句。各種 JOIN 的結果差異整理在 SQL JOIN 完整參考，ER 圖的語法本身則整理在 Mermaid ER 圖語法參考。這些工具全都在瀏覽器內完成處理，輸入的 schema 資訊不會傳送到外部。

總結

  • 型別上記住三點：SQLite 只有型別親和性、PostgreSQL 的 VARCHAR(n) 沒有速度優勢、金額用 DECIMAL
  • 自動編號是 AUTO_INCREMENT / GENERATED ALWAYS AS IDENTITY / INTEGER PRIMARY KEY 三種完全不同的功能。PostgreSQL 新設計優先用 IDENTITY 而非 SERIAL
  • MySQL 8.0.16 之前的 CHECK 會被忽略；SQLite 的外鍵需要每次連線執行 PRAGMA foreign_keys = ON
  • 使用 ON DELETE SET NULL 的欄位不能設成 NOT NULL
  • ON UPDATE CURRENT_TIMESTAMP 是 MySQL 專用，PostgreSQL 要用觸發器替代
  • MySQL 的字元編碼是 utf8mb4，不是 utf8

常見問題

VARCHAR 和 TEXT 該用哪一個？

依資料庫而定。PostgreSQL 兩者的實作幾乎相同，沒有明確的字數上限需求時用 TEXT 就好。MySQL 的 VARCHAR 存在資料列本身，而 TEXT 可能存放在別的區塊，因此經常搜尋與排序的短字串用 VARCHAR(n) 比較有利。SQLite 不論寫哪一種，內部處理都一樣。

SQLite 需要加上 AUTOINCREMENT 嗎？

通常不需要。寫成 INTEGER PRIMARY KEY 時，省略值就會自動編號。加上 AUTOINCREMENT 只多了「不重複使用已刪除 ID」的保證，代價是對管理用資料表的額外寫入而變慢。只有在絕對不能重複使用已發出 ID（例如對外公開的 ID）時才需要考慮。

CREATE TABLE 裡寫的 CHECK 約束沒有生效

如果使用 MySQL，請確認版本是否早於 8.0.16——舊版只接受 CHECK 的語法卻不會套用。可以執行 SELECT VERSION(); 確認。在 SQLite 中 CHECK 是有效的，但外鍵約束預設停用，需要在每次連線執行 PRAGMA foreign_keys = ON;。

貼上的 DDL 會被送到伺服器嗎？

不會。DDL 產生器 與 SQL 轉 ER 圖 都在瀏覽器內完成處理，輸入的資料表定義與 schema 資訊不會傳送到任何地方。