临到 “坑” 时方恨少的 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 INDEXCREATE TRIGGER 重建与 Old 表关联的索引和触发器,参考第 3 步中保存的结果

9、重建 View 视图

如果有引用该表的 View 视图,要根据 Scheme 分析是否需要重建视图,使用 DROP VIEWCREATE 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 使用更通用的 动态类型 系统。

在存储时,会存储为以下形式 INTEGERREALTEXTBLOBNULL

所以在插入数据的时候,任意列可以放入上述任意类型的数据,不会受到 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差异问题,你遇到过吗