來源：https://modern-sql.com/blog/2019-01/sqlite-in-2018
SQLite 是一個被大家低估的資料庫，但有些人認為它是一個不適合生產環境使用的玩具資料庫。事實上，SQLite 是一個非常可靠的資料庫，它可以處理 TB 級的數據，但它沒有網路層。接下來，本文將與大家共同探討 SQLite 在過去一年中最新的 SQL 功能。
SQLite 「只是」一個庫，它不是傳統意義上的伺服器。因此，在某些場合下，它確實不合適。但是，在相當多的其他場合，它卻是最合適的選擇。SQLite 號稱是部署和使用最廣泛的資料庫引擎。我認為這很有可能，因為 SQLite 沒有版權的限制。
無論何時，只要開發者想使用 SQL 在檔中儲存結構化的數據，SQLite 應是首選方案。
SQLite 的 SQL 方言也非常強大。它比 MySQL 早四年就開始支持 with 語句。最近，它還實作了對於視窗函式的支持，這僅僅比 MySQL 晚五個月。
接下來，本文將介紹 SQLite 在 2018 年新增加的 SQL 功能，也就是 SQLite 從版本 3.22.0 到 3.26.0 所新增加的 SQL 功能。
具體內容包括：
布爾字面量和判斷
視窗函式
Filter子句
Insert … on conflict (「Upsert」)
重新命名列
在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在這一點上能盡快做到。
# Insert … on conflict (「Upsert」)
SQLite 從版本3.24.0開始，引入了「upsert」概念：它是一個insert語句，可以優雅地處理主鍵和唯一約束的沖突。您可以選擇忽略這些沖突(在on conflict語句中什麽都不做)或者更新當前行(在on conflict語句中執行更新操作)。
這是一個特有的SQL擴充套件，即它不是標準SQL的一部份，因此在下面的矩陣中是灰色的。但是，SQLite遵守與PostgreSQL相同的語法來實作此功能0。該標準提供了對merge語句的支持。
與PostgreSQL不同，SQLite在以下語句中存在問題。
INSERT INTO targetSELECT * FROM source ON CONFLICT (id) DO UPDATE SET val = excluded.val
根據說明文件，這是因為解析器無法判斷關鍵字ON是SELECT語句的連線約束還是upsert子句的開頭。你可以透過向查詢中添加子句來解決，例如where true。
INSERT INTO targetSELECT * FROM source WHERE true ON CONFLICT (id) DO UPDATE SET val = excluded.val
0：同樣記錄insert、update、delete和merge操作的錯誤資訊 (「DML error logging」)
1：On conflict語句不能緊挨查詢的from語句，如果需要，可以添加 where true語句來分隔。
# 重新命名列
SQLite引入的另一個特有功能是重新命名基準資料庫表中的列1。標準的SQL不支持此類功能2。
SQLite遵循其他產品常用的語法來重新命名列：
ALTER TABLE … RENAME COLUMN … TO
0：請查閱 sp_rename.
# 其他訊息
在2018年，SQLite除了在SQL語法上的變化，還有一些應用程式介面(API)的變化。你可以查閱sqlite.com(https://www.sqlite.org/news.html)上的新聞部份來了解更詳細的訊息。
腳標:
0：SQLite通常遵循PostgreSQL語法，Richard Hipp將此稱為PostgreSQL會怎麽做(WWPD)。
1：基準資料庫表是指用Create table語句建立的資料庫表。衍生的資料庫表(如Select語句返回的查詢結果集)中的列名可以透過SELECT語句、FROM語句或WITH語句來進行改變
2：據我所知，也授權以透過可更新檢視或衍生的列來模擬該功能。
熱門推薦
