重识 SQLite，简约不简单

2022-03-16 00:00:00 数据函数数据库支持提供

大家好！我是只谈技术不剪发的 Tony 老师。

如果问你哪个数据库产品是世界上使用多的数据库，你认为是 Oracle、MySQL 还是 Microsoft SQL Server？

以上都不是，世界上安装使用多的数据库是 SQLite。没错，就是这个小巧的嵌入式数据库引擎。所有的手机、电脑、浏览器以及无数的应用程序都内置了 SQLite 数据库，PHP 和 Python 语言也内置的 SQLite 支持，预计正在使用的 SQLite 数据库达到了一万亿（1012）以上。

无论对于开发/测试人员、数据分析师/科学家、IT 运维人员还是产品经理，SQLite 都是一个非常有用的工具。本文就带大家回顾一下 SQLite 提供的一些实用功能。

命令行工具

SQLite 提供了一个非常方便的数据库控制台，也就是 Windows 系统上的 sqlite3.exe 或者 Linux / macOS 系统上的 sqlite3。对于数据分析师来说，它比 Excel 强大，但是比 Python pandas 简单。用户可以通过命令导入 CSV 文件，导入时会自动创建相应的数据表：

> .import --csv city.csv city> select count(*) from city;1117

SQLite 命令行工具支持各种 SQL 语句，同时以 ASCII 风格显示查询结果：

select  century || ' century' as dates,  count(*) as city_countfrom historygroup by centuryorder by century desc;┌────────────┬────────────┐│   dates    │ city_count │├────────────┼────────────┤│ 21 century │ 1          ││ 20 century │ 263        ││ 19 century │ 189        ││ 18 century │ 191        ││ 17 century │ 137        ││ ...        │ ...        │└────────────┴────────────┘

查询结果可以被导出到各种 SQL 命令、CSV、JSON、Markdown 以及 HTML 格式的文件。例如：

.mode json.output city.jsonselect city, foundation_year, timezone from city limit 10;.shell cat city.json[    { "city": "Amsterdam", "foundation_year": 1300, "timezone": "UTC+1" },    { "city": "Berlin", "foundation_year": 1237, "timezone": "UTC+1" },    { "city": "Helsinki", "foundation_year": 1548, "timezone": "UTC+2" },    { "city": "Monaco", "foundation_year": 1215, "timezone": "UTC+1" },    { "city": "Moscow", "foundation_year": 1147, "timezone": "UTC+3" },    { "city": "Reykjavik", "foundation_year": 874, "timezone": "UTC" },    { "city": "Sarajevo", "foundation_year": 1461, "timezone": "UTC+1" },    { "city": "Stockholm", "foundation_year": 1252, "timezone": "UTC+1" },    { "city": "Tallinn", "foundation_year": 1219, "timezone": "UTC+2" },    { "city": "Zagreb", "foundation_year": 1094, "timezone": "UTC+1" }]

如果你喜欢使用 BI 工具而不是控制台，常见的数据探索工具都支持 SQLite，例如 Metabase、Redash 以及 Superset 等。

原生 JSON

SQLite 可以非常方便地分析和转换 JSON 数据，用户可以直接从文件中查询数据，也可以将数据导入表中然后进行查询：

select  json_extract(value, '$.iso.code') as code,  json_extract(value, '$.iso.number') as num,  json_extract(value, '$.name') as name,  json_extract(value, '$.units.major.name') as unitfrom  json_each(readfile('currency.sample.json'));┌──────┬─────┬─────────────────┬──────────┐│ code │ num │      name       │   unit   │├──────┼─────┼─────────────────┼──────────┤│ ARS  │ 032 │ Argentine peso  | peso     ││ CHF  │ 756 │ Swiss Franc     │ franc    ││ EUR  │ 978 │ Euro            │ euro     ││ GBP  │ 826 │ British Pound   │ pound    ││ INR  │ 356 │ Indian Rupee    │ rupee    ││ JPY  │ 392 │ Japanese yen    │ yen      ││ MAD  │ 504 │ Moroccan Dirham │ dirham   ││ RUR  │ 643 │ Russian Rouble  │ rouble   ││ SOS  │ 706 │ Somali Shilling │ shilling ││ USD  │ 840 │ US Dollar       │ dollar   │└──────┴─────┴─────────────────┴──────────┘

