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

大家好!我是只谈技术不剪发的?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。