SQLite 二周目

很久以前写过一个很屎的 SQLite C++ binding ，实在是太屎了，最近想用的时候发现我自己已经看不懂了，研究了一会之后我发现我设计的非常失败， API 嗯抄 Python 的 sqlite3 模块，但由于实力不足，抄成了一坨，而且还非要用 PIMPL ，导致源文件非常臃肿，而且因为 sqlite3* 和 sqlite3_stmt* 指针的处理不当，在某些情况会 segfault ，还有些情况会内存泄漏。所以打算重新设计一个 binding ，趁此机会捡一捡 SQL 的基础。

新的 binding 已经基本实现好了~~（非常的新鲜，非常的美味）~~，放在了我发起的「ExDinner - 超级低能」组织里了，项目名为 sqlitemm

参考内容：

MySQL Crash Course / MySQL 必知必会
SQLite 文档

1. SQLite 没有实现的一些 SQL feature

1.1. `ALTER TABLE` 实现不完整

只支持 RENAME TABLE, ADD COLUMN, RENAME COLUMN 和 DROP COLUMN 。

1.2. trigger 实现不完整

FOR EACH ROW trigger 是支持的， FOR EACH STATEMENT trigger 是不支持的。

1.3. `VIEW` 是只读的

不能对 VIEW 执行 DELETE, INSERT 或 UPDATE 语句。但写一个 trigger 对 VIEW 进行修改是可以的。

1.4. 没有 `GRANT` 和 `REVOKE`

一般 CS 架构的 DBMS 才会用这种东西， SQLite 中这种语句没有意义。

2. 基本数据类型

SQLite 只有 5 种数据：

类型	解释
`NULL`	顾名思义
`INTEGER`	根据实际值，占用 1 2 4 6 8 字节的有符号整数
`REAL`	8 字节 IEEE 浮点
`TEXT`	貌似没有长度限制的字符串
`BLOB`	貌似没有长度限制的二进制数据

2.1. 布尔值

SQLite 使用 INTEGER 的 0 和 1 表示 TRUE 和 FALSE ，相反 TRUE 和 FALSE 在 SQLite 中仅为 0 和 1 的别名。

2.2. 日期和时间

SQLite 没有原生的日期和时间类型，但可以用其他类型间接表示：

INTEGER 表示 Unix 时间
TEXT 保存 ISO8601 时间（ YYYY-MM-DD HH:MM:SS.SSS ）

3. SQL 基础

关键词	解释
database	顾名思义，但 SQLite 和 MariaDB 之类称为 DBMS
table	顾名思义
schema	模式，数据库和表的布局及特征信息，比如 SQLite 每个数据库都有一个 sqlite_schema 表
column	顾名思义
datatype	顾名思义
row	顾名思义
primary key	主键，顾名思义
clause	子句，比如 `SELECT`, `FROM` 和 `ORDER BY` 都算子句
operator	顾名思义，一般用在 `WHERE` 子句，也叫 logical operator ，比如 `AND`, `OR`

SQL 读作 S-Q-L 或 sequel ，而不是 circle

3.1. 检索

SELECT * FROM foo;
SELECT foo, bar FROM baf;
SELECT baf.foo FROM baf;

3.1.1. `DISTINCT`

SELECT DISTINCT foo FROM bar;
SELECT DISTINCT foo, bar FROM baf;

这个关键词作用到所有列，第二个语句的输出可能包含 foo 列相同但 bar 列不同的两行数据。

3.1.2. `LIMIT`

SELECT foo FROM bar LIMIT 5;
SELECT foo FROM bar LIMIT 5, 5;
SELECT foo FROM bar LIMIT 5 OFFSET 5;

第二个语句表示输出 ${rows:5:5} (shell) ，第三个语句为第二个语句的上位替代，因为它的语义更明确

3.1.3. `ORDER BY`

SELECT a, b FROM foo ORDER BY c, d; -- 默认为升序 (ASC)
SELECT a, b FROM foo ORDER BY c DESC, d ASC; -- c 列降序， d 列升序

3.1.4. `WHERE`

