SQLite 初学者注意事项

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net




文章目录

        SQLite 是一个嵌入式数据库引擎
        灵活的数据类型
            没有 BOOLEAN 数据类型
            没有 DATETIME 数据类型
            字段类型的可选性
        默认不启用外键约束
        主键可能包含空值
        汇总查询中可以使用非 GROUP BY 字段
        默认不支持完整的 Unicode 字符大小写转换
        双引号字符串可以作为常量
        关键字通常可以用作标识符
        输入不可靠 SQL 语句时不会产生错误或警告
        AUTOINCREMENT 和 MySQL 实现不同
        字符串中允许存在 NUL 字符

大家好，我是只谈技术不剪发的 Tony 老师。

SQL 语言是一个标准，但是没有任何两个数据库管理系统的实现完全相同。每种 SQL 实现都有自己的特性和扩展功能，SQLite 也是如此。本文给大家介绍了 SQLite 和其他数据库实现的主要差异，可以帮助开发人员开发跨数据库支持的系统或者实现数据库的移植。

如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁
SQLite 是一个嵌入式数据库引擎

相对于其他数据库引擎而言，例如 SQL Server、PostgreSQL、MySQL 或者 Oracle，SQLite 最重要的区别在于它的设计目标不是这类数据库系统的替代者。SQLite 是一个嵌入到应用程序中的数据库引擎，没有服务端，没有单独管理数据库的服务器进程。应用程序通过函数调用和数据库引擎进程交互，而不是将消息发送给单独的进程或线程。

客户端/服务器数据库对于现代应用系统非常重要，它们专注于可伸缩性、并发性、集中管理和控制等，解决了企业数据的共享存储问题。 SQLite 则致力于为单个应用程序和设备提供本地数据存储，强调的是经济、效率、可靠性、独立性和简单性。

SQLite 支持的使用场景包括：

    嵌入式设备和物联网应用。例如手机、机顶盒、电视、游戏机、照相机、手表、厨房用具、恒温器、汽车、机床、飞机、远程传感器、无人机、医疗设备和机器人等等。
    桌面应用程序的磁盘文件格式。例如版本控制系统、财务分析工具、媒体编目和编辑套件、CAD 软件包、记录保存程序等。
    中小型网站。对于大多数中小型流量的网站（也就是大多数网站），SQLite 可以作为数据库引擎。 一般来说，每天点击量少于 10 万的网站都可以正常运行。这是一个保守的估计，SQLite 已经被证明可以处理 10 倍以上的流量。
    数据分析。熟悉 SQL 的人员可以使用 sqlite3 命令行工具或者第三方程序来分析大型数据集，包括网站日志分析、体育统计分析、程序编译指标以及实验结果分析。
    企业数据的缓存。很多应用程序使用 SQLite 作为企业 RDBMS 数据的本地缓存，加快了前端响应速度，同时减少了网络传输和核心数据库服务器上的负载。另外，而且很多情况下，客户端应用程序可以在网络中断期间继续运行。
    服务器端数据库。客户端发送请求到服务端，服务端将请求转换为 SQL 查询，获取结果并进行处理之后返回客户端。服务端支持并发访问，然后将数据库请求串行化。另外，通过使用多个数据库文件可以进一步提高并发支持。例如，每个用户对应一个数据库，每个数据库只有一个连接。
    数据传输的文件格式。一个 SQLite 数据库就是一个压缩的文件，具有跨平台支持的格式，因此也可以作为应用传输数据的容器。
    文件归档和数据存储。SQLite 的归档功能可以作为 ZIP 归档或者 TAR 的替代。
    替代临时磁盘文件。很多程序需要使用 fopen()、fread() 和 fwrite() 接口创建和管理专用格式的数据文件，SQLite 非常适合替代这种文件，而且比文件系统读取更快。
    内部数据库或者临时数据库。如果程序需要对大量数据进行各种筛选和排序，将数据加载到 SQLite 内存数据库中进程操作通常比手动编写代码进行处理会更简单快速。这种方式还提供了很好的灵活性，因为可以增加新的字段和索引，而不需要重新对查询进行编码。
    演示或者测试数据库。客户端应用通常使用一个通用的数据库接口（例如 JDBC），可以连接到各种数据库。在测试或者演示时可以连接到 SQLite 数据文件，而不需要安装单独的数据库服务器。
    教学和培训。SQLite 安装个使用非常简单，只需要将 sqlite3 或者 sqlite3.exe 复制到指定机器然后允许即可。因此，SQLite 非常适合学习 SQL 语句和分享。
    SQL 扩展特性的试验。SQLite 具有简单的模块化设计，因此非常适合用于新的数据库语言特性或者理念的试验。

