AI魔法学院客服
微信为什么使用 SQLite 保存聊天记录?
本文介绍了SQLite作为一个被低估但功能强大的数据库,尽管没有网络层,但适用于多种场合。文章重点讨论了SQLite在2018年新增的SQL功能,包括布尔字面量和判断、窗口函数、Filter子句、Insert … on conflict (“Upsert”)、重命名列等。SQLite支持布尔值判断,窗口函数功能领先业界,Filter子句提供更易理解的聚合过滤方式,Upsert功能优雅处理主键和唯一约束冲突,并引入重命名列功能。此外,还提到了SQLite在API方面的一些变化。总之,SQLite在不断完善其SQL功能,成为一个可靠且广泛应用的数据库引擎。
 2024-05-21
收藏 复制地址分享海报

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 功能。

具体内容包括:

1. 

布尔字面量和判断

2. 

窗口函数

3. 

Filter子句

4. 

Insert … on conflict (“Upsert”)

5. 

重命名列

6. 

Modern-SQL.com上接下来

布尔变量和判断

SQLite支持布尔值:它接受Boolean作为类型的名称,但它将其当作整数看待(这一点非常类似于MySQL)。真值truefalse分别由数值10表示(这一点和C语言一样)。

从版本3.23.0开始,SQLite将关键字truefalse分别用数字10表示,并支持is [not] true | false的判断语句。现在,它不再支持关键字unknown。开发者可以使用空值null来代替,因为unknownnull的布尔值是一样的。

INSERTUPDATE语句中,字面量truefalse可以大大提高valuesset子句的可读性。

is [not] true | false这个判断语句很有用,它与比较操作的含义不一样:

我们来比较一下

· 

WHERE c <> FALSE

· 

WHERE c IS NOT FALSE

在上面的例子中,如果cnull, 那么c <> false的结果是unknown.

这是因为WHERE子句只接受结果为true的值,它会过滤掉结果为falseunknown的值。这样,它就会把对应的行从结果中去掉。

与此相对应,如果cnull,那么,c is not false的判断结果是true。因此,第二个WHERE子句也将包含cnull的行。

要达到同样的效果,您可以采用的另外一种方法是增加单独处理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特性。

SQLiteover子句的支持与其他数据库非常接近。唯一值得注意的限制是range语句不支持数字或间隔距离(仅支持current rowunbounded preceding|following)。在发布sqlite 3.25.0,SQL ServerPostgreSQL具有同样的限制。PostgreSQL 11消除了这一限制。

 

0:没有变化

1Range范围定义不支持datetime类型

2Range范围不接受关键字 (只支持unboundedcurrent row)

SQLite对于窗口函数的支持在业界是领先的。它不支持的功能在其他一些主要产品中也同样不支持(在聚合中语句中的distinctwidth_bucketrespect|ignore nullsfrom 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语句

7nulls的特定处理: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 target

SELECT *

  FROM source

ON CONFLICT (id)

    DO UPDATE SET val = excluded.val

根据说明文档,这是因为解析器无法判断关键字ONSELECT语句的连接约束还是upsert子句的开头。你可以通过向查询中添加子句来解决,例如where true

· 

INSERT INTO target

SELECT *

  FROM source

 WHERE true

ON CONFLICT (id)

    DO UPDATE SET val = excluded.val

 

0:同样记录insertupdatedeletemerge操作的错误信息 (“DML error logging”)

1On conflict语句不能紧挨查询的from语句,如果需要,可以添加  where true语句来分隔。

重命名列

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

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

ALTER TABLE … RENAME COLUMN … TO

 

0:请查阅 sp_rename.

其他消息

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

脚标:

· 

0SQLite通常遵循PostgreSQL语法,Richard Hipp将此称为PostgreSQL会怎么做(WWPD)。

· 

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

· 

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

 

 

 

 

出自:https://mp.weixin.qq.com/s/QbyeMOWrtb-Dh_xgpZZPMA

 

本文档由网友提供,仅限参考学习,如有不妥或产生版权问题,请联系我们及时删除。 客服请加微信:skillupvip
评论
1 评论
智慧女孩要秃头2024/5/21 8:49:13
SQLite确实是个宝藏级数据库,虽无网络层但实用性超强!2018年的这些新特性简直太棒了,特别是布尔判断和窗口函数,简直领先业界!Filter子句让聚合过滤更直观,Upsert功能处理约束冲突也很优雅。重命名列功能也很实用。总之,SQLite越来越完善了,值得推荐!
20秒读懂全文
伴读
# 1. 一句话总结文章摘要
SQLite作为被低估的数据库,在新的一年中展现了强大的SQL功能,包括布尔字面量和判断、窗口函数、过滤子句等,使其成为许多场合下的理想选择。

# 2. 生成关键词和可能相关的关键词
**关键词**:
- SQLite
- 数据库
- SQL功能
- 布尔字面量
- 窗口函数
- 过滤子句
- Upsert
- 列重命名
One More Thing
One More Thing again ...

找组织,加入AI魔法学院群