SELECT a FROM foo WHERE b == 'c';
-- 注意 WHERE 和 ORDER BY 的前后顺序决定了语句的语义
SELECT a FROM foo WHERE b == 'c' ORDER BY d;

SELECT a FROM foo WHERE b BETWEEN 114 AND 514;
SELECT a FROM foo WHERE b IS NULL; -- 不能写成 `b == NULL`

这里需要注意的是 SQLite 对于字符串比较是大小写敏感的， i.e. "Foo" != "foo"

3.2. 操作符

SELECT a FROM foo WHERE b == 'c' AND c == 'd';
SELECT a FROM foo WHERE (b == 'c' OR b == 'd') AND c == 'E';
SELECT a FROM foo WHERE (b IN ('c', 'd')) AND c == 'E';
SELECT a FROM foo WHERE (b NOT IN ('c', 'd')) AND c == 'E';

3.3. 通配符

LIKE 子句可以使用通配符 (wildcard) ，比如 % 表示任何字符出现任何次数、 _ 匹配单个字符； 114_1419% 这种叫做搜索模式 (search pattern) 。注意 '%' 也不能匹配 NULL

这里需要注意的是 SQLite 的 LIKE 子句貌似不是大小写敏感的， i.e. "Foo" == "foo"

SELECT a FROM foo WHERE b LIKE '114_1419%';

3.4. 正则表达式

SELECT a FROM foo WHERE b REGEXP '114\d+19810';

在我测试下， SQLite 的正则表达式支持了几乎我能想到的所有关键字

3.5. 内置函数

节选了一些内置函数，见完整列表

-- 字符串
SELECT upper('foo') AS foo;
SELECT lower('FOO') AS foo;
SELECT length('foo');
SELECT concat('114', '514');
SELECT '114' || '514'; -- 同样是拼接
SELECT ltrim('  114');
SELECT rtrim('514  ');
SELECT trim('  114514  ');
SELECT substring('foobar', 2, 2); -- 等同于 shell `${foobar:2:2}`
SELECT hex('abc'); -- `616263`
SELECT hex(randomblob(8)); -- hex str of random 8 bytes
SELECT unicode('😂'); -- code point of '😂'

-- 数数数数学
SELECT a * b AS foo, c FROM bar;
SELECT abs(-114);
SELECT sin(45);
SELECT acos(45);
SELECT exp(10);
SELECT sqrt(16);
SELECT mod(10, 3);
SELECT pi();
SELECT random();

-- 日期和时间
SELECT time();
SELECT date();
SELECT datetime();
SELECT unixepoch();
SELECT date('now', 'start of month', '+1 month', '-1 day'); -- 这个月的最后一天
SELECT datetime(1145141919, 'auto');

-- 数据聚合
SELECT avg(a) FROM foo;
SELECT avg(DISTINCT a) FROM foo; -- 只取样不重复的值
SELECT count(a) FROM foo GROUP BY b;
SELECT group_concat(a) FROM foo GROUP BY b;
SELECT group_concat(a, ', ') FROM foo GROUP BY b;
SELECT max(a) FROM foo;
SELECT sum(a * b) FROM foo;

3.6. 数据分组

SELECT a, count(*) AS a_count FROM foo GROUP BY a;
SELECT a, count(*) AS a_count FROM foo GROUP BY a HAVING count(*) >= 2;

SELECT 子句顺序
子句说明使用情况
SELECT 选择表的某个（些）列或表达式必须使用
FROM 选择某个（些）表需要选择表时使用
WHERE 行级过滤对每行进行限定时使用
GROUP BY 进行分组对行进行分组时使用
HAVING 组级过滤对组进行限定时使用
ORDER BY 输出排序顾名思义
LIMIT 限制输出行数顾名思义

子句	说明	使用情况
`SELECT`	选择表的某个（些）列或表达式	必须使用
`FROM`	选择某个（些）表	需要选择表时使用
`WHERE`	行级过滤	对每行进行限定时使用
`GROUP BY`	进行分组	对行进行分组时使用
`HAVING`	组级过滤	对组进行限定时使用
`ORDER BY`	输出排序	顾名思义
`LIMIT`	限制输出行数	顾名思义

