SQLite

SQLite 是美国程序员 D. Richard Hipp ，用 C 语言实现的跨平台、轻量、开源数据库引擎。由于功能完善又轻量快速，从浏览器、操作系统到手机上都内嵌了此数据库，首个版本于 2000 年发布至今，地球上超过一万亿设备活跃使用。

安装工具

Windows 系统上从官网下载命令行工具，选这个 Precompiled Binaries for Windows - sqlite-tools-win32-x86-版本号.zip - A bundle of command-line tools ... ，下载压缩包后解压到任意目录即可。

Ubuntu 系统上安装官方 sqlite 命令行工具 sudo apt install sqlite3

安装更易用的 sqlite-utils 和 jq 工具

pip install sqlite-utils
apt install jq

sqlite-utils 和 jq 组合使用示例：查询 t1 表一行记录并且将结果以 JSON 格式化

sqlite-utils data.db 'select * from t1 limit 1' | jq

数据类型

主存储类别和类型

NULL 等同 C 语言中 NULL
INTEGER 一个带符号的整数，根据值的大小存储在 0、1、2、3、4、6 或 8 个字节中
REAL 8-byte IEEE 浮点数
TEXT UTF-8, UTF-16BE 或 UTF-16LE 编码文本
BLOB 二进制数据

注：SQLite 和大多数据库引擎不一样，它支持通用的动态类型系统。2021 年 3.37.0 版后可通过创建为 STRICT TABLE 类型表改变。

常用查询

特有语法

列出所有表 .tables

查看表结构 .schema [tableName]

查改增删表

sql

CREATE TABLE `t1` (id text, name text, PRIMARY KEY (`id`));

DROP TABLE t1;

ALTER TABLE t1_new RENAME TO t1;

SQLite SQL 语法和 SQL-92 标准大同小异。

修改表结构或主键

下面以修改 t1 表独立主键 id 为组合主键 (id, owner_id) 为例。

查询得到表旧的表结构

sqlite3 data.db
sh.schema t1

新建空表 t1_new ，为了建新主键，并增新列 owner_id

sql

CREATE TABLE t1_new (
  id TEXT,
  name TEXT,
  owner_id TEXT,
  PRIMARY KEY (id, owner_id)
);

从旧表 t1 复制数据到新表 t1_new

sql

INSERT INTO t1_new (id, name) SELECT id, name FROM t1;

删除旧表 DROP TABLE t1;
重命名新表为旧表名 ALTER TABLE t1_new RENAME TO t1;

基于 FTS5 扩展实现全文搜索

FTS5 扩展基于 SQLite3 虚表实现。从 SQL 语句角度看，虚表和普通表和视图表(view)一样，但

不支持触发器(trigger)
只能在创建是指定索引，不能创建后再修改添加(CREATE INDEX)额外索引
不支持通过 ALTER TABLE ... ADD COLUMN 命令增列
只允许插入(INSERT)或删除(DELETE)，不允许更新(UPDATE)

因此实践中实现全文索引时用两个表，一个表存储原始数据 t，另建一个 FTS5 虚表作 t_fts，并 t_fts 设置为 Contentless Table 。

注：文档提到创建虚表语句 CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='t1', content_rowid='a'); ，数据表名为 t1，虚表名为 fts_idx，content_rowid 参数是 fts_idx 和 t1 关联列，必须为 INTEGER，如果 t1 的主键是 INTEGER，直接设置为主键即可，如果是非 INTEGER 如 TEXT，则 t1 需要新增一个 INTEGER 类型列作为 fts_idx.content_rowid ；

由于虚表限制，需在 t1 上建立触发器，让 fts_idx 和 t1 保持数据一致性。

一个简单基于英文单词中文单字分词的全文索引实现：

如果要索引的列不大，可在 t1 新增一列存储 col_idx 分词后的字符串，这样触发器自动同步更新到 fts_idx 就自动完成索引；
每次创建或更新 t1 时，对要索引的列分词，并设置 col_idx 的值；
英文单词中文单字分词实现并不复杂，不需要额外依赖，核心代码不到 100 行可完成；

SQLite ​

安装工具 ​

数据类型 ​

常用查询 ​

特有语法 ​

查改增删表 ​

修改表结构或主键 ​

基于 FTS5 扩展实现全文搜索 ​