PostgreSQL、MySQL、SQLite、SQL Server、MongoDB 怎麼選?從需求到語法的比較筆記
用商品、訂單、離線筆記與問卷等情境,比較常見資料庫的選型、資料型別、交易與併發特性,再對照自動編號、分頁、Upsert、JSON 查詢等語法差異。
本篇目錄
做離線筆記,我不太想為了存幾篇文字再架一台資料庫伺服器;換成購物網站,就得考慮訂單、商品和會員怎麼互相對照。
前一篇資料庫規劃筆記用 PostgreSQL 談搶票與交易。接著想看看:換成 MySQL,SQL 能直接搬嗎?MongoDB、SQLite 或 Cloudflare D1 又適合用在哪裡?SQL Server 也一起放進來比較,下面簡稱 MSSQL,PostgreSQL 則簡稱 PG。
什麼情境,我會先考慮哪一套?
| 資料庫 | 我會先評估的需求 | 選之前要確認 |
|---|---|---|
| PostgreSQL | 商品、訂單、複雜查詢;需要 jsonb 或 PostGIS |
團隊維運能力,託管平台允許哪些擴充 |
| MySQL | 一般網站、購物系統;已有 MySQL 工具與經驗 | 儲存引擎與設定;這裡以 InnoDB 為準 |
| SQLite | 離線筆記、桌面工具、單機服務 | 同時只能有一個 writer,多裝置同步要另外設計 |
| SQL Server | 公司已有相關資料庫、報表與管理工具 | 版本、版別、授權與相容性層級 |
| MongoDB | 問卷回覆、設定文件,常整份讀寫的巢狀資料 | 跨文件查詢、驗證規則與文件成長方式 |
| Cloudflare D1 | API 已在 Workers,希望用託管 SQL | 容量、用量、交易方式與讀取一致性 |
做訂單系統,我會先從關聯式資料庫開始。團隊沒有既定選擇的話,就拿 PG 試一份設計;如果 MySQL 或 SQL Server 的備份、維運流程都已經有了,我也沒有一定要換掉它的理由。
像問卷回覆,每份答案的結構可能不同,又常整份打開、整份保存,MongoDB 的 BSON 文件就值得考慮。但存一個商品 ID,不代表它會像 SQL 外鍵那樣替你確認商品存在。單份文件也有 16 MiB 上限,會員的歷史訂單不能一直往同一個陣列塞。
SQLite 則嵌在應用程式裡,不必另外架資料庫服務。它也能用於單機 API,是否適合要看寫入競爭與交易時間,不能只看使用者人數。
int、bigint、boolean 等型別,有哪些差別?
這些名稱是「資料型別」。常見的還有文字、日期時間、二進位資料和 JSON;同名型別在不同系統裡,不一定有相同限制。
| 資料庫 | 整數、金額與布林 | 文字、二進位資料 | JSON、識別碼與陣列 |
|---|---|---|---|
| PG | smallint、int、bigint;numeric/decimal;boolean |
varchar、text;bytea |
json、jsonb;uuid;原生陣列 |
| MySQL | TINYINT 到 BIGINT,可用 UNSIGNED;DECIMAL;BOOLEAN 是 TINYINT(1) 別名 |
VARCHAR、TEXT;VARBINARY、BLOB |
原生 JSON;UUID 常存 BINARY(16) 或 CHAR(36) |
| SQLite/D1 | INTEGER;沒有一般內建的固定精度 decimal;布林常存 0/1 |
TEXT;BLOB |
JSON 可存文字;UUID 常存 TEXT 或 BLOB |
| SQL Server | tinyint、smallint、int、bigint;decimal;bit |
varchar、nvarchar;varbinary |
2022 用文字搭配 JSON 函式;2025 有原生 json;uniqueidentifier |
| MongoDB | BSON int32、int64、Decimal128、boolean | string;binary | 文件、array;ObjectId;UUID 通常用 binary subtype 4 |
庫存用 int 通常夠用,長期成長的 ID 則要看是否需要 bigint。但 JavaScript Number 的安全整數範圍比有號 64 位元整數小,大型 ID 在 API 裡用字串傳遞,能避免前端讀錯。
金額可以用合適的 decimal,也能約定以分為單位存整數,例如 1,290.50 元存成 129050。float、double、real 是近似浮點數;SQLite 宣告 DECIMAL(10, 2) 也不會自動得到固定兩位小數的精確儲存。
布林值同樣要看限制。PG 有原生 boolean;MySQL 的別名、SQLite 的整數欄位,都不會光靠名稱就把資料限制成 0/1,必要時加上 CHECK。MongoDB 的 Decimal128 也沒有 SQL DECIMAL(p, s) 那種欄位宣告,固定小數位數仍需驗證。
時間型別尤其容易混淆:PG 的 timestamptz 表示時間點,不保留原始時區名稱;MySQL 的 TIMESTAMP 會按連線時區轉換,DATETIME 沒有相同行為。SQL Server 可用 datetime2 或 datetimeoffset,但它的 timestamp 是資料版本,不是日期。SQLite/D1 常用文字或數值,MongoDB 用 BSON Date;若還需要 Asia/Taipei 這類時區名稱,要另外保存。
SQL 觀念能沿用,語法不一定能直接搬
以下以 PG 18、MySQL 8.4、SQLite 3.45 以上、SQL Server 2022 和 MongoDB 8.x 為基準。D1 採 SQLite 語意,支援範圍另依平台文件確認。
| 操作 | PG | MySQL | SQLite/D1 | SQL Server |
|---|---|---|---|---|
| 自動編號 | GENERATED ... AS IDENTITY |
AUTO_INCREMENT |
INTEGER PRIMARY KEY |
IDENTITY(1, 1) |
| 分頁 | LIMIT 10 OFFSET 20 |
LIMIT 10 OFFSET 20 |
LIMIT 10 OFFSET 20 |
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY |
| Upsert | ON CONFLICT ... DO UPDATE |
ON DUPLICATE KEY UPDATE |
ON CONFLICT ... DO UPDATE |
可用 MERGE,需確認鎖定與併發行為 |
| JSON 取文字值 | specs ->> 'color' |
JSON_UNQUOTE(JSON_EXTRACT(specs, '$.color')) |
json_extract(specs, '$.color') |
JSON_VALUE(specs, '$.color') |
例如找有庫存的商品,PG、MySQL 和 SQLite 可以這樣分頁:
SELECT id, name FROM products
WHERE stock > 0
ORDER BY id
LIMIT 10 OFFSET 20;
SQL Server 要把最後一行換成上表的 OFFSET ... FETCH。MongoDB 則使用查詢 API:
db.products
.find({ stock: { $gt: 0 } })
.sort({ _id: 1 })
.skip(20)
.limit(10);
分頁要有明確排序;很後面的頁碼可能需要掃過大量資料,這時可以評估游標分頁。Upsert 也要有對應的唯一限制或索引,不能只寫「先查沒有,再新增」就忽略同時送來的請求。
庫存競爭與 D1 的交易方式
兩個人搶最後一件商品時,可以把庫存條件放進更新:
UPDATE products SET stock = stock - 1
WHERE sku = 'P101' AND stock > 0;
程式必須檢查實際更新筆數,再讓扣庫存和新增訂單一起成功或失敗。PG、MySQL InnoDB、SQLite 和 SQL Server 都支援交易;MongoDB 的單文件更新具原子性,多文件交易則需要 replica set 或 sharded cluster 等部署條件。
D1 由 Cloudflare 管理,透過 Workers binding 或 HTTP API 存取。batch() 能依序執行一批 SQL,發生 SQL 錯誤時整批回滾;但「更新了 0 筆」通常不是 SQL 錯誤,缺貨邏輯仍得自己處理,不能以為放進 batch 就完成了交易設計。
D1 的寫入會送到主要資料庫,讀取副本的一致性由 Sessions API 管理。它還有容量、查詢時間及讀寫額度限制;Workers binding 不支援 JavaScript BigInt,大型 ID 可以用文字欄位,或先 CAST(id AS TEXT) 再讀回。已有 PG 或 MySQL 的專案,也可以評估 Hyperdrive,讓 Workers 連接原本的資料庫。
選好之後,還是得拿自己的查詢試一次。兩個請求搶最後一件商品,是否只有一筆成功?訂單沒存成,庫存有沒有還原?我也會實際試著還原備份,確認資料真的能取回來。
語法速查與相關資料
- PostgreSQL:SQL 指令、
psqlhelp - MySQL:SQL statements、client 指令
- SQLite:SQL 語法、CLI 指令
- SQL Server:T-SQL reference
- MongoDB:Shell Cheat Sheet
- Cloudflare D1:SQL/PRAGMA、Prepared statement API
各系統的型別、交易與平台限制文件
MongoDB:MongoDB 的文件結構說明、$lookup、官方資料建模說明、16 MiB 的大小限制、BSON 型別、mongosh 資料型別文件、schema validation、MongoDB 的 skip() 說明、updateOne() 的 Upsert 說明、唯一索引文件、原子更新文件、MongoDB 的交易文件、mongosh 方法索引、mongosh 內建 help。
Cloudflare D1 與 Workers:D1 的介紹、D1 的型別轉換與 BigInt 限制、D1 的回傳結果說明、D1 的外鍵說明、JSON 函式速查、D1 的備份與復原文件、D1 的 batch() 說明、讀取副本的一致性說明、容量與查詢限制、價格與使用量、Hyperdrive、batch() 與 session。
MySQL:InnoDB、資料型別、MySQL 整數型別、MySQL 的自動編號文件、MySQL 的官方說明、MySQL JSON 型別、MySQL 的 JSON 索引方式、MySQL 的大小寫規則、DATETIME/TIMESTAMP 的差異。
PostgreSQL:PostGIS 的空間資料與查詢能力、資料型別、PostgreSQL 數值型別、PostgreSQL 的陣列設計提醒、Identity column、PostgreSQL 的 INSERT ... RETURNING、PostgreSQL 的分頁說明、PostgreSQL 的衝突處理、PostgreSQL JSON 型別、PostgreSQL 的 JSON 索引說明、PostgreSQL 的比對運算、日期時間型別。
SQLite:SQLite 的適用情境說明、型別與 affinity、SQLite 的自動編號說明、STRICT tables 文件、SQLite 的 RETURNING、SQLite 的 UPSERT、SQLite JSON 函式、SQLite 的隔離與併發說明、WAL 的限制、SQLite 的 LIKE、SQLite 的外鍵文件。
SQL Server:資料型別、SQL Server 整數型別、IDENTITY、新增後取值的 OUTPUT、排序與分頁、MERGE 的併發注意事項、版本與 JSON 功能說明、JSON_VALUE、SQL Server 的 JSON 索引方式、SQL Server 的 collation、rowversion 文件。
JavaScript 與其他資料庫:JavaScript 的安全整數範圍、BigInt 的 JSON 序列化說明、MariaDB 的 JSON 文件、Redis 的資料型別說明、DuckDB 的介紹。
站內相關文章: