一、引言
嘿，朋友们！在计算机的世界里，数据库就像是一个大仓库，帮我们存放各种各样的数据。而 SQLite 呢，它是一个小巧玲珑又功能强大的嵌入式数据库，很多小型项目或者移动应用都喜欢用它。不过呢，在使用 SQLite 处理数据的过程中，数据类型处理可是个容易掉坑的地方。要是不小心，存储和查询数据的时候就会出现各种奇怪的问题。接下来，咱们就一起深入了解下 SQLite 数据类型处理的那些陷阱，以及怎么去避免这些常见错误。
二、SQLite 数据类型基础
2.1 动态类型系统
SQLite 和其他一些数据库不太一样，它采用的是动态类型系统。啥意思呢？就是说在 SQLite 里，数据类型是和值关联的，而不是和列关联。简单来讲，你在创建表的时候可以指定列的数据类型，但实际往这个列里存数据的时候，它不一定非要符合那个类型。
比如说，我们创建一个简单的表：
-- 创建一个名为 person 的表，包含 id 和 name 两列
CREATE TABLE person (
id INTEGER,
name TEXT
);
上面的代码创建了一个 person 表，id 列的数据类型指定为 INTEGER，name 列的数据类型指定为 TEXT。按照常理，id 应该存整数，name 应该存文本。但在 SQLite 里，你可以这么干：
-- 插入一条数据，id 存的是字符串，name 存的也是字符串，没问题
INSERT INTO person (id, name) VALUES ('1', 'Alice');
-- 再插入一条数据，id 存整数，name 也存字符串，同样可以
INSERT INTO person (id, name) VALUES (2, 'Bob');
这就是动态类型系统的特点，它很灵活，但也容易让人不小心犯错。
2.2 主要数据类型
SQLite 有几种主要的数据类型，分别是 NULL、INTEGER、REAL、TEXT 和 BLOB。
NULL：表示空值，也就是没有值。举个例子：
-- 创建一个表，包含一个允许为空的列
CREATE TABLE test_null (
info TEXT
);
-- 插入一条数据，info 列值为 NULL
INSERT INTO test_null (info) VALUES (NULL);
INTEGER：用来存储整数，比如 1、2、3 这样的。
-- 创建一个表，包含一个存储整数的列
CREATE TABLE test_integer (
number INTEGER
);
-- 插入一个整数
INSERT INTO test_integer (number) VALUES (10);
REAL：存储浮点数，像 3.14、2.718 这些。
-- 创建一个表，包含一个存储浮点数的列
CREATE TABLE test_real (
value REAL
);
-- 插入一个浮点数
INSERT INTO test_real (value) VALUES (3.14);
TEXT：存储文本数据，比如 "Hello, World!"。
-- 创建一个表，包含一个存储文本的列
CREATE TABLE test_text (
message TEXT
);
-- 插入一段文本
INSERT INTO test_text (message) VALUES ('Hello, World!');
BLOB：存储二进制大对象，像图片、音频文件这些二进制数据就可以用它存。不过操作起来相对复杂一些，这里就简单提一下。
三、存储数据时的常见陷阱及避免方法
3.1 类型不匹配问题
前面咱们说了 SQLite 的动态类型系统很灵活，但这也会带来类型不匹配的麻烦。比如说，你原本想存整数，结果不小心存了字符串。这可能会导致后续查询或者计算出现问题。
看个例子：
-- 创建一个表，用于统计用户的年龄
CREATE TABLE user_age (
age INTEGER
);
-- 不小心把年龄存成了字符串
INSERT INTO user_age (age) VALUES ('twenty');
现在，咱们想计算所有用户的平均年龄：
-- 尝试计算平均年龄
SELECT AVG(age) FROM user_age;
由于存进去的是字符串，这个计算就会出问题，得到的结果可能不是我们想要的。
为了避免这种情况，我们在插入数据之前要做好数据类型的检查和转换。比如在 Python 里和 SQLite 交互的时候，可以这样：
import sqlite3
# 连接到 SQLite 数据库
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
# 假设我们有一个年龄变量
age = '20'
# 尝试将其转换为整数
try:
age = int(age)
cursor.execute("INSERT INTO user_age (age) VALUES (?)", (age,))
conn.commit()
except ValueError:
print("输入的不是有效的整数年龄")
# 关闭连接
conn.close()
3.2 日期和时间存储问题
在 SQLite 里，没有专门的日期和时间数据类型，通常是用 TEXT、REAL 或者 INTEGER 来存储日期和时间。这就需要我们自己做好规范和处理。
用 TEXT 存储日期和时间
-- 创建一个表，用 TEXT 存储日期
CREATE TABLE event (
event_time TEXT
);
-- 插入一个日期，格式为 'YYYY-MM-DD HH:MM:SS'
INSERT INTO event (event_time) VALUES ('2024-01-01 12:00:00');
用 TEXT 存储的好处是直观，方便人阅读，但是在做日期和时间的比较、计算的时候就不太方便了。
用 INTEGER 存储日期和时间
可以把日期和时间转换成 Unix 时间戳（从 1970 年 1 月 1 日开始到指定时间的秒数）来存储。
-- 创建一个表，用 INTEGER 存储日期
CREATE TABLE timestamp_event (
event_timestamp INTEGER
);
-- 插入一个 Unix 时间戳
INSERT INTO timestamp_event (event_timestamp) VALUES (1704014400);
用 INTEGER 存储在做日期和时间的计算时会比较方便，但是不太直观。
为了避免日期和时间存储的问题，我们要根据实际需求选择合适的存储方式，并且在整个项目里保持一致。
四、查询数据时的常见陷阱及避免方法
4.1 隐式类型转换问题
SQLite 在查询的时候可能会进行隐式类型转换，这有时候会导致结果和我们预期的不一样。
看个例子：
-- 创建一个表，包含一个整数列和一个文本列
CREATE TABLE mixed_types (
int_col INTEGER,
text_col TEXT
);
-- 插入数据
INSERT INTO mixed_types (int_col, text_col) VALUES (1, '1');
-- 尝试比较整数列和文本列
SELECT * FROM mixed_types WHERE int_col = text_col;
这里 SQLites 会进行隐式类型转换，让 int_col 和 text_col 可以比较。但有时候这种转换可能不是我们想要的，会让结果变得混乱。
为了避免隐式类型转换的问题，我们在查询的时候尽量保证数据类型一致。比如把 text_col 转换为整数再比较：
SELECT * FROM mixed_types WHERE int_col = CAST(text_col AS INTEGER);
4.2 空值处理问题
在查询的时候，空值也是个容易出错的地方。SQLite 里用 IS NULL 和 IS NOT NULL 来判断一个值是不是空值。
-- 创建一个表，包含一个允许为空的列
CREATE TABLE nullable_column (
data TEXT
);
-- 插入一条数据，data 列值为 NULL
INSERT INTO nullable_column (data) VALUES (NULL);
-- 查询 data 列为空的记录
SELECT * FROM nullable_column WHERE data IS NULL;
如果不小心用了 = 来判断空值，就会得不到正确的结果。
-- 这个查询不会返回任何结果，因为不能用 = 判断空值
SELECT * FROM nullable_column WHERE data = NULL;
五、SQLite 数据类型处理的应用场景
5.1 移动应用开发
在移动应用开发中，SQLite 经常被用来存储本地数据。比如一个待办事项应用，它会把用户的待办事项存储在本地的 SQLite 数据库里。每个待办事项可能包含标题、描述、截止日期等信息。标题和描述可以用 TEXT 类型存储，截止日期可以用前面说的日期和时间存储方式来处理。这样，即使用户在没有网络的情况下也能正常查看和操作自己的待办事项。
5.2 小型项目和桌面应用
对于一些小型的项目或者桌面应用，SQLite 也是个很好的选择。比如一个简单的图书管理系统，它可以用 SQLite 来存储图书的信息，像书名、作者、出版日期等。由于项目规模小，不需要复杂的数据库管理，SQLite 的小巧和易用就能发挥很大的优势。
六、SQLite 数据类型处理的技术优缺点
6.1 优点
- 灵活性高：动态类型系统让我们在存储数据的时候非常灵活，不需要严格遵循创建表时指定的类型，方便快速开发和调整。
- 小巧轻便：SQLite 体积小，不需要单独的服务器进程，很适合嵌入式系统和小型项目，减少了系统的开销。
6.2 缺点
- 类型安全性低：由于动态类型系统，容易出现类型不匹配的问题，需要开发者自己做好类型检查和处理，增加了开发的难度。
- 复杂数据类型支持有限：对于一些复杂的数据类型，像日期和时间，没有专门的支持，需要开发者自己处理，不够直观和方便。
七、注意事项
- 数据类型检查和转换：在插入数据之前，一定要做好数据类型的检查和转换，确保数据的正确性。
- 保持一致：在整个项目里，对于日期和时间、自定义数据类型等的存储方式要保持一致，方便后续的查询和维护。
- 测试：对存储和查询操作进行充分的测试，尤其是涉及到数据类型转换和空值处理的地方，及时发现和解决问题。
八、文章总结
通过上面的介绍，我们了解了 SQLite 数据类型处理的各种陷阱以及避免方法。SQLite 的动态类型系统既给我们带来了灵活性，也带来了一些挑战。在存储数据的时候，要注意类型不匹配和日期时间存储的问题；在查询数据的时候，要留意隐式类型转换和空值处理的陷阱。同时，我们也知道了 SQLite 在移动应用开发和小型项目等场景下的应用，以及它的优缺点和使用时的注意事项。只要我们掌握了这些知识，就能更好地使用 SQLite 处理数据，避免常见的错误，让我们的项目更加稳定和可靠。
评论