SQLite 初學者注意事項

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

        SQLite 是一個嵌入式數(shù)據(jù)庫引擎
        靈活的數(shù)據(jù)類型
            沒有 BOOLEAN 數(shù)據(jù)類型
            沒有 DATETIME 數(shù)據(jù)類型
            字段類型的可選性
        默認不啟用外鍵約束
        主鍵可能包含空值
        匯總查詢中可以使用非 GROUP BY 字段
        默認不支持完整的 Unicode 字符大小寫轉(zhuǎn)換
        雙引號字符串可以作為常量
        關(guān)鍵字通?？梢杂米鳂俗R符
        輸入不可靠 SQL 語句時不會產(chǎn)生錯誤或警告
        AUTOINCREMENT 和 MySQL 實現(xiàn)不同
        字符串中允許存在 NUL 字符

SQL 語言是一個標準，但是沒有任何兩個數(shù)據(jù)庫管理系統(tǒng)的實現(xiàn)完全相同。每種 SQL 實現(xiàn)都有自己的特性和擴展功能，SQLite 也是如此。本文給大家介紹了 SQLite 和其他數(shù)據(jù)庫實現(xiàn)的主要差異，可以幫助開發(fā)人員開發(fā)跨數(shù)據(jù)庫支持的系統(tǒng)或者實現(xiàn)數(shù)據(jù)庫的移植。

SQLite 是一個嵌入式數(shù)據(jù)庫引擎

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

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

SQLite 支持的使用場景包括：

    嵌入式設備和物聯(lián)網(wǎng)應用。例如手機、機頂盒、電視、游戲機、照相機、手表、廚房用具、恒溫器、汽車、機床、飛機、遠程傳感器、無人機、醫(yī)療設備和機器人等等。
    桌面應用程序的磁盤文件格式。例如版本控制系統(tǒng)、財務分析工具、媒體編目和編輯套件、CAD 軟件包、記錄保存程序等。
    中小型網(wǎng)站。對于大多數(shù)中小型流量的網(wǎng)站（也就是大多數(shù)網(wǎng)站），SQLite 可以作為數(shù)據(jù)庫引擎。 一般來說，每天點擊量少于 10 萬的網(wǎng)站都可以正常運行。這是一個保守的估計，SQLite 已經(jīng)被證明可以處理 10 倍以上的流量。
    數(shù)據(jù)分析。熟悉 SQL 的人員可以使用 sqlite3 命令行工具或者第三方程序來分析大型數(shù)據(jù)集，包括網(wǎng)站日志分析、體育統(tǒng)計分析、程序編譯指標以及實驗結(jié)果分析。
    企業(yè)數(shù)據(jù)的緩存。很多應用程序使用 SQLite 作為企業(yè) RDBMS 數(shù)據(jù)的本地緩存，加快了前端響應速度，同時減少了網(wǎng)絡傳輸和核心數(shù)據(jù)庫服務器上的負載。另外，而且很多情況下，客戶端應用程序可以在網(wǎng)絡中斷期間繼續(xù)運行。
    服務器端數(shù)據(jù)庫?？蛻舳税l(fā)送請求到服務端，服務端將請求轉(zhuǎn)換為 SQL 查詢，獲取結(jié)果并進行處理之后返回客戶端。服務端支持并發(fā)訪問，然后將數(shù)據(jù)庫請求串行化。另外，通過使用多個數(shù)據(jù)庫文件可以進一步提高并發(fā)支持。例如，每個用戶對應一個數(shù)據(jù)庫，每個數(shù)據(jù)庫只有一個連接。
    數(shù)據(jù)傳輸?shù)奈募袷?。一個 SQLite 數(shù)據(jù)庫就是一個壓縮的文件，具有跨平臺支持的格式，因此也可以作為應用傳輸數(shù)據(jù)的容器。
    文件歸檔和數(shù)據(jù)存儲。SQLite 的歸檔功能可以作為 ZIP 歸檔或者 TAR 的替代。
    替代臨時磁盤文件。很多程序需要使用 fopen()、fread() 和 fwrite() 接口創(chuàng)建和管理專用格式的數(shù)據(jù)文件，SQLite 非常適合替代這種文件，而且比文件系統(tǒng)讀取更快。
    內(nèi)部數(shù)據(jù)庫或者臨時數(shù)據(jù)庫。如果程序需要對大量數(shù)據(jù)進行各種篩選和排序，將數(shù)據(jù)加載到 SQLite 內(nèi)存數(shù)據(jù)庫中進程操作通常比手動編寫代碼進行處理會更簡單快速。這種方式還提供了很好的靈活性，因為可以增加新的字段和索引，而不需要重新對查詢進行編碼。
    演示或者測試數(shù)據(jù)庫?？蛻舳藨猛ǔＪ褂靡粋€通用的數(shù)據(jù)庫接口（例如 JDBC），可以連接到各種數(shù)據(jù)庫。在測試或者演示時可以連接到 SQLite 數(shù)據(jù)文件，而不需要安裝單獨的數(shù)據(jù)庫服務器。
    教學和培訓。SQLite 安裝個使用非常簡單，只需要將 sqlite3 或者 sqlite3.exe 復制到指定機器然后允許即可。因此，SQLite 非常適合學習 SQL 語句和分享。
    SQL 擴展特性的試驗。SQLite 具有簡單的模塊化設計，因此非常適合用于新的數(shù)據(jù)庫語言特性或者理念的試驗。