无论 JSON 对象包含多少层嵌套，SQLite 都可以获取其中的数据：

select  json_extract(value, '$.id') as id,  json_extract(value, '$.name') as namefrom  json_tree(readfile('industry.sample.json'))where  path like '$[%].industries';┌────────┬──────────────────────┐│   id   │         name         │├────────┼──────────────────────┤│ 7.538  │ Internet provider    ││ 7.539  │ IT consulting        ││ 7.540  │ Software development ││ 9.399  │ Mobile communication ││ 9.400  │ Fixed communication  ││ 9.401  │ Fiber-optics         ││ 43.641 │ Audit                ││ 43.646 │ Insurance            ││ 43.647 │ Bank                 │└────────┴──────────────────────┘

CTE 与集合运算

SQLite 支持通用表表达式（Common Table Expression）和连接查询。对于具有层级关系的数据（例如组织结构等），可以通过 WITH RECURSIVE 很方便地进行遍历。

with recursive tmp(id, name, level) as (  select id, name, 1 as level  from area  where parent_id is null  union all  select    area.id,    tmp.name || ', ' || area.name as name,    tmp.level + 1 as level  from area    join tmp on area.parent_id = tmp.id)select * from tmp;┌──────┬──────────────────────────┬───────┐│  id  │           name           │ level │├──────┼──────────────────────────┼───────┤│ 93   │ US                       │ 1     ││ 768  │ US, Washington DC        │ 2     ││ 1833 │ US, Washington           │ 2     ││ 2987 │ US, Washington, Bellevue │ 3     ││ 3021 │ US, Washington, Everett  │ 3     ││ 3039 │ US, Washington, Kent     │ 3     ││ ...  │ ...                      │ ...   │└──────┴──────────────────────────┴───────┘

SQLite 还提供了 UNION、INTERSECT 以及 EXCEPT 集合运算符：

select employer_idfrom employer_areawhere area_id = 1exceptselect employer_idfrom employer_areawhere area_id = 2;

基于其他字段的生成列也不在话下：

alter table vacancyadd column salary_net integer as (  case when salary_gross = true then    round(salary_from/1.04)  else    salary_from  end);

生成列可以像其他普通字段一样查询：

select  substr(name, 1, 40) as name,  salary_netfrom vacancywhere  salary_currency = 'JPY'  and salary_net is not nulllimit 10;

统计函数

通过加载 stats 插件，SQLite 支持以下描述性统计：均值、中位数、百分位、标准差等。

.load sqlite3-stats
select  count(*) as book_count,  cast(avg(num_pages) as integer) as mean,  cast(median(num_pages) as integer) as median,  mode(num_pages) as mode,  percentile_90(num_pages) as p90,  percentile_95(num_pages) as p95,  percentile_99(num_pages) as p99from books;┌────────────┬──────┬────────┬──────┬─────┬─────┬──────┐│ book_count │ mean │ median │ mode │ p90 │ p95 │ p99  │├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤│ 1483       │ 349  │ 295    │ 256  │ 640 │ 817 │ 1199 │└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘

SQLite 比其他数据库管理系统提供的函数更少一些，不过可以通过扩展插件的方式获取额外的支持。这个项目按照不同的领域编译了一些常用的插件。

以下示例在控制台中描绘了一个数据分布图：

with slots as (  select    num_pages/100 as slot,    count(*) as book_count  from books  group by slot),max as (  select max(book_count) as value  from slots)select  slot,  book_count,  printf('%.' || (book_count * 30 / max.value) || 'c', '*') as barfrom slots, maxorder by slot;┌──────┬────────────┬────────────────────────────────┐│ slot │ book_count │              bar               │├──────┼────────────┼────────────────────────────────┤│ 0    │ 116        │ *********                      ││ 1    │ 254        │ ********************           ││ 2    │ 376        │ ****************************** ││ 3    │ 285        │ **********************         ││ 4    │ 184        │ **************                 ││ 5    │ 90         │ *******                        ││ 6    │ 54         │ ****                           ││ 7    │ 41         │ ***                            ││ 8    │ 31         │ **                             ││ 9    │ 15         │ *                              ││ 10   │ 11         │ *                              ││ 11   │ 12         │ *                              ││ 12   │ 2          │ *                              │└──────┴────────────┴────────────────────────────────┘

