0%

临到 “坑” 时方恨少的 SQLite 细节知识

发表于 分类于

最近的项目开发过程中，由于对 SQLite 的一些细节的疏忽，导致数据库升级发生异常问题，引起笔者对 SQLite 基础知识的重视。本文将对 SQLite 中涉及的一些易疏忽点进行介绍。

如何修改表结构

在 SQLite 中，如果让你删除 PERSON 表中的 Birthday 列，熟悉 SQL 语句的你会不会毫不犹豫地输入下列语句，然后按下回车呢？

1
2
ALTER TABLE Person
DROP COLUMN Birthday

如果是的，那么恭喜你，挂了。事实上，SQLite 对表的修改有诸多限制，我们来看看。

ALTER TABLE 有限子集

SQLite 仅支持 ALTER TABLE 操作的 有限子集。SQLite 中的 ALTER TABLE 命令只允许用户重命名表、重命名表中的列、或将新列添加到现有表。

不支持 DROP COLUMN 删除一列、ALTER COLUMN 修改某列的数据类型，甚至在版本 3.25.0（2018-09-15）之后，重命名表操作才会对外键约束、视图引用进行修改。

那么，如果一定要对表结构进行复杂的修改，应该如何做呢？最佳答案是，不要这么做！

开玩笑的，SQLite 官方告诉了我们一种较为可靠的方式，聪明的你拍一下脑袋也能想到，即: 创建新表，复制数据后再丢弃旧表。

image-20190401194154638

可是，有两种方案可供选择，应该如何选择呢？

乍眼看过去，没有多大区别，无非是方案一的事务锁粒度可以更小，更加安全可靠，这当然没错，是一个原因。事实上，也应该 选择方案一，避免使用方案二。

然而，更重要的原因是: 在 SQLite 3.25.0 版本后，方案二中第一步的重命名表操作，会将 Old 表引用的触发器、视图、外键约束等引用进行重命名操作，这将导致严重错误。

在 3.25.0 版本以上，可通过 PRAGMA legacy_alter_table=ON 启动兼容特性，当然，不推荐该方式。

附: Android 版本对应的 SQLite 版本 https://developer.android.com/reference/android/database/sqlite/package-summary.html

从 Android 5.0 开始，SQLite 版本几乎伴随 Android 版本进行升级:

Update

最后，对应的操作顺序如下（参考 SQLite 官方提供的方法）:

1、禁用外键约束

使用 PRAGMA foreign_keys = OFF 禁用它们

2、开启事务

3、记住与 Old 表关联的所有索引和触发器的格式

下面的步骤 8 中将需要此信息，可通过 SQL 语句查询: 

1
SELECT type, sql FROM sqlite_master WHERE tbl_name='Old'

4、使用 CREATE TABLE 创建一个新表 New

新表的 Scheme 和旧表一致，同时，确保 New 名称不会与任何现有的表名冲突

5、复制插入数据

使用如下 SQL 语句将内容从 Old 传输到 New: 

1
INSERT INTO New SELECT ... FROM Old

6、删除旧表 Old

1
DROP TABLE Old

7、对新表 New 进行重命名

1
ALTER TABLE New RENAME TO Old

8、重建索引和触发器

使用 CREATE INDEX 和 CREATE TRIGGER 重建与 Old 表关联的索引和触发器，参考第 3 步中保存的结果

9、重建 View 视图

如果有引用该表的 View 视图，要根据 Scheme 分析是否需要重建视图，使用 DROP VIEW 和 CREATE VIEW 命令

10、检查外键约束

如果最初启用了外键约束，则运行 PRAGMA foreign_key_check 验证表结构更改未破坏任何外键约束

11、提交事务

提交在步骤 2 中启动的事务

12、重新启用外键约束

使用 PRAGMA foreign_keys=ON 重新启用外键约束

关于数据类型的那些事

先提出几个问题，大家可以在心中思考后回答，之后再对基础知识进行讲解。

前提，假设在 SQLite 中有一张表是这样的:

1
2
3
4
5
create table person(
	id integer primary key,
	name varchar,
	age integer
);

为了便于阅读，此处将 SQL 语句全部小写。

那么问题来了，Come on:

  • age 是 integer 类型的，放入 18 自然是没有问题的，那如果我放入 18.5 会报错吗
  • 如果不报错，结果是 18 还是 19 呢
  • 如果我放入一个包含单引号字符串形式 「18.5」 呢
  • 最后暴躁了，单引号字符串形式放入时，如果不小心多输入一个小数点，为「18..5」又如何呢

如果上面的问题都能解决，就，别急，还有问题:

  • 如果我不是将数据放入 age，而是放入 integer primary key 类型的 id 列，上述的情况又如何呢？

  • name 是 varchar 类型，如果修改为 varchar(20)，那我放入一个长度大于 20 的字符串会怎么样？如果是标准 SQL 呢？

  • 如果有一条数据的 name 列刚好是字符串「name」，那么下面的 SQL 语句为什么不能查询出期望的结果？

    1
    select * from person where name="name";

    SQL

OK，暂时问这么多问题，你都回答上来了吗？它们中是否总有几个出乎你意料的答案，接下来就来详细讲解 SQLite 这些知识吧。

SQLite 的动态类型

大多数 SQL 数据库引擎都使用静态，严格的类型。使用静态类型时，值的数据类型由其容器（存储值的特定列）确定，而 SQLite 使用更通用的 动态类型 系统。

在存储时，会存储为以下形式 INTEGER、REAL、TEXT、BLOB 或 NULL。

所以在插入数据的时候，任意列可以放入上述任意类型的数据，不会受到 Scheme 的强制约束，SQLite 都将会将其存入数据库文件中。

但是，唯有 INTEGER PRIMARY KEY 列只能放入 64 位的整型数字，否则会抛 Error: datatype mismatch 异常

而在其它 SQL 数据库引擎中，如 MySQL，如果放入不匹配类型的数据，通常会先尝试类型转换，如 integer 列放入 18.5 会转换为 19，再进行放入，如果转换失败，则直接报错。

字符串长度的问题

众所周知，在大多数 SQL 数据库引擎中，varchar 表示的是可变长字符串，而 varchar(n) 伴随的参数 n 通常代表的是该字符串列的最大长度。

此处提一下 SQL 基础知识中 integer(n) 和 varchar(n) 的区别，避免混淆，前者的 n 是指数字显示宽度，和存储空间无关，后者的 n 才是指最大存储长度，和存储空间有关

可是，在 SQLite 中却不是这样:

SQLite 中将无视 varchar(n) 的参数 n，其会将 varchar(n) 翻译为 TEXT 类型，尽管在 Scheme 中指定 varchar(10)，也可以在该列放入长度为 5 亿的字符串。

单引号和双引号

关于单引号和双引号，其实是 SQL 标准的内容，但不同数据库的实现都有差异（如 MySQL），给很多开发者造成了误导，而 SQLite 是严格按照 SQL 标准实现的。

SQL 标准中规定 单引号 用于包裹字符串，而 双引号 用于转义包含关键字的语句。如: 

1
2
3
4
5
# 单引号
INSERT INTO T(t) VALUES('5 O''clock');

# 双引号
SELECT "column" FROM T WHERE foo = "bar"

Backticks 反引号「`」在 SQL 中等同于双引号

StackOverFlow 上有个助记的方法:

StackOverFlow

工程化中关心的问题

INSERT 数据速度慢

SQLite 官方给出的数据 INSERT 速度可以达到 5w 次 / 秒，而在实际工程开发中，会发现 INSERT 非常慢。如，在 7200 转的机械硬盘最高只能达到 60 次 / 秒。

事实上，在 SQLite 中，每一个 INSERT 语句中包含了一个事务，在每一次 INSERT 中，会经历开启事务、写入数据、提交事务过程。而事务的操作会造成多次读写，耗费性能，故性能极低。

解决方案就是在 INSERT 批处理语句的外层，整体使用事务包裹，就能够批量 INSERT 只开启一个事务，避免每一个 INSERT 都开启一个事务。

SQLite 的 Free-List

在实际工程中，删除大量 SQLite 数据库数据时，数据库文件的大小并不会减少。

因为 SQLite 存在 Free-List 机制，在删除数据时，空闲的空间会移交给 Free-List 供下次写入数据使用，而不会交给操作系统。

可以通过 VACUUM 命令或者启用 auto_vacuum 模式对数据进行重建整理

结语

本文介绍的 SQLite 疏忽点希望大家都已掌握，避免在日常开发中犯此类错误。还有一些其它的差异点，在平时的 Android 开发中可能不太遇得到，可以参考腾讯云的一篇文章 这些SQLite与SQL差异问题，你遇到过吗。