[译] 抛弃你的成见，SQLite 不是一个玩具数据库-一一网

无论你是开发人员、数据分析师、QA工程师、DevOps人员，还是产品经理–SQLite都是你的完美工具。

有几个众所周知的事实可以开始了。

SQLite是世界上最常见的DBMS，所有流行的操作系统都支持。
SQLite是无服务器的（serverless）。
对于开发者来说，SQLite 可以直接嵌入到应用程序中。
对于其他人，有一个方便的数据库控制台（REPL），以单个文件的形式提供（Windows上的sqlite3.exe，Linux / macOS上的sqlite3）。

控制台、进口和出口

控制台是SQLite在数据分析方面的杀手锏：比Excel更强大，比pandas 更简单。人们可以用一个命令导入CSV数据，表格会自动创建。

> .import --csv city.csv city
> select count(*) from city;
1117
复制代码

该控制台支持基本的SQL功能，并在一个漂亮的ASCII绘制的表格中显示查询结果。也支持高级SQL功能，但后面会有更多的介绍。

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        │
│ ...        │ ...        │
└────────────┴────────────┘
复制代码

数据可以导出为 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 工具而不是经常使用控制台：流行的数据探索工具如Metabase、Redash和Superset都支持SQLite。

本地JSON

在分析和转换JSON方面，没有什么比 SQLite 更方便。你可以直接从文件中选择数据，就像它是一个普通的表格一样。或者将数据导入表格并从中选择。

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   │
└──────┴─────┴─────────────────┴──────────┘
复制代码

不管 JSON 嵌套有多深 – 你可以提取任何嵌套对象。

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                 │
└────────┴──────────────────────┘
复制代码

CTEs 和 set 操作

当然，SQLite支持通用表表达式（WITH 子句）和JOINs，我在这里都不举例了。如果数据是分层的（表通过像parent_id 这样的列来指代自己）–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     │
│ ...  │ ...                      │ ...   │
└──────┴──────────────────────────┴───────┘
复制代码

Set？没问题：可使用UNION,INTERSECT,EXCEPT。

select employer_id
from employer_area
where area_id = 1

except

select employer_id
from employer_area
where area_id = 2;
复制代码

根据其他几栏来计算一栏？输入生成的列。

alter table vacancy
add 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_net
from vacancy
where
  salary_currency = 'JPY'
  and salary_net is not null
limit 10;
复制代码

数学统计

描述性统计？简单：平均数、中位数、百分位数、标准差，你说了算。你必须加载一个扩展，但它也是一个单一的命令（和一个文件）。

.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 │
└────────────┴──────┴────────┴──────┴─────┴─────┴──────┘
复制代码

关于延期的说明。与PostgreSQL等其他DBMS相比，SQLite缺少很多功能。但它们很容易添加，这就是人们所做的–所以结果是相当混乱。

因此，我决定制作一套一致的扩展，按领域区域划分，并为主要的操作系统进行编译。那里的人还很少，但更多的人正在路上。

sqlean @ GitHub

更多关于统计的乐趣。你可以在控制台中直接绘制数据分布。看看它是多么可爱。

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可以很好地处理数以亿计的记录。普通的INSERT，在我的笔记本电脑上显示每秒约24万条记录。如果你把CSV文件作为一个虚拟表来连接（有一个扩展名）–插入的速度会提高2倍。

.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
复制代码

在开发者中流行一种观点，认为SQLite不适合 Web，因为它不支持并发访问。这是个神话。在先写后收的日志模式下（很久以前就有了），只要你想，可以有多少个并发的读取。可以只有一个当前的写入，但往往一个就够了。

SQLite是小型网站和应用程序的完美选择。sqlite.org使用SQLite作为数据库，不屑于进行优化（每页≈200个请求）。它每月能处理70万次访问，并且比我见过的95%的网站提供的页面都快。

文件、图表和搜索

SQLite支持部分索引和表达式的索引，就像 “大 “DBMS一样。你可以在生成的列上建立索引，甚至把SQLite变成一个文档数据库。只需存储原始JSON并在json_extract()-ed列上建立索引。

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=?)
复制代码

你也可以使用SQLite作为一个图形数据库。一堆复杂的WITH RECURSIVE ，或者也许你更喜欢添加一点Python。

simple-graph @ GitHub

全文搜索开箱即用。

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 │
└─────────────────────┴────────────────────────────────┴────────────┘
复制代码

也许你需要一个内存数据库来进行中间计算？单行的python代码。

db = sqlite3.connect(":memory:")
复制代码

你甚至可以从多个连接中访问它。

db = sqlite3.connect("file::memory:?cache=shared")
复制代码

还有这么多

有花哨的窗口函数（就像在PostgreSQL中）。UPSERT,UPDATE FROM, 和generate_series() 。R-Tree索引。正则表达式、模糊搜索和地理学。在功能方面，SQLite可以与任何 “大型 “DBMS竞争。

围绕SQLite也有很好的工具。我特别喜欢Datasette–一个用于探索和发布SQLite数据集的开源工具。而DBeaver是一个优秀的开源数据库IDE，支持最新的SQLite版本。

我希望这篇文章能激发你尝试SQLite。谢谢你的阅读!

在Twitter上关注@ohmypy，了解新的文章?。

英文原文：antonz.org/sqlite-is-n…

文章版权归作者所有，未经允许请勿转载。

THE END

后端

Cagintranet Networks GetSimple CMS 跨站脚本漏洞

Apple Mac OS X NSURL访问控制绕过漏洞

PCNetSoftware RAC Server 拒绝服务漏洞

Compose Preview 的 UX 设计之旅

Cisco IOS绕开过滤漏洞

精华！消息队列NSQ的源码剖析

[译] 抛弃你的成见，SQLite 不是一个玩具数据库