灵活的数据类型

SQLite 提供了非常灵活的数据类型。其中的关键在于 SQLite 对数据库中数据的类型要求非常宽松。例如，可以将一个字段的类型定义为 INTEGER 之后，插入数据时存储一个字符串；此时 SQLite 会尝试将字符串转换为整数，因此 ‘1234’ 会被转换为 1234 并存入表中。但是，如果插入一个非数字的字符串（例如 ‘wxyz’）SQLite 也不会报错，而是直接存储一个字符串值。这一点显然和其他数据库不同。

与此类似，SQLite 允许将一个 2000 字符长度的字符串插入一个 VARCHAR(50) 类型的字段中；其他数据库则会报错或者截断输入的字符串。

如果在初始开发过程中使用 SQLite，然后部署上线时替换为其他数据库（PostgreSQL 或者 SQL Server），可能会因此导致问题。因为 SQLite 对于类型的要求更加宽松，其他数据库对于类型的要求更加严格。

这种灵活的数据类型是 SQLite 的一个特性，而不是缺陷。但无论如何，这个特性的确会给熟悉其他数据库的开发人员带来一些困惑和痛苦。也许更好的方式是 SQLite 提供一个 ANY 数据类型，开发人员就可以在需要时使用灵活类型，而不是默认使用这种类型。但是，现在改变这一行为会损坏已经使用该功能的数百万应用程序和数十亿个数据库文件。
没有 BOOLEAN 数据类型

和其他大多数 SQL 实现不同，SQLite 没有提供真正的 BOOLEAN 数据类型。TRUE 和 FALSE 通常使用 1 和 0 进行表示。这似乎没有导致什么问题，但还是应该有所了解。

从 SQLite 3.23.0（2018-04-02）开始 TRUE 和 FALSE 关键字分别定义为 1 和 0 的别名。这样可以更好地兼容其他 SQL 实现。但是为了 向下兼容，如果某个字段名为 TRUE 或者 FALSE，那么这些关键字就表示这些字段，而不是布尔常量。
没有 DATETIME 数据类型

SQLite 没有提供 DATETIME 数据类型。日期和时间可以使用以下方式进行存储：

    ISO-8601 格式的 TEXT 字符串，例如 ‘2018-04-02 12:13:46’。
    使用 INTEGER 表示自从 1970 年 1 月 1 日以来的秒数（也就是 unix 时间戳），不考虑闰秒。
    使用 REAL 值表示的 Julian 日期。

SQLite 内置的日期时间函数可以操作和转换以上格式的日期/时间数据。
字段类型的可选性

由于 SQLite 支持灵活的数据类型，创建表时甚至可以不用指定字段的数据类型。例如：

CREATE TABLE t1(a,b,c,d);



其中，表 t1 包含 4个字段：a、b、c、d，它们都没有特定的数据类型。因此，我们可以在这些字段中存储任何数据。
默认不启用外键约束

SQLite 很早就支持了外键约束的解析，但是实际上直到 SQLite 3.6.19（2009-10-14）才真正实现了外键约束。这个时候已经有数百万个数据库包含了外键约束，但是实际上的数据并不满足条件；为了避免破坏那些遗留的数据库，SQLite 默认关闭了外键约束的强制执行。

应用程序可以在运行时通过PRAGMA foreign_keys指令启用外键约束，或者在编译时通过 -DSQLITE_DEFAULT_FOREIGN_KEYS=1 选项启用。
主键可能包含空值

通常来说，SQLite 表中的 PRIMARY KEY 相当于一个 UNIQUE 约束（除了 INTEGER PRIMARY KEY 表和 WITHOUT ROWID 表之外）。由于历史问题，主键字段中的值允许为 NULL。显然这是一个缺陷，但是等到发现问题的时候存在大量的数据库依赖于该缺陷，所以最终还是继续支持这种错误行为。

不过，INTEGER PRIMARY KEY 字段的值只能是一个非空的整数， WITHOUT ROWID 表的 PRIMARY KEY 字段也只能存储非空的数据。
汇总查询中可以使用非 GROUP BY 字段

对于绝大多数的 SQL 实现，汇总查询的结果中只能包含聚合函数或者 GROUP BY 分组字段。在汇总查询中引用其他的字段没有逻辑意义，因为输出结果可能是由两行或者多行数据汇总而成。

