在 SQLite 中偏好使用 STRICT 表

在 SQLite 中偏好使用 STRICT 表

使用 STRICT 表來強制執行資料完整性

SQLite 的 STRICT 表透過強制執行嚴格的型別，防止常見的資料完整性問題，確保只有宣告的資料型別才能儲存在欄位中。藉由在 CREATE TABLE 語句的末尾附加 STRICT，開發者可以阻止 SQLite 允許將文字插入到整數欄位中，或發生非嚴格表預設會出現的其他型別不匹配問題。

-- Non-strict tables allow any type in any column
CREATE TABLE people_nonstrict (age INTEGER);
INSERT INTO people_nonstrict (age) VALUES ('garbage'); -- Works fine

-- Strict tables enforce the declared type
CREATE TABLE people_strict (age INTEGER) STRICT;
INSERT INTO people_strict (age) VALUES ('garbage'); -- Error: cannot store TEXT value in INTEGER column

無損轉換仍然是被允許的；例如，字串 '123' 可以儲存在 INTEGER 欄位中，因為它可以被完美地轉換為整數。

防止無效的欄位定義

嚴格表消除了建立具有不存在或拼寫錯誤的資料型別欄位的可能性。在標準的 SQLite 表中，像 DATETIME、JSON 或 UUID 這樣的型別是被接受的，儘管它們並非 SQLite 的原生型別。在 STRICT 表中，僅允許以下型別：

  • INT / INTEGER
  • REAL
  • TEXT
  • BLOB
  • ANY

在建立表時嘗試使用任何其他型別名稱，將導致錯誤，從而防止拼寫錯誤和對 SQLite 支援的型別的誤解。

ANY 型別的靈活性

對於需要欄位保持靈活性的場景——例如鍵值儲存 (key-value store) 或追蹤不同欄位型別變動的稽核表 (audit table)——可以在嚴格表中使用 ANY 型別。這允許欄位接受任何資料型別，同時表中的其餘部分保持嚴格的型別限制。

CREATE TABLE tbl (value ANY) STRICT;
INSERT INTO tbl (value) VALUES (123);
INSERT INTO tbl (value) VALUES ('text');

STRICT 表的權衡與限制

雖然 STRICT 表提高了可預測性，但它們也帶來了特定的技術限制：

版本要求與相容性

嚴格表是在 SQLite version 3.37.0 (November 2021) 引入的。包含嚴格表的資料庫無法被早於 3.37.0 的 SQLite 版本讀取，這為使用舊版 SQLite 安裝的環境造成了潛在的相容性障礙。

遷移挑戰

現有的表無法透過 ALTER TABLE 命令轉換為嚴格表。將非嚴格表遷移到嚴格表需要建立一個新的嚴格表並將資料複製過去：

  1. 使用相同的 schema 建立一個新的嚴格表。
  2. 使用 INSERT INTO ... SELECT * FROM ... 複製資料。
  3. 刪除舊表並將新表重新命名。

如果現有的資料包含型別不匹配，遷移將會失敗，這需要開發者先進行資料清理或轉型 (cast)。

效能考量

嚴格表會引入少量的額外開銷 (overhead)，因為 SQLite 必須在 INSERT 和 UPDATE 操作期間驗證資料型別。然而，實證測試顯示這種開銷在大多數實際應用中是可以忽略不計的，且有人認為避免欄位親和性 (affinity) 不匹配可能會潛在性地提高效能。

社群對靈活性 vs. 嚴格型別的觀點

SQLite 開發者與許多使用者之間在靈活性型別的效用上存在顯著分歧。官方 SQLite 文檔檔說明靈活性型別是一個優勢，認為它能防止在匯入雜亂資料（如 CSVs）時發生資料損失，並使某些類型的錯誤更容易被發現。

社群成員表達了不同的觀點：

"I’ve personally encountered many bugs where an unexpected data type caused subtle headaches. I’d much rather these mistakes explode loudly."

"If one application stores a string into a numeric column that breaks everyone else... the main use case for SQLite is embedded databases... the application's code knows what to expect in each column."

相反地，有些使用者認為嚴格模式限制了使用更有意義的欄位型別名稱的能力，以便於在應用程式層進行映射 (mapping)（例如，使用 Rust 的 sqlx crate）。

對於那些由於版本限制或需要特定約束而無法使用 STRICT 表的人，可以使用 CHECK 約束來作為手動替代方案，以強制執行資料正確性。

Sources