SQL 語言是一個標準，但是沒有任何兩個數據庫管理系統的實現完全相同。每種 SQL 實現都有自己的特性和擴展功能，SQLite 也是如此。

本文給大家介紹一下 SQLite 和其他數據庫實現的主要差異，可以幫助開發人員開發跨數據庫支持的系統或者實現數據庫的移植。

SQLite 是一個嵌入式數據庫引擎

相對于其他數據庫而言，例如 Microsoft SQL Server、PostgreSQL、MySQL 或者 Oracle，SQLite 最重要的區別在于它的設計目標不是這類數據庫系統的替代者。SQLite 是一個嵌入到應用程序中的數據庫引擎，沒有服務端，沒有單獨管理數據庫的服務器進程。應用程序通過函數調用和數據庫引擎進程交互，而不是將消息發送給單獨的進程或線程。

客戶端/服務器數據庫對于現代應用系統非常重要，它們專注于可伸縮性、并發性、集中管理和控制等，解決了企業數據的共享存儲問題。SQLite 則致力于為單個應用程序和設備提供本地數據存儲，強調的是經濟、效率、可靠性、獨立性和簡單性。

SQLite 支持的使用場景包括：

• 嵌入式設備和物聯網應用。例如手機、機頂盒、電視、游戲機、照相機、手表、廚房用具、恒溫器、汽車、機床、飛機、遠程傳感器、無人機、醫療設備和機器人等等。

• 桌面應用程序的磁盤文件格式。例如版本控制系統、財務分析工具、媒體編目和編輯套件、CAD 軟件包、記錄保存程序等。

• 中小型網站。對于大多數中小型流量的網站（也就是大多數網站），SQLite 可以作為數據庫引擎。一般來說，每天點擊量少于 10 萬的網站都可以正常運行。這是一個保守的估計，SQLite 已經被證明可以處理 10 倍以上的流量。

• 數據分析。熟悉 SQL 的人員可以使用 sqlite3 命令行工具或者第三方程序來分析大型數據集，包括網站日志分析、體育統計分析、程序編譯指標以及實驗結果分析。

• 企業數據的緩存。很多應用程序使用 SQLite 作為企業 RDBMS 數據的本地緩存，加快了前端響應速度，同時減少了網絡傳輸和核心數據庫服務器上的負載。另外，而且很多情況下，客戶端應用程序可以在網絡中斷期間繼續運行。

• 服務器端數據庫。客戶端發送請求到服務端，服務端將請求轉換為 SQL 查詢，獲取結果并進行處理之后返回客戶端。服務端支持并發訪問，然后將數據庫請求串行化。另外，通過使用多個數據庫文件可以進一步提高并發支持。例如，每個用戶對應一個數據庫，每個數據庫只有一個連接。

• 數據傳輸的文件格式。一個 SQLite 數據庫就是一個壓縮的文件，具有跨平臺支持的格式，因此也可以作為應用傳輸數據的容器。

• 文件歸檔和數據存儲。SQLite 的歸檔功能可以作為 ZIP 歸檔或者 TAR 的替代。

• 替代臨時磁盤文件。很多程序需要使用 fopen()、fread() 和 fwrite() 接口創建和管理專用格式的數據文件，SQLite 非常適合替代這種文件，而且比文件系統讀取更快。

• 內部數據庫或者臨時數據庫。如果程序需要對大量數據進行各種篩選和排序，將數據加載到 SQLite 內存數據庫中進程操作通常比手動編寫代碼進行處理會更簡單快速。這種方式還提供了很好的靈活性，因為可以增加新的字段和索引，而不需要重新對查詢進行編碼。

• 演示或者測試數據庫。客戶端應用通常使用一個通用的數據庫接口（例如 JDBC），可以連接到各種數據庫。在測試或者演示時可以連接到 SQLite 數據文件，而不需要安裝單獨的數據庫服務器。

• 教學和培訓。SQLite 安裝和使用非常簡單，只需要將 sqlite3 或者 sqlite3.exe 復制到指定機器然后允許即可。因此，SQLite 非常適合學習 SQL 語句和分享。

• SQL 擴展特性的試驗。SQLite 具有簡單的模塊化設計，因此非常適合用于新的數據庫語言特性或者理念的試驗。

靈活的數據類型

SQLite 提供了非常靈活的數據類型。其中的關鍵在于 SQLite 對數據庫中數據的類型要求非常寬松。例如，可以將一個字段的類型定義為 INTEGER 之后，插入數據時存儲一個字符串；此時 SQLite 會嘗試將字符串轉換為整數，因此 '1234' 會被轉換為 1234 并存入表中。但是，如果插入一個非數字的字符串（例如 'wxyz'）SQLite 也不會報錯，而是直接存儲一個字符串值。這一點顯然和其他數據庫不同。

