微信中如何使用SQLite 保存聊天記錄

作者：Java碼農

SQLite “只是”一個庫，它不是傳統意義上的服務器。因此，在某些場合下，它確實不合適。但是，在相當多的其他場合，它卻是最合適的選擇。SQLite 號稱是部署和使用最廣泛的數據庫引擎。我認為這很有可能，因為 SQLite 沒有版權的限制。無論何時，只要開發者想使用 SQL 在文件中存儲結構化的數據，SQLite 應是首選方案。

SQLite 的 SQL 方言也非常強大。它比 MySQL 早四年就開始支持 with 語句。最近，它還實現了對于窗口函數的支持，這僅僅比 MySQL 晚五個月。

接下來，本文將介紹 SQLite 在 2018 年新增加的 SQL 功能，也就是 SQLite 從版本 3.22.0 到 3.26.0 所新增加的 SQL 功能。

具體內容包括：

1. ?布爾字面量和判斷

2. ?窗口函數

3. ?Filter子句

4. ?Insert … on conflict (“Upsert”)

5. ?重命名列

6. ?在Modern-SQL.com上接下來

布爾變量和判斷

SQLite支持“假”布爾值：它接受Boolean作為類型的名稱，但它將其當作整數看待（這一點非常類似于MySQL）。真值true和false分別由數值1和0表示（這一點和C語言一樣）。

從版本3.23.0開始，SQLite將關鍵字true和false分別用數字1和0表示，并支持is [not] true | false的判斷語句。現在，它不再支持關鍵字unknown。開發者可以使用空值null來代替，因為unknown和null的布爾值是一樣的。

在INSERT和UPDATE語句中，字面量true和false可以大大提高values和set子句的可讀性。

is [not] true | false這個判斷語句很有用，它與比較操作的含義不一樣：

我們來比較一下

WHERE?c?<>?FALSE?

和

WHERE?c?IS?NOT?FALSE

在上面的例子中，如果c是null, 那么c <> false的結果是unknown.

這是因為WHERE子句只接受結果為true的值，它會過濾掉結果為false或unknown的值。這樣，它就會把對應的行從結果中去掉。

與此相對應，如果c是null，那么，c is not false的判斷結果是true。因此，第二個WHERE子句也將包含c是null的行。

要達到同樣的效果，您可以采用的另外一種方法是增加單獨處理null值的子句。也就是使用語句:

WHERE?c?<>?FALSE ???OR?c?IS?NULL

這種形式的語句更長并且有一些冗余語句（c被使用了兩次）。長話短說，可以使用is not false判斷來替代這個or…is-null的語句。更詳細的內容，請參考“Binary Decisions Based on Three-Valued Results”。

SQLite中對布爾字面量和布爾判斷的支持現在和其他開源數據庫接近，唯一的差距是SQLite不支持is[not] unknown（你可以使用is [not] null來代替）。有趣的是，這些功能在下面提到的商用產品中還不可用。

0：只支持true,false.不支持notknown，如果需要，用null代替1：不支持is [not] unknown,如果需要，用is [not] null代替

窗口函數

SQLite 3.25.0引入了窗口函數。如果你知道窗口函數，那么也知道這是一件大事。如果你不了解窗口功能，請你自己學習如何使用。這篇文章不會具體解釋窗口函數，但請相信：它是最重要的“現代”SQL特性。

SQLite對over子句的支持與其他數據庫非常接近。唯一值得注意的限制是range語句不支持數字或間隔距離（僅支持current row和unbounded preceding|following）。在發布sqlite 3.25.0時,SQL Server和PostgreSQL具有同樣的限制。PostgreSQL 11消除了這一限制。

0：沒有變化
1：Range范圍定義不支持datetime類型
2：Range范圍不接受關鍵字 (只支持unbounded和current row)

SQLite對于窗口函數的支持在業界是領先的。它不支持的功能在其他一些主要產品中也同樣不支持（在聚合中語句中的distinct，width_bucket， respect|ignore nulls和from first|last等語句）。

0：同樣沒有ORDER BY 語句
1：不允許負偏移量，nulls的特定處理：lead(, 'IGNORE NULLS')，這里是字符串參數
2：沒有缺省值(第三個參數)，不支持respect|ignore nulls語句
3：不允許負偏移量，不支持ignore nulls語句
4：不允許負偏移量
5：不支持respect|ignore nulls語句
6：不允許負偏移量，不支持respect|ignore nulls語句
7：nulls的特定處理：first_value(, 1, null, 'IGNORE NULLS') ，這里是字符串參數。
8：不支持ignore nulls語句9：不支持ignore nulls語句和from last語句

過濾語句

雖然filter語句只是語法糖——你也可以很容易地使用表達式來獲得相同的結果——我認為它也是必不可少的語法糖，因為它能使人們更加容易地學習和理解SQL語句。

看看下面的select子句，您覺得哪一個更容易理解？

SELECT?SUM(revenue)?total_revenue ?????,?SUM(CASE?WHEN?product?=?1? ????????????????THEN?revenue ????????????END ??????????)?prod1_revenue ???...

和

SELECT?SUM(revenue)?total_revenue ?????,?SUM(revenue)?FILTER(WHERE?product?=?1)?prod1_revenue ???...

此示例很好地總結了filter子句的作用：它是聚合函數的后綴，可以在進行聚合之前根據特定條件，過濾掉相應的行。pivot技術是filter子句最常見的用例。這包括將實體屬性值（EAV）模型中的屬性轉換為表格的列，如果想了解更多的內容，可以參考鏈接“filter-Selective Aggregates”（https://modern-sql.com/feature/filter）。

SQLite 從版本3.25.0開始，在使用over子句的聚合函數中支持了filter子句，但是在使用group by子句的聚合函數中還不支持。不幸的是，這意味著您仍然無法在SQLite中使用filter語句來處理上述情況。你必須像以前一樣使用case表達式。我真的希望SQLite在這一點上能盡快做到。