SQLite 是一個(gè)被大家低估的數(shù)據(jù)庫，但有些人認(rèn)為它是一個(gè)不適合生產(chǎn)環(huán)境使用的玩具數(shù)據(jù)庫砌们。事實(shí)上杆麸，SQLite 是一個(gè)非常可靠的數(shù)據(jù)庫浪感，它可以處理 TB 級(jí)的數(shù)據(jù)昔头，但它沒有網(wǎng)絡(luò)層。接下來影兽，本文將與大家共同探討 SQLite 在過去一年中最新的 SQL 功能揭斧。
SQLite “只是”一個(gè)庫，它不是傳統(tǒng)意義上的服務(wù)器峻堰。因此未蝌，在某些場(chǎng)合下，它確實(shí)不合適茧妒。但是，在相當(dāng)多的其他場(chǎng)合左冬，它卻是最合適的選擇桐筏。SQLite 號(hào)稱是部署和使用最廣泛的數(shù)據(jù)庫引擎。我認(rèn)為這很有可能拇砰，因?yàn)?SQLite 沒有版權(quán)的限制梅忌。無論何時(shí)，只要開發(fā)者想使用 SQL 在文件中存儲(chǔ)結(jié)構(gòu)化的數(shù)據(jù)除破，SQLite 應(yīng)是首選方案牧氮。
SQLite 的 SQL 方言也非常強(qiáng)大。它比 MySQL 早四年就開始支持 with 語句瑰枫。最近踱葛，它還實(shí)現(xiàn)了對(duì)于窗口函數(shù)的支持丹莲，這僅僅比 MySQL 晚五個(gè)月。
接下來尸诽，本文將介紹 SQLite 在 2018 年新增加的 SQL 功能甥材，也就是 SQLite 從版本 3.22.0 到 3.26.0 所新增加的 SQL 功能。
具體內(nèi)容包括：
- 布爾字面量和判斷
- 窗口函數(shù)
- Filter子句
- Insert … on conflict (“Upsert”)
- 重命名列
- 在Modern-SQL.com上接下來
布爾變量和判斷
SQLite支持“假”布爾值：它接受Boolean作為類型的名稱性含，但它將其當(dāng)作整數(shù)看待（這一點(diǎn)非常類似于MySQL）洲赵。真值true和false分別由數(shù)值1和0表示（這一點(diǎn)和C語言一樣）。
從版本3.23.0開始商蕴，SQLite將關(guān)鍵字true和false分別用數(shù)字1和0表示叠萍，并支持is [not] true | false的判斷語句。現(xiàn)在绪商，它不再支持關(guān)鍵字unknown苛谷。開發(fā)者可以使用空值null來代替，因?yàn)閡nknown和null的布爾值是一樣的部宿。
在INSERT和UPDATE語句中抄腔，字面量true和false可以大大提高values和set子句的可讀性。
is [not] true | false這個(gè)判斷語句很有用理张，它與比較操作的含義不一樣：
我們來比較一下
WHERE c <> FALSE
和
WHERE c IS NOT FALSE
在上面的例子中赫蛇，如果c是null, 那么c <> false的結(jié)果是unknown.
這是因?yàn)閃HERE子句只接受結(jié)果為true的值，它會(huì)過濾掉結(jié)果為false或unknown的值雾叭。這樣悟耘，它就會(huì)把對(duì)應(yīng)的行從結(jié)果中去掉。
與此相對(duì)應(yīng)织狐，如果c是null暂幼，那么，c is not false的判斷結(jié)果是true移迫。因此旺嬉，第二個(gè)WHERE子句也將包含c是null的行。
要達(dá)到同樣的效果厨埋，您可以采用的另外一種方法是增加單獨(dú)處理null值的子句邪媳。也就是使用語句:
WHERE c <> FALSE
OR c IS NULL
這種形式的語句更長(zhǎng)并且有一些冗余語句（c被使用了兩次）。長(zhǎng)話短說荡陷，可以使用is not false判斷來替代這個(gè)or…is-null的語句雨效。更詳細(xì)的內(nèi)容，請(qǐng)參考“Binary Decisions Based on Three-Valued Results”废赞。
SQLite中對(duì)布爾字面量和布爾判斷的支持現(xiàn)在和其他開源數(shù)據(jù)庫接近徽龟，唯一的差距是SQLite不支持is[not] unknown（你可以使用is [not] null來代替）。有趣的是唉地，這些功能在下面提到的商用產(chǎn)品中還不可用据悔。
0：只支持true,false.不支持notknown传透，如果需要，用null代替1：不支持is [not] unknown,如果需要屠尊，用is [not] null代替
窗口函數(shù)
SQLite 3.25.0引入了窗口函數(shù)旷祸。如果你知道窗口函數(shù)，那么也知道這是一件大事讼昆。如果你不了解窗口功能托享，請(qǐng)你自己學(xué)習(xí)如何使用。這篇文章不會(huì)具體解釋窗口函數(shù)浸赫，但請(qǐng)相信：它是最重要的“現(xiàn)代”SQL特性闰围。
SQLite對(duì)over子句的支持與其他數(shù)據(jù)庫非常接近。唯一值得注意的限制是range語句不支持?jǐn)?shù)字或間隔距離（僅支持current row和unbounded preceding|following）既峡。在發(fā)布sqlite 3.25.0時(shí),SQL Server和PostgreSQL具有同樣的限制羡榴。PostgreSQL 11消除了這一限制。
0：沒有變化
1：Range范圍定義不支持datetime類型
2：Range范圍不接受關(guān)鍵字 (只支持unbounded和current row)
SQLite對(duì)于窗口函數(shù)的支持在業(yè)界是領(lǐng)先的运敢。它不支持的功能在其他一些主要產(chǎn)品中也同樣不支持（在聚合中語句中的distinct校仑，width_bucket， respect|ignore nulls和from first|last等語句）传惠。
0：同樣沒有ORDER BY 語句
1：不允許負(fù)偏移量迄沫，nulls的特定處理：lead(, 'IGNORE NULLS')，這里是字符串參數(shù)
2：沒有缺省值(第三個(gè)參數(shù))卦方，不支持respect|ignore nulls語句
3：不允許負(fù)偏移量羊瘩，不支持ignore nulls語句
4：不允許負(fù)偏移量
5：不支持respect|ignore nulls語句
6：不允許負(fù)偏移量，不支持respect|ignore nulls語句
7：nulls的特定處理：first_value(, 1, null, 'IGNORE NULLS') 盼砍，這里是字符串參數(shù)尘吗。
8：不支持ignore nulls語句9：不支持ignore nulls語句和from last語句
過濾語句
雖然filter語句只是語法糖——你也可以很容易地使用表達(dá)式來獲得相同的結(jié)果——我認(rèn)為它也是必不可少的語法糖，因?yàn)樗苁谷藗兏尤菀椎貙W(xué)習(xí)和理解SQL語句浇坐。
看看下面的select子句睬捶，您覺得哪一個(gè)更容易理解？
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
...
此示例很好地總結(jié)了filter子句的作用：它是聚合函數(shù)的后綴近刘，可以在進(jìn)行聚合之前根據(jù)特定條件擒贸，過濾掉相應(yīng)的行。pivot技術(shù)是filter子句最常見的用例跌宛。這包括將實(shí)體屬性值（EAV）模型中的屬性轉(zhuǎn)換為表格的列，如果想了解更多的內(nèi)容积仗，可以參考鏈接“filter-Selective Aggregates”（https://modern-sql.com/feature/filter）疆拘。
SQLite 從版本3.25.0開始，在使用over子句的聚合函數(shù)中支持了filter子句寂曹，但是在使用group by子句的聚合函數(shù)中還不支持哎迄。不幸的是回右，這意味著您仍然無法在SQLite中使用filter語句來處理上述情況。你必須像以前一樣使用case表達(dá)式漱挚。我真的希望SQLite在這一點(diǎn)上能盡快做到翔烁。
Insert … on conflict (“Upsert”)
SQLite 從版本3.24.0開始，引入了“upsert”概念：它是一個(gè)insert語句旨涝，可以優(yōu)雅地處理主鍵和唯一約束的沖突蹬屹。您可以選擇忽略這些沖突（在on conflict語句中什么都不做）或者更新當(dāng)前行（在on conflict語句中執(zhí)行更新操作）。
這是一個(gè)特有的SQL擴(kuò)展白华，即它不是標(biāo)準(zhǔn)SQL的一部分慨默，因此在下面的矩陣中是灰色的。但是弧腥，SQLite遵守與PostgreSQL相同的語法來實(shí)現(xiàn)此功能0厦取。該標(biāo)準(zhǔn)提供了對(duì)merge語句的支持。
與PostgreSQL不同管搪，SQLite在以下語句中存在問題虾攻。
INSERT INTO target
SELECT *
FROM source
ON CONFLICT (id)
DO UPDATE SET val = excluded.val
根據(jù)說明文檔，這是因?yàn)榻馕銎鳠o法判斷關(guān)鍵字ON是SELECT語句的連接約束還是upsert子句的開頭更鲁。你可以通過向查詢中添加子句來解決霎箍，例如where true。
INSERT INTO target
SELECT *
FROM source
WHERE true
ON CONFLICT (id)
DO UPDATE SET val = excluded.val
0：同樣記錄insert岁经、update朋沮、delete和merge操作的錯(cuò)誤信息 (“DML error logging”)
1：On conflict語句不能緊挨查詢的from語句，如果需要缀壤，可以添加 where true語句來分隔樊拓。
重命名列
SQLite引入的另一個(gè)特有功能是重命名基準(zhǔn)數(shù)據(jù)庫表中的列1。標(biāo)準(zhǔn)的SQL不支持此類功能2塘慕。
SQLite遵循其他產(chǎn)品常用的語法來重命名列：
ALTER TABLE … RENAME COLUMN … TO
0：請(qǐng)查閱 sp_rename.
其他消息
在2018年筋夏，SQLite除了在SQL語法上的變化，還有一些應(yīng)用程序接口（API）的變化图呢。你可以查閱sqlite.com（https://www.sqlite.org/news.html）上的新聞部分來了解更詳細(xì)的消息条篷。
腳標(biāo):
- 0：SQLite通常遵循PostgreSQL語法，Richard Hipp將此稱為PostgreSQL會(huì)怎么做（WWPD）蛤织。
- 1：基準(zhǔn)數(shù)據(jù)庫表是指用Create table語句創(chuàng)建的數(shù)據(jù)庫表赴叹。派生的數(shù)據(jù)庫表（如Select語句返回的查詢結(jié)果集）中的列名可以通過SELECT語句、FROM語句或WITH語句來進(jìn)行改變
- 2：據(jù)我所知指蚜，也許可以通過可更新視圖或派生的列來模擬該功能乞巧。