與此類似，SQLite 允許將一個 2000 字符長度的字符串插入一個  VARCHAR(50) 類型的字段中；其他數據庫則會報錯或者截斷輸入的字符串。

如果在初始開發過程中使用 SQLite，然后部署上線時替換為其他數據庫（PostgreSQL 或者 Microsoft SQL Server），可能會因此導致問題。因為 SQLite 對于類型的要求更加寬松，其他數據庫對于類型的要求更加嚴格。

這種靈活的數據類型是 SQLite 的一個特性，而不是缺陷。但無論如何，這個特性的確會給熟悉其他數據庫的開發人員帶來一些困惑和痛苦。也許更好的方式是 SQLite 提供一個 ANY 數據類型，開發人員就可以在需要時使用靈活類型，而不是默認使用這種類型。但是，現在改變這一行為會損壞已經使用該功能的數百萬應用程序和數十億個數據庫文件。

SQLite 3.37.0 開始支持嚴格（STRICT）的數據類型，具體參考官方文檔。

沒有 BOOLEAN 數據類型

和其他大多數 SQL 實現不同，SQLite 沒有提供真正的 BOOLEAN 數據類型。TRUE 和 FALSE 通常使用 1 和 0 進行表示。這似乎沒有導致什么問題，但還是應該有所了解。

從 SQLite 3.23.0（2018-04-02）開始 TRUE 和 FALSE 關鍵字分別定義為 1 和 0 的別名。這樣可以更好地兼容其他 SQL 實現。但是為了 向下兼容，如果某個字段名為 TRUE 或者 FALSE，那么這些關鍵字就表示這些字段，而不是布爾常量。

沒有 DATETIME 數據類型

SQLite 沒有提供 DATETIME 數據類型。日期和時間可以使用以下方式進行存儲：

• ISO-8601 格式的 TEXT 字符串，例如 '2018-04-02 12:13:46'。

• 使用 INTEGER 表示自從 1970 年 1 月 1 日以來的秒數（也就是 unix 時間戳），不考慮閏秒。

• 使用 REAL 值表示的 Julian 日期。

SQLite 內置的日期時間函數可以操作和轉換以上格式的日期/時間數據。

字段類型的可選性

由于 SQLite 支持靈活的數據類型，創建表時甚至可以不用指定字段的數據類型。例如：

CREATE TABLE t1(a,b,c,d);

其中，表 t1 包含 4個字段：a、b、c、d，它們都沒有特定的數據類型。因此，我們可以在這些字段中存儲任何數據。

默認不啟用外鍵約束

SQLite 很早就支持了外鍵約束的解析，但是實際上直到 SQLite 3.6.19（2009-10-14）才真正實現了外鍵約束。這個時候已經有數百萬個數據庫包含了外鍵約束，但是實際上的數據并不滿足條件；為了避免破壞那些遺留的數據庫，SQLite 默認關閉了外鍵約束的強制執行。

應用程序可以在運行時通過`PRAGMA foreign_keys`指令啟用外鍵約束，或者在編譯時通過 -DSQLITE_DEFAULT_FOREIGN_KEYS=1 選項啟用。

主鍵可能包含空值

通常來說，SQLite 表中的 PRIMARY KEY 相當于一個 UNIQUE 約束（除了 INTEGER PRIMARY KEY 表和 WITHOUT ROWID 表之外）。由于歷史問題，主鍵字段中的值允許為 NULL。顯然這是一個缺陷，但是等到發現問題的時候存在大量的數據庫依賴于該缺陷，所以最終還是繼續支持這種錯誤行為。

不過，INTEGER PRIMARY KEY 字段的值只能是一個非空的整數， WITHOUT ROWID 表的 PRIMARY KEY 字段也只能存儲非空的數據。

匯總查詢中可以使用非 GROUP BY 字段

對于絕大多數的 SQL 實現，匯總查詢的結果中只能包含聚合函數或者 GROUP BY 分組字段。在匯總查詢中引用其他的字段沒有邏輯意義，因為輸出結果可能是由兩行或者多行數據匯總而成。

但是，SQLite 并不強制這種限制，輸出字段可以是任意表達式，包括非分組字段。這一特性包含兩種使用場景：

• 對于 SQLite 而言，如果匯總查詢中只包含一個 min() 或者 max() 函數，其他字段的值也來自 min() 或者 max() 取值所在的數據行；如果 min() 或者 max() 對應多行數據，隨機返回一行中的值。例如，以下查詢返回了薪水最高的員工：

   SELECT max(salary), first_name, last_name   FROM employee;

 以上查詢中，返回的 first_name 和 last_name 是薪水最高的員工姓名。

