将SQLite用作文档数据库(2020)
摘要
本文介绍如何利用SQLite的JSON支持与生成列,将其作为文档数据库使用,从而能够从JSON列中提取并索引数据。
暂无内容
查看缓存全文
缓存时间: 2026/08/29 15:37
# SQLite作为文档数据库
来源:https://dgl.cx/2020/06/sqlite-json-support
SQLite早已支持JSON功能。
但近期它增加了一项杀手级特性:生成列(generated columns)(https://www.sqlite.org/gencol.html)。(该功能于2020年1月22日发布的3.31.0版本中引入。)
这使得我们可以直接将JSON数据插入SQLite,然后由数据库提取数据并建立索引——即可以把SQLite当作文档数据库使用。虽然PostgreSQL早已支持此功能,Elasticsearch等产品更是其典型代表,但能在一个嵌入式数据库中拥有该特性,对轻量级应用而言实属福音。
让我们开始实践:
```
$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Connected to a transient in-memory database.
sqlite> CREATE TABLE t (
body TEXT,
d INT GENERATED ALWAYS AS (json_extract(body, '$.d')) VIRTUAL);
sqlite> insert into t values(json('{"d":"42"}'));
sqlite> select * from t WHERE d = 42;
{"d":"42"}|42
```
操作非常简单——`d`列的数据直接从提供的JSON中提取。
(补充说明:难点可能在于获取足够新版本的SQLite。截至本文撰写时,macOS的Homebrew已提供该版本,否则你可能需要使用如nixpkgs-unstable等不稳定的源。)
这种方式具有若干优点。通常建议在插入数据时通过`json()`函数进行压缩和验证,因为SQLite没有专门的JSON类型,它会接受任何输入。但这并非强制要求,你可以添加约束条件,但很可能忘记执行。而使用`GENERATED ALWAYS`配合`json_extract`,无效JSON在插入时就会触发`Error: malformed JSON`错误。
这可以进一步拓展:
```
sqlite> CREATE TABLE x (
body TEXT,
id TEXT GENERATED ALWAYS AS (json_extract(body, '$.id')) VIRTUAL NOT NULL);
sqlite> insert into x values('');
Error: malformed JSON
sqlite> insert into x values('{}');
Error: NOT NULL constraint failed: x.id
```
我们可以强制要求插入的JSON必须包含特定字段——这里通过`NOT NULL`实现,但也可以使用其他约束和SQLite特性。
你可能注意到示例中使用了`VIRTUAL`类型生成列。也可以选择`STORED`类型来缓存提取值,但其缺点是无法通过`ALTER TABLE`添加这类列。
不过,即使对于虚拟列,我们仍然可以为其创建索引:
```
CREATE INDEX xid on x(id);
```
验证索引是否按预期工作:
```
EXPLAIN QUERY PLAN SELECT * FROM x WHERE id='foo';
QUERY PLAN
`--SEARCH TABLE x USING INDEX xid (id=?)
```
结合ALTER TABLE,我们可以添加新列并为其建立索引:
```
ALTER TABLE x ADD COLUMN text TEXT
GENERATED ALWAYS AS (json_extract(body, '$.text')) VIRTUAL;
INSERT INTO x VALUES(json('{"id":43, "text":"test"}'));
CREATE INDEX xtext ON x(text);
```
这种方法的优势在于:可以从最简单的单JSON列结构开始,随着发现JSON数据中有用字段,逐步添加列和索引。这种模式特别适合处理Webhook数据——将收到的所有原始数据直接插入表,后续再按需提取有价值的内容。尽情探索吧。
2020年6月17日 发布于code专栏 (https://dgl.cx/tag/code)
相似文章
SQLite:万能数据库解决方案
本文倡导将SQLite视为一种多功能且稳定的数据库解决方案,强调其在不同技术栈中替代多种其他工具的能力。
SQLite 3.53.0
SQLite 3.53.0 发布,带来重要的累积改进,包括 ALTER TABLE 约束修改、新的 JSON 函数(json_array_insert),以及通过新的查询结果格式化库实现的重大 CLI 模式增强。
基于 SQLite 的分布式高性能 Blob 存储
Zerodha 介绍了 Blobasaur——一个基于 SQLite 构建的分片式、分布式轻量级 blob 存储服务器,在商用硬件上可处理数 MB 大小的 JSON blob,达到每秒数千次操作、个位数毫秒的延迟,并在评估了 ScyllaDB、Postgres、YugabyteDB、RocksDB、Ceph 和 SeaweedFS 后,用它替代了 SSDB。
我们如何为SQLite构建零磁盘、S3分层存储引擎
Rivet工程师详细介绍了他们如何为SQLite构建零磁盘、S3分层的存储引擎,实现了隔离数据库,具备即时启动、低延迟写入、时间点恢复和无限存储能力,可支持数百万Actors。
BriskDB
BriskDB 是一款处于早期阶段的数据库工具,它将 SQLite 文件转化为一个支持并行写入、兼容 PostgreSQL 并嵌入 Rust/Python API 的分片系统,旨在扩展 SQLite 的同时保持其简洁性。