靈活的數(shù)據(jù)類型

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

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

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

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

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

從 SQLite 3.23.0（2018-04-02）開始 TRUE 和 FALSE 關(guān)鍵字分別定義為 1 和 0 的別名。這樣可以更好地兼容其他 SQL 實現(xiàn)。但是為了 向下兼容，如果某個字段名為 TRUE 或者 FALSE，那么這些關(guān)鍵字就表示這些字段，而不是布爾常量。
沒有 DATETIME 數(shù)據(jù)類型

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

    ISO-8601 格式的 TEXT 字符串，例如 ‘2018-04-02 12:13:46’。
    使用 INTEGER 表示自從 1970 年 1 月 1 日以來的秒數(shù)（也就是 unix 時間戳），不考慮閏秒。
    使用 REAL 值表示的 Julian 日期。

SQLite 內(nèi)置的日期時間函數(shù)可以操作和轉(zhuǎn)換以上格式的日期/時間數(shù)據(jù)。
字段類型的可選性

由于 SQLite 支持靈活的數(shù)據(jù)類型，創(chuàng)建表時甚至可以不用指定字段的數(shù)據(jù)類型。例如：

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

    

其中，表 t1 包含 4個字段：a、b、c、d，它們都沒有特定的數(shù)據(jù)類型。因此，我們可以在這些字段中存儲任何數(shù)據(jù)。
默認不啟用外鍵約束

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

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

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

不過，INTEGER PRIMARY KEY 字段的值只能是一個非空的整數(shù)， WITHOUT ROWID 表的 PRIMARY KEY 字段也只能存儲非空的數(shù)據(jù)。
匯總查詢中可以使用非 GROUP BY 字段

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

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

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

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

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

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

    例如：

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

    以上查詢?yōu)槊總€部門（department_id）返回一條記錄，同時返回該部門內(nèi)的一個隨機員工。SQLite 支持 DISTINCT，但是不支持 DISTINCT ON，可以使用這種形式的 GROUP BY 替代。

默認不支持完整的 Unicode 字符大小寫轉(zhuǎn)換

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

    雖然現(xiàn)在已經(jīng)穩(wěn)定了，但是在最初設計 SQLite 時， Unicode 大小寫對應仍然存在變化。這就意味著每個新的 Unicode 版本的行為可能發(fā)生改變，從而破壞使用中的應用程序和數(shù)據(jù)索引。
    完全正確的 Unicode 大小寫轉(zhuǎn)換表比整個 SQLite 庫文件更大。

如果編譯時使用了 -DSQLITE_ENABLE_ICU 選項，SQLite 可以支持完整的 Unicode 大小寫轉(zhuǎn)換，此時 SQLite 庫文件會鏈接到 ICU 庫文件。
雙引號字符串可以作為常量

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

    “this is a legal SQL column name”
    ‘this is an SQL string literal’

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

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

從 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 參數(shù)。參數(shù)的默認值也可以在編譯時使用 -DSQLITE_DQS=N 選項。
關(guān)鍵字通常可以用作標識符

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

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

CREATE TABLE union(true INT, with BOOLEAN);


其他數(shù)據(jù)庫中不允許以上語句，因為 union、true 和 with 都是關(guān)鍵字。

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

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


觸發(fā)器的名稱是“AFTER”，它是一個 BEFORE 觸發(fā)器。語句中的 AFTER 只能解釋為標識符，而不是關(guān)鍵字。
輸入不可靠 SQL 語句時不會產(chǎn)生錯誤或警告

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

SQLite 中的 AUTOINCREMENT 屬性和 MySQL 中的 AUTO_INCREMENT 實現(xiàn)不同。實際上 SQLite 并不推薦使用 autoincrement，因為 INTERGER 主鍵默認就是自動增長，具體可以參考官方文檔。
字符串中允許存在 NUL 字符

NUL 字符（ASCII 編碼 0x00 以及 Unicode 編碼 \u0000）允許出現(xiàn)在 SQLite 字符串的中間，這可能會導致異常行為。 具體內(nèi)容可以參考官方文檔。