4. 高端操作

sqlite> SELECT unhex('e9ab98e7abafe6938de4bd9c') AS 队友呢队友呢救一下啊;
┌──────────────────────┐
│ 队友呢队友呢救一下啊 │
├──────────────────────┤
│ 高端操作             │
└──────────────────────┘

4.1. 子查询

SELECT a FROM foo WHERE b IN (
  SELECT b FROM bar
);

SELECT a, (
  SELECT count(*) FROM bar WHERE foo.b == bar.b
) AS b_count FROM foo;

4.2. 联结 (`JOIN`)

显式使用 WHERE 的联结：

SELECT a, b FROM foo, bar WHERE foo.c == bar.c;

4.2.1. 内部联结 (`INNER JOIN`)

SELECT a, b FROM foo INNER JOIN bar ON foo.c == bar.c;

和上面使用 WHERE 的那句输出是一致的，不清楚有什么区别， Gemini 跟我说这种 INNER JOIN 也会隐式使用 WHERE ，所以两种是等价的

4.2.2. 自联结

这两句是等价的，但第二种更好：

SELECT id, name FROM products WHERE id == (
    SELECT id FROM products WHERE type == 'foo'
)

SELECT p1.id, p1.name FROM products AS p1, products AS p2 WHERE p2.type == 'foo' AND p1.id == p2.id;

4.2.3. 自然联结

指联结多个表时输出没有重复的列的联结，目前所有联结都算作自然联结

4.2.4. 外部联结 (`OUTER JOIN`)

上面内部联结的

SELECT a, b FROM foo INNER JOIN bar ON foo.c == bar.c;

会导致输出只包含 foo 和 bar 两个表中都含有 c 列数据且一致的行，有时需要以其中某个表为准：

SELECT foo.a, bar.b FROM foo LEFT OUTER JOIN bar ON foo.c == bar.c;

这句会输出 foo 表的所有行的 a 列，以及 bar 表中对应 c 列相同的行的 b 列，如果没有对应的 b 列则会填 NULL ，在 SQLite CLI 中显示为空

4.3. 组合查询 (`UNION`)

SELECT a FROM foo WHERE b < 5
UNION
SELECT a FROM foo WHERE c IN (114, 514);

会输出 foo 表中 b 值小于 5 以及 c 值在 114 和 514 之间的所有行

4.3.1. 去重

默认 UNION 会去掉重复的行，如果不需要去重可以加上 ALL 关键字：

SELECT a FROM foo WHERE b < 5
UNION ALL
SELECT a FROM foo WHERE c IN (114, 514);

4.3.2. 排序

可以在最后的最后加上 ORDER BY 表示对所有结果进行排序，不可以对某个 SELECT 加：

SELECT a FROM foo WHERE b < 5
UNION ALL
SELECT a FROM foo WHERE c IN (114, 514)
ORDER BY d;

5. 数据操作

-- 狠狠地向纳西妲注入脱氧核糖核酸
INSERT INTO nahida VALUES('DNA');
-- 刚刚有点操之过急了
INSERT INTO nahida VALUES('おちんちん'), ('DNA');
-- 刚刚是不是放错地方了
INSERT INTO nahida(おまんこ) VALUES('おちんちん'), ('DNA');
-- 别问，问就是恨不得把。。。
INSERT INTO nahida(おまんこ) SELECT * FROM me;
-- 狠狠地开发
INSERT INTO nahida(おまんこ, 口) VALUES('おちんちん', 'おちんちん'), ('DNA', 'DNA');

5.1. `UPDATE`

UPDATE foo
SET a = NULL
WHERE b = 114514;

把 foo 表中 b 列值为 114514 的行的 a 列值改为 NULL

5.2. `DELETE`

DELETE FROM foo
WHERE b = 114514;

删除 foo 表中所有 b 列值为 114514 的行

6. 数据库管理

6.1. 表格

6.1.1. 创建表格

详细语法见 CREATE TABLE —— SQLite 文档

