SQLite 初學者注意事項

大家好，我是只談技術不剪發的 Tony 老師，

SQL 語言是一個標準，但是沒有任何兩個資料庫管理系統的實作完全相同，每種 SQL 實作都有自己的特性和擴展功能，SQLite 也是如此，本文給大家介紹了 SQLite 和其他資料庫實作的主要差異，可以幫助開發人員開發跨資料庫支持的系統或者實作資料庫的移植，

如果覺得文章有用，歡迎評論📝、點贊👍、推薦🎁

SQLite 是一個嵌入式資料庫引擎

相對于其他資料庫引擎而言，例如 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 或者 SQL Server），可能會因此導致問題，因為 SQLite 對于型別的要求更加寬松，其他資料庫對于型別的要求更加嚴格，

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

沒有 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 字串的中間，這可能會導致例外行為， 具體內容可以參考官方檔案，