SQLite:世界上安装使用最多的数据库产品

作者: MM

大家好!我是只谈技术不剪发的 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 > selectcount( *) fromcity ; 1117

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

select century || ' century'asdates, count(*) ascity_count fromhistory groupbycentury orderbycentury 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.json selectcity, foundation_year, timezone fromcity 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') ascode, json_extract( value, '$.iso.number') asnum, json_extract( value, '$.name') asname, json_extract( value, '$.units.major.name') asunit 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 │ └──────┴─────┴─────────────────┴──────────┘

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

select json_extract( value, '$.id') asid, json_extract( value, '$.name') asname from json_tree(readfile( 'industry.sample.json')) where pathlike'$[%].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 很方便地进行遍历。

withrecursivetmp( id, name, level) as( selectid, name, 1aslevel fromarea whereparent_id isnull unionall select area.id, tmp.name || ', '|| area.name asname, tmp.level + 1aslevel fromarea jointmp onarea.parent_id = tmp.id ) select* fromtmp; ┌──────┬──────────────────────────┬───────┐ │ 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 集合运算符:

selectemployer_id fromemployer_area wherearea_id = 1 except selectemployer_id fromemployer_area wherearea_id = 2;

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

altertablevacancy addcolumnsalary_net integeras( casewhensalary_gross = truethen round(salary_from/ 1.04) else salary_from end );

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

select substr( name, 1, 40) asname, salary_net fromvacancy where salary_currency = 'JPY' andsalary_net isnotnull limit10;

统计函数

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

.load sqlite3-stats

selectcount(*) asbook_count, cast( avg(num_pages) asinteger) asmean, cast( median(num_pages) asinteger) asmedian, mode(num_pages) asmode, percentile_90(num_pages) asp90, percentile_95(num_pages) asp95, percentile_99(num_pages) asp99 frombooks; ┌────────────┬──────┬────────┬──────┬─────┬─────┬──────┐│ book_count │ mean │ median │ mode │ p90 │ p95 │ p99 │├────────────┼──────┼────────┼──────┼─────┼─────┼──────┤│ 1483 │ 349 │ 295 │ 256 │ 640 │ 817 │ 1199 │└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘

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

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

with slots as (selectnum_pages/100 as slot,count(*) as book_countfrom booksgroup by slot),max as (select max(book_count) as valuefrom slots)selectslot,book_count,printf('%.' || (book_count * 30 / max.value) || 'c', '*') as bar from 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

createvirtualtabletemp.blocks_csv usingvsv( 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 oninsertintoblocks select* fromblocks_csv;

Run Time: real 5.176 user 4.716420 sys 0.403866selectcount(*) fromblocks; 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 作为文档数据库使用:

createtablecurrency( bodytext, code textas(json_extract( body, '$.code')), nametextas(json_extract( body, '$.name')) );

createindexcurrency_code_idx oncurrency(code);

insertintocurrency selectvaluefromjson_each(readfile( 'currency.sample.json')); explainqueryplan selectnamefromcurrency wherecode = 'EUR'; QUERY PLAN` --SEARCH TABLE currency USING INDEX currency_code_idx (code=?)

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

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

createvirtualtablebooks_fts usingfts5(title, author, publisher);

insertintobooks_fts selecttitle, author, publisher frombooks;

selectauthor,substr(title, 1, 30) astitle, substr(publisher, 1, 10) aspublisher frombooks_fts wherebooks_fts match'ann'limit5; ┌─────────────────────┬────────────────────────────────┬────────────┐│ 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。

谷歌浏览器

检测到您谷歌浏览器版本()过低,建议升级到最新版本 立即升级
×

QQ浏览器

检测到您QQ浏览器版本()过低,建议升级到最新版本 立即升级
×

2345加速浏览器

检测到您2345加速浏览器版本()过低,建议升级到最新版本 立即升级
×

Firefox火狐浏览器

检测到您Firefox火狐浏览器版本()过低,建议升级到最新版本 立即升级
×

傲游浏览器

检测到您傲游浏览器版本()过低,建议升级到最新版本 立即升级
×

UC浏览器

检测到您UC浏览器版本()过低,建议升级到最新版本 立即升级
×

360浏览器

现有新的360浏览器最新版本更新,保护上网安全,建议更新安装。 立即升级
×

搜狗浏览器

现有新的搜狗浏览器最新版本更新,保护上网安全,建议更新安装。 立即升级
×