性能

SQLite 可以支持数以亿计的数据行，在个人电脑上的普通 INSERT 语句也可以达到 10 万条/秒以上。如果使用虚拟表连接 CSV 文件，插入性能会更好：

.load sqlite3-vsv
create virtual table temp.blocks_csv using vsv(    filename="ipblocks.csv",    schema="create table x(network text, geoname_id integer, registered_country_geoname_id integer, represented_country_geoname_id integer, is_anonymous_proxy integer, is_satellite_provider integer, postal_code text, latitude real, longitude real, accuracy_radius integer)",    columns=10,    header=on,    nulls=on);.timer oninsert into blocksselect * from blocks_csv;
Run Time: real 5.176 user 4.716420 sys 0.403866select count(*) from blocks;3386629
Run Time: real 0.095 user 0.021972 sys 0.063716

很多人认为 SQLite 不适合作为 Web 应用后台数据库，因为它不支持并发访问。实际上这是一个谣传，在write-ahead log 模式下，SQLite 提供了并发读取。虽然只能单个进程写入，但是很多情况下已经足够了。

SQLite 非常适合小型网站和应用程序。sqlite.org 就是使用 SQLite 作为数据库，在不需要进行优化的情况下（每个页面大概包含 200 个查询请求），它可以处理每个月 70 万的访问量，同时性能超过 95% 的网站。

文档、图形以及全文搜索

SQLite 支持部分索引和表达式索引（函数索引），我们可以基于计算列创建索引，甚至将 SQLite 作为文档数据库使用：

create table currency(  body text,  code text as (json_extract(body, '$.code')),  name text as (json_extract(body, '$.name')));
create index currency_code_idx on currency(code);
insert into currencyselect valuefrom json_each(readfile('currency.sample.json'));explain query planselect name from currency where code = 'EUR';QUERY PLAN`--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)

有了 WITH RECURSIVE 查询，SQLite 也可以作为一个图形数据库使用，或者使用这个 simple-graph（Python 模块）。

SQLite 提供了内置的全文搜索功能：

create virtual table books_ftsusing fts5(title, author, publisher);
insert into books_ftsselect title, author, publisher from books;
select  author,  substr(title, 1, 30) as title,  substr(publisher, 1, 10) as publisherfrom books_ftswhere books_fts match 'ann'limit 5;┌─────────────────────┬────────────────────────────────┬────────────┐│       author        │             title              │ publisher  │├─────────────────────┼────────────────────────────────┼────────────┤│ Ruby Ann Boxcar     │ Ruby Ann's Down Home Trailer P │ Citadel    ││ Ruby Ann Boxcar     │ Ruby Ann's Down Home Trailer P │ Citadel    ││ Lynne Ann DeSpelder │ The Last Dance: Encountering D │ McGraw-Hil ││ Daniel Defoe        │ Robinson Crusoe                │ Ann Arbor  ││ Ann Thwaite         │ Waiting for the Party: The Lif │ David R. G │└─────────────────────┴────────────────────────────────┴────────────┘

如果想要一个内存数据库作为中间计算模块，只需要一行 Python 代码就可以搞定：

db = sqlite3.connect(":memory:")

甚至可以支持多个连接访问：

db = sqlite3.connect("file::memory:?cache=shared")

更多功能

SQLite 还提供了许多其他的功能，例如窗口函数、UPSERT 语句、UPDATE FROM、generate_series() 函数、R-树索引、正则表达式、模糊查找以及 GEO 等。

如果你在寻找 SQLite 管理开发工具，推荐两款免费开源的工具：DBeaver 和 DB Browser for SQLite。

来源 https://mp.weixin.qq.com/s/FouI89tjM6OAPzq23G4mQA

相关文章