新动态：微信为什么使用 SQLite 保存聊天记录?

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上接下来1布尔变量和判断

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代替。

2窗口函数

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语句

3过滤语句

虽然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）。

微信搜索公众号：Java项目精选，回复：java 领取资料 。

SQLite 从版本3.25.0开始，在使用over子句的聚合函数中支持了filter子句，但是在使用group by子句的聚合函数中还不支持。不幸的是，这意味着您仍然无法在SQLite中使用filter语句来处理上述情况。你必须像以前一样使用case表达式。我真的希望SQLite在这一点上能尽快做到。

4Insert … 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语句来分隔。

2：另外，公众 号Java精选，回复java面试，获取数据库面试资料，支持随时随地刷题。

5重命名列

SQLite引入的另一个特有功能是重命名基准数据库表中的列1。标准的SQL不支持此类功能2。

SQLite遵循其他产品常用的语法来重命名列：

ALTER TABLE … RENAME COLUMN … TO

0：请查阅 sp_rename.6其他消息

在2018年，SQLite 除了在SQL语法上的变化，还有一些应用程序接口（API）的变化。你可以查阅sqlite.com（https://www.sqlite.org/news.html）上的新闻部分来了解更详细的消息。

脚注

SQLite 通常遵循 PostgreSQL 语法，Richard Hipp 将此称为 PostgreSQL会 怎么做（WWPD）。

基准数据库表是指用Create table语句创建的数据库表。派生的数据库表（如Select语句返回的查询结果集）中的列名可以通过SELECT语句、FROM语句或WITH语句来进行改变

据我所知，也许可以通过可更新视图或派生的列来模拟该功能。

来源：https://modern-sql.com/blog/2019-01/sqlite-in-2018

PS：如果觉得我的分享不错，欢迎大家随手点赞、在看。

关注公众号：Java后端编程，回复下面关键字 

要Java学习完整路线，回复  路线 

缺Java入门视频，回复： 视频 

要Java面试经验，回复  面试 

缺Java项目，回复： 项目 

进Java粉丝群： 加群 

PS：如果觉得我的分享不错，欢迎大家随手点赞、在看。

（完）加我"微信" 获取一份 最新Java面试题资料

请备注：666，不然不通过～

最近好文

1、堪称神级的Spring Boot手册，从基础入门到实战进阶

2、SQL优化万能公式：5 大步骤 + 10 个案例

3、Java面试中如何介绍自己的项目经验？

4、一个基于Spring Boot+Vue+Redis的物联网智能家居系统

5、公司用了 6 年的Spring Boot 部署方案！

最近面试BAT，整理一份面试资料《Java面试BAT通关手册》，覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。获取方式：关注公众号并回复 java 领取，更多内容陆续奉上。

明天见(｡･ω･｡)

关键词： 窗口函数 这是因为