• 如果查詢中沒有聚合函數，GROUP BY 可以作為 DISTINCT ON 子句的替代實現。也就是說，基于 GROUP BY 中的字段進行分組，每組返回一行記錄，其他字段則隨機返回一個數據值。

 例如：

   SELECT department_id, first_name, last_name   FROM employees   GROUP BY department_id;

以上查詢為每個部門（department_id）返回一條記錄，同時返回該部門內的一個隨機員工。SQLite 支持 DISTINCT，但是不支持 DISTINCT ON，可以使用這種形式的 GROUP BY 替代。

默認不支持完整的 Unicode 字符大小寫轉換

SQLite 無法區分所有 Unicode 字符的大小寫，SQL 函數（ 例如 upper() 和 lower()）只支持 ASCII 字符。這個問題的存在有兩個原因：

• 雖然現在已經穩定了，但是在最初設計 SQLite 時， Unicode 大小寫對應仍然存在變化。這就意味著每個新的 Unicode 版本的行為可能發生改變，從而破壞使用中的應用程序和數據索引。

• 完全正確的 Unicode 大小寫轉換表比整個 SQLite 庫文件更大。

如果編譯時使用了 -DSQLITE_ENABLE_ICU 選項，SQLite 可以支持完整的 Unicode 大小寫轉換，此時 SQLite 庫文件會鏈接到 ICU 庫文件。

雙引號字符串可以作為常量

按照 SQL 標準，雙引號表示標識符，單引號表示字符串常量。例如：

• "this is a legal SQL column name"

• 'this is an SQL string literal'

SQLite 支持以上兩種形式。但是最初為了兼容 MySQL 3.x，如果雙引號字符串沒有匹配到有效的標識符，SQLite 會將其解釋為字符串常量。

這種行為意味著雙引號中拼寫錯誤的標識符將被解釋為字符串常量，而不是產生一個錯誤。它還會導致那些 SQL 初學者形成使用雙引號字符串常量的不良習慣，而不是使用正確的單引號。仍然是由于存在大量應用使用了雙引號字符串，因此 SQLite 選擇了繼續支持這個功能，避免破壞遺留系統。

從 SQLite 3.27.0（2019-02-07）開始，使用雙引號字符串會在錯誤日志中記錄一個告警信息。

從 SQLite 3.29.0（2019-07-10）開始，雙引號字符串常量功能可以在運行時禁用，只需要為 sqlite3_db_config() 方法設置 SQLITE_DBCONFIG_DQS_DDL 和 SQLITE_DBCONFIG_DQS_DML 參數。參數的默認值也可以在編譯時使用 -DSQLITE_DQS=N 選項。

關鍵字通常可以用作標識符

SQL 語言定義了許多關鍵字，大多數數據庫都不允許使用關鍵字作為標識符（表名或者字段名），除非使用了雙引號。SQLite 對此更加靈活，很多關鍵字可以直接作為標識符使用，只要這些關鍵字不會引起歧義并且上下文需要一個標識符。

例如，以下語句在 SQLite 中有效：

CREATE TABLE union(true INT, with BOOLEAN);

其他數據庫中不允許以上語句，因為 union、true 和 with 都是關鍵字。

這個功能可以提供向下兼容，因為新增關鍵字可能是遺留系統中的標識符。盡管如此，這也可能導致一些困惑。例如：

CREATE TRIGGER AFTER INSERT ON tableX BEGIN  INSERT INTO tableY(b) VALUES(new.a);END;

觸發器的名稱是“AFTER”，它是一個 BEFORE 觸發器。語句中的 AFTER 只能解釋為標識符，而不是關鍵字。

輸入不可靠 SQL 語句時不會產生錯誤或警告

最初的 SQLite 實現遵循伯斯塔爾法則，接受任意的輸入內容。這在最初被認為是很好的設計，一個系統應該接受不可靠的輸入并盡量處理。但是隨后，人們認識到有時候最好嚴格控制輸入的內容，從而在輸入時更容易發現錯誤。

AUTOINCREMENT 和 MySQL 實現不同

SQLite 中的 AUTOINCREMENT 屬性和 MySQL 中的 AUTO_INCREMENT 實現不同。實際上 SQLite 并不推薦使用 autoincrement，因為 INTERGER 主鍵默認就是自動增長，具體可以參考官方文檔。

字符串中允許存在 NUL 字符

NUL 字符（ASCII 編碼 0x00 以及 Unicode 編碼 \u0000）允許出現在 SQLite 字符串的中間，這可能會導致異常行為。