SQLite 初學(xué)者注意事項(xiàng)

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




文章目錄

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

大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。

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

如果覺(jué)得文章有用，歡迎評(píng)論??、點(diǎn)贊??、推薦??
SQLite 是一個(gè)嵌入式數(shù)據(jù)庫(kù)引擎

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

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

SQLite 支持的使用場(chǎng)景包括：

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

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

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

與此類似，SQLite 允許將一個(gè) 2000 字符長(zhǎng)度的字符串插入一個(gè) VARCHAR(50) 類型的字段中；其他數(shù)據(jù)庫(kù)則會(huì)報(bào)錯(cuò)或者截?cái)噍斎氲淖址?br>
如果在初始開發(fā)過(guò)程中使用 SQLite，然后部署上線時(shí)替換為其他數(shù)據(jù)庫(kù)（PostgreSQL 或者 SQL Server），可能會(huì)因此導(dǎo)致問(wèn)題。因?yàn)?SQLite 對(duì)于類型的要求更加寬松，其他數(shù)據(jù)庫(kù)對(duì)于類型的要求更加嚴(yán)格。

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

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

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

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

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

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

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

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



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

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

應(yīng)用程序可以在運(yùn)行時(shí)通過(guò)PRAGMA foreign_keys指令啟用外鍵約束，或者在編譯時(shí)通過(guò) -DSQLITE_DEFAULT_FOREIGN_KEYS=1 選項(xiàng)啟用。
主鍵可能包含空值

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

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

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

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

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

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


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

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

    例如：

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


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

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

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

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

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

按照 SQL 標(biāo)準(zhǔn)，雙引號(hào)表示標(biāo)識(shí)符，單引號(hào)表示字符串常量。例如：

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

SQLite 支持以上兩種形式。但是最初為了兼容 MySQL 3.x，如果雙引號(hào)字符串沒(méi)有匹配到有效的標(biāo)識(shí)符，SQLite 會(huì)將其解釋為字符串常量。

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

從 SQLite 3.27.0（2019-02-07）開始，使用雙引號(hào)字符串會(huì)在錯(cuò)誤日志中記錄一個(gè)告警信息。

從 SQLite 3.29.0（2019-07-10）開始，雙引號(hào)字符串常量功能可以在運(yùn)行時(shí)禁用，只需要為 sqlite3_db_config() 方法設(shè)置 SQLITE_DBCONFIG_DQS_DDL 和 SQLITE_DBCONFIG_DQS_DML 參數(shù)。參數(shù)的默認(rèn)值也可以在編譯時(shí)使用 -DSQLITE_DQS=N 選項(xiàng)。
關(guān)鍵字通?？梢杂米鳂?biāo)識(shí)符

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

例如，以下語(yǔ)句在 SQLite 中有效：

CREATE TABLE union(true INT, with BOOLEAN);



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

這個(gè)功能可以提供向下兼容，因?yàn)樾略鲫P(guān)鍵字可能是遺留系統(tǒng)中的標(biāo)識(shí)符。盡管如此，這也可能導(dǎo)致一些困惑。例如：

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



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

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

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

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