個(gè)人中心
退出
重識(shí) SQLite，簡(jiǎn)約不簡(jiǎn)單
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專(zhuān)欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
命令行工具
原生 JSON
CTE 與集合運(yùn)算
統(tǒng)計(jì)函數(shù)
性能
文檔、圖形以及全文搜索
更多功能
??業(yè)精于勤，荒于嬉；行成于思，毀于隨。——韓愈
大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。
如果問(wèn)你哪個(gè)數(shù)據(jù)庫(kù)產(chǎn)品是世界上使用最多的數(shù)據(jù)庫(kù)，你認(rèn)為是 Oracle、MySQL 還是 Microsoft SQL Server？
以上都不是，世界上安裝使用最多的數(shù)據(jù)庫(kù)是 SQLite。沒(méi)錯(cuò)，就是這個(gè)小巧的嵌入式數(shù)據(jù)庫(kù)引擎。所有的手機(jī)、電腦、瀏覽器以及無(wú)數(shù)的應(yīng)用程序都內(nèi)置了 SQLite 數(shù)據(jù)庫(kù)，PHP 和 Python 語(yǔ)言也內(nèi)置的 SQLite 支持，預(yù)計(jì)正在使用的 SQLite 數(shù)據(jù)庫(kù)達(dá)到了一萬(wàn)億（1012）以上。
無(wú)論對(duì)于開(kāi)發(fā)/測(cè)試人員、數(shù)據(jù)分析師/科學(xué)家、IT 運(yùn)維人員還是產(chǎn)品經(jīng)理，SQLite 都是一個(gè)非常有用的工具。本文就帶大家回顧一下 SQLite 提供的一些實(shí)用功能。如果覺(jué)得文章有用，歡迎評(píng)論??、點(diǎn)贊??、推薦??
命令行工具
SQLite 提供了一個(gè)非常方便的數(shù)據(jù)庫(kù)控制臺(tái)，也就是 Windows 系統(tǒng)上的 sqlite3.exe 或者 Linux / macOS 系統(tǒng)上的 sqlite3。對(duì)于數(shù)據(jù)分析師來(lái)說(shuō)，它比 Excel 強(qiáng)大，但是比 Python pandas 簡(jiǎn)單。用戶(hù)可以通過(guò)命令導(dǎo)入 CSV 文件，導(dǎo)入時(shí)會(huì)自動(dòng)創(chuàng)建相應(yīng)的數(shù)據(jù)表：
> .import --csv city.csv city
> select count(*) from city;
1117
SQLite 命令行工具支持各種 SQL 語(yǔ)句，同時(shí)以 ASCII 風(fēng)格顯示查詢(xún)結(jié)果：
select
century || ' century' as dates,
count(*) as city_count
from history
group by century
order by century desc;
┌────────────┬────────────┐
│ dates │ city_count │
├────────────┼────────────┤
│ 21 century │ 1 │
│ 20 century │ 263 │
│ 19 century │ 189 │
│ 18 century │ 191 │
│ 17 century │ 137 │
│ ... │ ... │
└────────────┴────────────┘
查詢(xún)結(jié)果可以被導(dǎo)出到各種 SQL 命令、CSV、JSON、Markdown 以及 HTML 格式的文件。例如：
.mode json
.output city.json
select 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 工具而不是控制臺(tái)，常見(jiàn)的數(shù)據(jù)探索工具都支持 SQLite，例如 Metabase、Redash 以及 Superset 等。
原生 JSON
SQLite 可以非常方便地分析和轉(zhuǎn)換 JSON 數(shù)據(jù)，用戶(hù)可以直接從文件中查詢(xún)數(shù)據(jù)，也可以將數(shù)據(jù)導(dǎo)入表中然后進(jìn)行查詢(xún)：
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 unit
from
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 │
└──────┴─────┴─────────────────┴──────────┘
無(wú)論 JSON 對(duì)象包含多少層嵌套，SQLite 都可以獲取其中的數(shù)據(jù)：
select
json_extract(value, '$.id') as id,
json_extract(value, '$.name') as name
from
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 與集合運(yùn)算
SQLite 支持通用表表達(dá)式（Common Table Expression）和連接查詢(xún)。對(duì)于具有層級(jí)關(guān)系的數(shù)據(jù)（例如組織結(jié)構(gòu)等），可以通過(guò) WITH RECURSIVE 很方便地進(jìn)行遍歷。
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 集合運(yùn)算符：
select employer_id
from employer_area
where area_id = 1
except
select employer_id
from employer_area
where area_id = 2;
基于其他字段的生成列也不在話(huà)下：
alter table vacancy
add column salary_net integer as (
case when salary_gross = true then
round(salary_from/1.04)
else
salary_from
end
);
生成列可以像其他普通字段一樣查詢(xún)：
select
substr(name, 1, 40) as name,
salary_net
from vacancy
where
salary_currency = 'JPY'
and salary_net is not null
limit 10;
統(tǒng)計(jì)函數(shù)
通過(guò)加載 stats 插件，SQLite 支持以下描述性統(tǒng)計(jì)：均值、中位數(shù)、百分位、標(biāo)準(zhǔn)差等。
.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 p99
from books;
┌────────────┬──────┬────────┬──────┬─────┬─────┬──────┐
│ book_count │ mean │ median │ mode │ p90 │ p95 │ p99 │
├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤
│ 1483 │ 349 │ 295 │ 256 │ 640 │ 817 │ 1199 │
└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘
SQLite 比其他數(shù)據(jù)庫(kù)管理系統(tǒng)提供的函數(shù)更少一些，不過(guò)可以通過(guò)擴(kuò)展插件的方式獲取額外的支持。這個(gè)項(xiàng)目按照不同的領(lǐng)域編譯了一些常用的插件。
以下示例在控制臺(tái)中描繪了一個(gè)數(shù)據(jù)分布圖：
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 bar
from slots, max
order 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 可以支持?jǐn)?shù)以?xún)|計(jì)的數(shù)據(jù)行，在個(gè)人電腦上的普通 INSERT 語(yǔ)句也可以達(dá)到 10 萬(wàn)條/秒以上。如果使用虛擬表連接 CSV 文件，插入性能會(huì)更好：
.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 on
insert into blocks
select * from blocks_csv;
Run Time: real 5.176 user 4.716420 sys 0.403866
select count(*) from blocks;
3386629
Run Time: real 0.095 user 0.021972 sys 0.063716
很多人認(rèn)為 SQLite 不適合作為 Web 應(yīng)用后臺(tái)數(shù)據(jù)庫(kù)，因?yàn)樗恢С植l(fā)訪問(wèn)。實(shí)際上這是一個(gè)謠傳，在write-ahead log 模式下，SQLite 提供了并發(fā)讀取。雖然只能單個(gè)進(jìn)程寫(xiě)入，但是很多情況下已經(jīng)足夠了。
SQLite 非常適合小型網(wǎng)站和應(yīng)用程序。sqlite.org 就是使用 SQLite 作為數(shù)據(jù)庫(kù)，在不需要進(jìn)行優(yōu)化的情況下（每個(gè)頁(yè)面大概包含 200 個(gè)查詢(xún)請(qǐng)求），它可以處理每個(gè)月 70 萬(wàn)的訪問(wèn)量，同時(shí)性能超過(guò) 95% 的網(wǎng)站。
文檔、圖形以及全文搜索
SQLite 支持部分索引和表達(dá)式索引（函數(shù)索引），我們可以基于計(jì)算列創(chuàng)建索引，甚至將 SQLite 作為文檔數(shù)據(jù)庫(kù)使用：
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 currency
select value
from json_each(readfile('currency.sample.json'));
explain query plan
select name from currency where code = 'EUR';
QUERY PLAN
`--SEARCH TABLE currency USING INDEX currency_code_idx (code=?)
有了 WITH RECURSIVE 查詢(xún)，SQLite 也可以作為一個(gè)圖形數(shù)據(jù)庫(kù)使用，或者使用這個(gè) simple-graph（Python 模塊）。
SQLite 提供了內(nèi)置的全文搜索功能：
create virtual table books_fts
using fts5(title, author, publisher);
insert into books_fts
select title, author, publisher from books;
select
author,
substr(title, 1, 30) as title,
substr(publisher, 1, 10) as publisher
from books_fts
where 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 │
└─────────────────────┴────────────────────────────────┴────────────┘
如果想要一個(gè)內(nèi)存數(shù)據(jù)庫(kù)作為中間計(jì)算模塊，只需要一行 Python 代碼就可以搞定：
db = sqlite3.connect(":memory:")
甚至可以支持多個(gè)連接訪問(wèn)：
db = sqlite3.connect("file::memory:?cache=shared")
更多功能
SQLite 還提供了許多其他的高級(jí)功能，例如窗口函數(shù)、UPSERT 語(yǔ)句、UPDATE FROM、generate_series() 函數(shù)、R-樹(shù)索引、正則表達(dá)式、模糊查找以及 GEO 等。
如果你在尋找 SQLite 管理開(kāi)發(fā)工具，推薦兩款免費(fèi)開(kāi)源的工具：DBeaver 和 DB Browser for SQLite。
分類(lèi)導(dǎo)航