CREATE TABLE foo (
  id   INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  name TEXT    DEFAULT 'bar'
)

6.1.2. 修改表格

详细语法见 ALTER TABLE —— SQLite 文档

ALTER TABLE foo ADD COLUMN type TEXT;
ALTER TABLE foo DROP COLUMN type;
ALTER TABLE foo RENAME TO bar;

6.1.3. 删除表格

DROP TABLE foo;

6.2. 视图 (`VIEW`)

CREATE VIEW foobar AS
SELECT a, b, c FROM foo, bar WHERE foo.d == bar.d;

foobar 就是下面那句 SELECT 的别名了，在此基础上可以进行筛选：

SELECT a, b FROM foobar WHERE c == '114514';

6.3. 触发器 (`TRIGGER`)

有点像回调，详细语法见 CREATE TRIGGER —— SQLite 文档

CREATE TRIGGER newfoo AFTER INSERT ON foo FOR EACH ROW BEGIN
    SELECT 'Row added';
END;

每次往 foo 表中插入数据时执行 SELECT 'Row added'; ，但亲测 SQLite CLI 不会输出任何内容。不想要了可以删除：

DROP TRIGGER newfoo;

SQLite 支持 BEFORE, AFTER, INSTEAD OF 于 DELETE, INSERT, UPDATE 操作的触发器

6.4. 事务 (`TRANSACTION`)

说白了就是保证某一系列操作是完整的，如果其中出现了错误则恢复到执行操作之前的状态。详细语法见 Transaction —— SQLite 文档

TRANSACTION: 一组 SQL 语句
ROLLBACK: 顾名思义
COMMIT: 类似 Git ，把暂存的结果写进数据库
SAVEPOINT: 顾名思义，学习水龙的品德

SELECT * FROM foo; -- foo 表本来有数据
BEGIN TRANSACTION; -- 事务开始（学习龙王的品德）
DELETE FROM foo;   -- rm -rf /*
SELECT * FROM foo; -- 数据都没了
ROLLBACK;          -- 还好我学习龙王的品德，给自己留后路了
SELECT * FROM foo; -- 你复活辣

事务中的语句产生的结果不会自动保存到数据库里，需要手动 COMMIT; ：

BEGIN TRANSACTION;
DELETE FROM foo;
COMMIT;

这样就保证了 foo 表被删干净了，因为如果事务期间发生了错误，数据库会自动撤销。 START TRANSACTION 对应 COMMIT 或 ROLLBACK 表示事务结束。需要更精细的 ROLLBACK 可以使用 SAVEPOINT ：

BEGIN TRANSACTION;
-- foo
SAVEPOINT foo;
-- bar
ROLLBACK TO foo;

SAVEPOINT 在事务结束后会自动释放，也可以手动释放，详细语法见 Savepoints —— SQLite 文档：

RELEASE SAVEPOINT foo;

SQLite 与 MariaDB 有一些不同，它默认是 autocommit 模式的，但貌似没有一个语句显式关掉 autocommit ，只能 BEGIN TRANSACTION; 后开启，到 COMMIT; 或 ROLLBACK; 结束。

读后感

其实最近写 sqlitemm 的时候有一些地方文档写的不清楚，比如 SQLite C API 有两种错误信息的获取方式： sqlite3_errmsg 和 sqlite3_errstr ，文档没有写什么情况下应该用哪个。

所以我下载了源码，跟着文档尝试构建（为了用 bear 生成 compile_commands.json 喂给 clangd 使我看源码能不像一个原始人），发现 SQLite 构建前必须把所有源码都粘到一个文件里，官方说法是能让编译器实现更好的优化，这个生成的文件一共 25 万行， clangd 要花十几秒甚至几十秒才能开始正常工作。但不得不说 SQLite 的源码是真的精致，测试用例更是细致到家，有点像艺术品。

说回 SQL ，最近在写一个基于哈希和体积的文件查重工具，想要用 SQLite 实现一个缓存，之前用 Python 实现过一个类似的，但现在某些文件越来越多，所以想好好用 C++ 设计一个更好的版本，希望我能做到。

RayAlto's Blog