只会MySQL？来看看SQLite！
MySQL 是被程序员广泛使用的 DBMS 之一，在中国，很多初学者都会自然地把 SQL 查询语言和 MySQL 画上大大的约等号。因为 MySQL 免费、使用范围广、易于上手，大多数中小型项目直接上 MySQL 都没有太大问题。
最近我在为一个项目进行技术选型时，考虑到项目本身只是一个后台管理系统，不存在大量并发，希望可以更加轻量地部署，而 MySQL 这样的数据库似乎显得过于庞大了——我只是需要存储少量的一些数据，还要单独部署一个 MySQL 服务？
因此，我注意到了更加轻量的，基于单文件的内嵌数据库——SQLite。相比 MySQL，他有以下特性：
- SQLite 是内嵌的，不对外提供服务
- SQLite 在类型上是灵活的
- SQLite 没有布尔型变量
- SQLite 没有日期类型
- SQLite 中表的数据类型是可选的
- SQLite 中的外键功能默认关闭
- SQLite 中的主键有时可为空（null）
- SQLite 中，聚合查询的结果可以包含非 GROUP BY 的列
- SQLite 默认不执行完整的 Unicode 大小写折叠
- SQLite 把双引号作为字面值
- SQLite 支持把关键字作为标识符
- SQLite 秉承模棱两可的 SQL 即不报错的原则
- SQLite 中的 AUTOINCREMENT 逻辑和 MySQL 不一样
- SQLite 允许在字符串中使用 NUL 字符
# SQLite 是内嵌的，不对外提供服务
SQL Server, PostgreSQL, MySQL, Oracle 等一众数据库都是基于 C/S 架构，提供一个单独的服务，他们需要被独立部署，相当于一个独立的程序，可以灵活地给其他程序提供服务。然而，SQLite 和他们最大的区别就是，SQLite 内嵌于程序中，直接通过函数调用来访问，而不需要跨进程访问。
这让 SQLite 具备强大的可移植性和轻量度，它不会给其他程序进程提供访问服务。
# SQLite 在类型上是灵活的
这点可能和大家的习惯不符，比如，在 MySQL 的表里创建一个Integer的字段，你尝试存一个非整数进去，MySQL 会拒绝这个操作，并且报错，以维持数据结构的可靠和稳定性。
但是 SQLite 会允许你在一个Integer字段里存字符串，也允许你在一个VARCHAR(10)的字段里存长度大于 10 的字符串，而且不报任何异常。
那在 SQLite 里指定字段类型的意义在哪？
根据官方文档，如果你尝试在
Integer里存一个字符串，SQLite 会尝试把字符串转换成整数，如果可以合法转换，SQLite 会存储转换后的整数值进去，如果不能，才会存储原字符串进去。此规则对其他数据类型也适用。设计存储配置信息的表的时候，对于配置信息键值对，值往往可能是各种各样的类型，比如文本、数字，甚至二进制文件。常规数据库要实现这个功能，就需要对不同的类型开对应的字段，而 SQLite 直接用一个字段就搞定
如果需要把已有的一些脏数据（比如 CSV 文件里存储的，每列数据类型各有不同的数据）导入数据库，SQLite 会简单很多
这种动态类型的风格，和动态类型的编程语言能更好地搭配使用
在定义字段类型时，SQLite 甚至可以接受一切值，不一定是 SQLite 自身支持的类型。这让你很方便地在想要后期切换数据库时减少心智负担，用同一个 SQL 就行
可以看出，SQLite 的思想核心是自由，它不会给程序员太多限制，把控制权更多地带给程序员。
当然，这种灵活的特性是前所未有的，因此肯定带来了代价，最显而易见的代价就是容易插入脏数据，让后续的读取操作变得危险。
如果不喜欢这种灵活的特性，SQLite 提供了严格模式（STRICT），把控制权收回。
可以看看The Advantages Of Flexible Typing，详细了解灵活类型的优缺点。
# SQLite 没有布尔型变量
SQLite 把TRUE和FALSE直接看作1和0。这通常不会造成问题，但是还是需要心里有底。
# SQLite 没有日期类型
要存储日期类型，可以直接存：
ISO-8601 格式的字符串，如”2018-04-02 12:13:46“
整型 unix 时间，单位秒
SQLite 提供了日期处理函数方便处理日期。
# SQLite 中表的数据类型是可选的
在定义表结构时，基于 SQLite 的灵活类型特性，甚至不需要指定数据类型：
CREATE TABLE t1(a,b,c,d);
这代表你可以存任何数据在里面，SQLite 不会对尝试存入的数据做特别处理。
# SQLite 中的外键功能默认关闭
这主要是历史原因。SQLite 的外键功能没有在一开始就实装，但是实装后市面上存在大量错误使用外键功能的软件，SQLite在更新时把向后兼容性看作第一要务，因此实装后并没有默认开启外键功能，避免影响已存在的软件。
# SQLite 中的主键有时可为空（null）
除了有INTEGER PRIMARY KEY或WITHOUT ROWID的表，SQLite 中的主键（Primary Key）和唯一约束（UNIQUE）是等效的。由于历史原因，主键的值可以为空。这是 BUG，但是由于 SQLite 重视向后兼容性，因此还是保留了这个”特性“。
不过，指定了INTEGER PRIMARY KEY或WITHOUT ROWID的表不会存在这个问题。
# SQLite 中，聚合查询的结果可以包含非 GROUP BY 的列
在 MySQL 中，用GROUP BY进行聚合查询时，SELECT中指定的返回数据只能是聚合函数或者GROUP BY中引用的列，如：
SELECT DEPT, MAX(SALARY)
FROM STAFF
GROUP BY DEPT
我们不能同时返回 STAFF 表中其他诸如员工姓名的数据，显然这没有很大的意义，因为返回的结果每一个列可能是由多个列计算得到的，不能直接和单一列数据绑定。
但 SQLite 允许我们这么做。
下面的测试在 DataGrip 进行，SQLite 3.36.0
在这个表中：
| DEPT | SALARY | NAME |
|---|---|---|
| d1 | 209 | Mike |
| d2 | 109 | Nuke |
| d1 | 209 | Tom |
| d3 | 123 | Amy |
| d2 | 111 | Coke |
我们进行：
SELECT NAME from STAFF GROUP BY DEPT
会返回：
| NAME | |
|---|---|
| Mike | |
| Nuke | |
| Amy |
多次执行，返回值相同。看起来，SQLite 会按 rowid 升序排序聚合查询的各 DEPT，把每组的第一列数据作为返回。这里似乎和官网文档的描述不一致：
Each expression in the result-set is then evaluated once for each group of rows. If the expression is an aggregate expression, it is evaluated across all rows in the group. Otherwise, it is evaluated against a single arbitrarily chosen row from within the group. If there is more than one non-aggregate expression in the result-set, then all such expressions are evaluated for the same row.
利用这个特性，我们可以直接用聚合查询查出薪水最高的员工：
SELECT max(SALARY), NAME from STAFF
这个查询会返回（多次查询结果一致）：
| ”max(SALARY)“ | NAME |
|---|---|
| 209 | Mike |
看起来，官网文档描述的”随机性“并没有体现，和上面的查询一样，返回的是按 rowid 升序排序的第一个符合条件的数据。
With SQLite (but not any other SQL implementation that we know of) if an aggregate query contains a single min() or max() function, then the values of columns used in the output are taken from the row where the min() or max() value was achieved. If two or more rows have the same min() or max() value, then the columns values will be chosen arbitrarily from one of those rows.
如果查询中没有任何聚合函数，GROUP BY会发挥和DISTINCT ON一样的作用，过滤组中相同的元素。
我们执行：
SELECT NAME from STAFF GROUP BY SALARY
返回：
| Name | |
|---|---|
| Nuke | |
| Coke | |
| Amy | |
| Mike |
可见，薪水和 Mike 相同的 Tom 没有出现在结果集中。
# SQLite 默认不执行完整的 Unicode 大小写折叠
SQLite 不知道所有 unicode 字符的大小写规则，SQL 中的upper()、lower()函数只在 ASCII 字符生效。
要让 SQLite 支持这个特性，需要在编译时添加-DSQLITE_ENABLE_ICU（笔者还不知道具体怎么实操）
# SQLite 把双引号作为字面值
标准 SQL 查询语言需要用双引号包裹标识符（在需要用关键字作为字段等的名字时），用单引号包裹字符串常量，SQLite 同样支持。
但是，如果 SQLite 发现双引号包裹的不是标识符，会把双引号看作一个普通的字符串常量。也就是说，如果你本来想要用双引号来表示包裹标识符，但是关键字拼错了，SQLite 不会报错，反而会把双引号带着里面的文本一起作为一个字符串。
（你可能猜到了，这个”特性“存在到现在的原因，还是因为 SQLite 想要保持向后兼容性）
从 SQLite 3.27.0 开始，使用双引号包裹字符串会产生 warning 日志。
从 SQLite 3.29.0 开始，支持在运行时更改这一特性，下面是 C 语言代码：
sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DDL, 0, (void*)0);
sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DML, 0, (void*)0);
# SQLite 支持把关键字作为标识符
直接上代码：
CREATE TABLE union(true INT, with BOOLEAN);
可以看到，SQLite 支持把true和with作为字段名。这个特性主要让 SQLite 能够更好的向后兼容，老版本代码不会因为新版本添加了关键字而失效。
# SQLite 秉承模棱两可的 SQL 即不报错的原则
就像上面提到的双引号和灵活字段，SQLite 倾向于不做过多的校验，把控制权给多的给到开发者。
# SQLite 中的 AUTOINCREMENT 逻辑和 MySQL 不一样
MySQL 中我们经常用到主键自增 id，在 SQLite 中，这个概念叫做：ROWID
# ROWID
在 SQLite 中，只要不是WITHOUT ROWID表，每个表都有一个 64 位有符号整型字段：ROWID。这个字段可以在 SQL 语句中用ROWID、_ROWID_、OID访问到（只要表里没有你自己定义的同名字段）。如果表里有一个INTEGER PRIMARY KEY类型的字段，如：
CREATE TABLE t(id INTEGER PRIMARY KEY, y, z);
则这个字段会成为 ROWID 的一个别名，通过它也可以访问到 ROWID。
插入新数据时，SQLite 遵循以下规则：
如果你在
INSERT时没有指定 ROWID，或者指定的 ROWID 是NULL，SQLite 会自动生成合适的 ROWID。一般来说，新的 ROWID 是目前表里最大的 ROWID+1。
如果表是空的，那么 ROWID 是 1。
如果最大的 ROWID 是最大的 64 位整数（9223372036854775807），那么 SQLite 会开始一个个找表里已存在的 ROWID，知道找到一个没用过的为止。如果全用过了，会抛出 SQLITE_FULL 错误。
如果没有自行指定为负的 ROWID，自动生成的 ROWID 永远大于 0。
例如，一个表里只有一条 ROWID 为-2 的记录，如果有新数据插入，SQLite 自动创建的 ROWID 是-1。
也就是说，在正常情况下，只要 ROWID 不大于最大值，而且你没有删除过记录，ROWID 会保持单调递增。只有在最大 ROWID 等于最大值，且你删除过记录时，才可能出现新 ROWID 非单调递增的情况。
# AUTOINCREMENT
接下来，了解一下 SQLite 中AUTOINCREMENT的特殊规则：
AUTOINCREMENT改变了上述 SQLite 中 ROWID 的运算规则，因此其不能在没有 ROWID 的表中使用，即不能在WITHOUT ROWID的表中使用；也不能在非INTEGER PRIMARY KEY的字段使用，因为没有意义。AUTOINCREMENT让新的 ROWID 选择之前用过的最大值+1。如果表是空的，ROWID 是 1。
如果最大的 ROWID 已经被用过了，新的
INSERT动作永远不被允许，抛出 SQLITE_FULL 错误。在事务中，只有之前事务的 ROWID 数据会被纳入考虑，事务回滚后释放的 ROWID 可以被再次使用
可以看出，AUTOINCREMENT最大的改变是不根据当前表的最大 ROWID，而是通过历史用过的最大 ROWID 决定下一次的 ROWID 是什么。这能够更严格地保证 ROWID 是单调递增的。AUTOINCREMENT的意义在于，保证避免使用之前使用过，但已删除的 ROWID。而默认算法其实只要 ROWID 不要过大，也不会出现这个问题。
# 最佳实践
AUTOINCREMENT需要花费额外的 CPU、内存、磁盘空间和 I/O，绝大多数情况下，如果不需要追求 ROWID 会非常大的情况下保持严格单调递增，不要使用。
# SQLite 允许在字符串中使用 NUL 字符
NUL（\u0000）可能会在字符串中间出现，这可能会导致意外的行为。详见文档。