但是，SQLite 并不强制这种限制，输出字段可以是任意表达式，包括非分组字段。这一特性包含两种使用场景：

    对于 SQLite 而言，如果汇总查询中只包含一个 min() 或者 max() 函数，其他字段的值也来自 min() 或者 max() 取值所在的数据行；如果 min() 或者 max() 对应多行数据，随机返回一行中的值。例如，以下查询返回了薪水最高的员工：

    SELECT max(salary), first_name, last_name FROM employee;


    以上查询中，返回的 first_name 和 last_name 是薪水最高的员工姓名。

    如果查询中没有聚合函数，GROUP BY 可以作为 DISTINCT ON 子句的替代实现。也就是说，基于 GROUP BY 中的字段进行分组，每组返回一行记录，其他字段则随机返回一个数据值。

    例如：

    SELECT department_id, first_name, last_name
    FROM employees
    GROUP BY department_id;


    以上查询为每个部门（department_id）返回一条记录，同时返回该部门内的一个随机员工。SQLite 支持 DISTINCT，但是不支持 DISTINCT ON，可以使用这种形式的 GROUP BY 替代。

默认不支持完整的 Unicode 字符大小写转换

SQLite 无法区分所有 Unicode 字符的大小写，SQL 函数（ 例如 upper() 和 lower()）只支持 ASCII 字符。这个问题的存在有两个原因：

    虽然现在已经稳定了，但是在最初设计 SQLite 时， Unicode 大小写对应仍然存在变化。这就意味着每个新的 Unicode 版本的行为可能发生改变，从而破坏使用中的应用程序和数据索引。
    完全正确的 Unicode 大小写转换表比整个 SQLite 库文件更大。

如果编译时使用了 -DSQLITE_ENABLE_ICU 选项，SQLite 可以支持完整的 Unicode 大小写转换，此时 SQLite 库文件会链接到 ICU 库文件。
双引号字符串可以作为常量

按照 SQL 标准，双引号表示标识符，单引号表示字符串常量。例如：

    “this is a legal SQL column name”
    ‘this is an SQL string literal’

SQLite 支持以上两种形式。但是最初为了兼容 MySQL 3.x，如果双引号字符串没有匹配到有效的标识符，SQLite 会将其解释为字符串常量。

这种行为意味着双引号中拼写错误的标识符将被解释为字符串常量，而不是产生一个错误。它还会导致那些 SQL 初学者形成使用双引号字符串常量的不良习惯，而不是使用正确的单引号。仍然是由于存在大量应用使用了双引号字符串，因此 SQLite 选择了继续支持这个功能，避免破坏遗留系统。

从 SQLite 3.27.0（2019-02-07）开始，使用双引号字符串会在错误日志中记录一个告警信息。

从 SQLite 3.29.0（2019-07-10）开始，双引号字符串常量功能可以在运行时禁用，只需要为 sqlite3_db_config() 方法设置 SQLITE_DBCONFIG_DQS_DDL 和 SQLITE_DBCONFIG_DQS_DML 参数。参数的默认值也可以在编译时使用 -DSQLITE_DQS=N 选项。
关键字通常可以用作标识符

SQL 语言定义了许多关键字，大多数数据库都不允许使用关键字作为标识符（表名或者字段名），除非使用了双引号。SQLite 对此更加灵活，很多关键字可以直接作为标识符使用，只要这些关键字不会引起歧义并且上下文需要一个标识符。

例如，以下语句在 SQLite 中有效：

CREATE TABLE union(true INT, with BOOLEAN);



其他数据库中不允许以上语句，因为 union、true 和 with 都是关键字。

这个功能可以提供向下兼容，因为新增关键字可能是遗留系统中的标识符。尽管如此，这也可能导致一些困惑。例如：

CREATE TRIGGER AFTER INSERT ON tableX BEGIN
  INSERT INTO tableY(b) VALUES(new.a);
END;



触发器的名称是“AFTER”，它是一个 BEFORE 触发器。语句中的 AFTER 只能解释为标识符，而不是关键字。
输入不可靠 SQL 语句时不会产生错误或警告

最初的 SQLite 实现遵循伯斯塔尔法则，接受任意的输入内容。这在最初被认为是很好的设计，一个系统应该接受不可靠的输入并尽量处理。但是随后，人们认识到有时候最好严格控制输入的内容，从而在输入时更容易发现错误。
AUTOINCREMENT 和 MySQL 实现不同

SQLite 中的 AUTOINCREMENT 属性和 MySQL 中的 AUTO_INCREMENT 实现不同。实际上 SQLite 并不推荐使用 autoincrement，因为 INTERGER 主键默认就是自动增长，具体可以参考官方文档。
字符串中允许存在 NUL 字符

NUL 字符（ASCII 编码 0x00 以及 Unicode 编码 \u0000）允许出现在 SQLite 字符串的中间，这可能会导致异常行为。 具体内容可以参考官方文档。