SQLite执行中的亲缘性与存储关系
2023-09-306.3k 阅读
SQLite 的数据类型与亲缘性基础
SQLite 是一款轻量级的嵌入式数据库，与许多其他数据库系统不同，它在数据类型处理上有独特的机制，其中亲缘性（Affinity）是理解其数据存储的关键概念。
在大多数传统数据库中，每个表的列都有严格定义的数据类型，插入的数据必须精确匹配该类型。然而，SQLite 采用了一种更灵活的方式。SQLite 中的数据值本身携带类型信息，而列具有“亲缘性”，它表示列对特定数据类型的偏好。
SQLite 定义了五种基本的数据类型：
- NULL：表示一个缺失或未定义的值。
- INTEGER：带符号的整数，根据值的大小可以存储为 1、2、3、4、6 或 8 字节。
- REAL：IEEE 754 格式的浮点数值，通常存储为 8 字节。
- TEXT：文本字符串，使用数据库编码（UTF - 8、UTF - 16BE 或 UTF - 16LE）存储。
- BLOB：二进制大对象，数据按输入时的原样存储。
SQLite 的亲缘性分为以下几类：
- TEXT 亲缘性：具有 TEXT 亲缘性的列会优先尝试将输入数据转换为 TEXT 类型。如果数据是数值类型，它会转换为文本格式存储。例如，列定义为
CREATE TABLE test (col TEXT);，当插入INSERT INTO test (col) VALUES (123);，123 会被转换为文本“123”存储。 - NUMERIC 亲缘性：这种亲缘性的列会优先将数据存储为 INTEGER 或 REAL 类型，如果无法转换为这两种类型，才会考虑存储为 TEXT 或 BLOB。例如
CREATE TABLE num_test (col NUMERIC);，插入INSERT INTO num_test (col) VALUES ('123');，'123' 会被转换为整数 123 存储。但如果插入INSERT INTO num_test (col) VALUES ('abc');，则会以 TEXT 形式存储。 - INTEGER 亲缘性：具有 INTEGER 亲缘性的列几乎总是将数据存储为 INTEGER 类型，除非数据明显无法转换为整数，如字符串 'abc'，此时会存储为 TEXT 或 BLOB。例如
CREATE TABLE int_test (col INTEGER);，插入INSERT INTO int_test (col) VALUES (123.45);，123.45 会被截断为 123 存储为 INTEGER。 - REAL 亲缘性：此亲缘性的列优先将数据存储为 REAL 类型，对于可转换为浮点数的数据会进行转换存储。例如
CREATE TABLE real_test (col REAL);，插入INSERT INTO real_test (col) VALUES (123);，123 会被转换为 123.0 存储为 REAL。 - NONE 亲缘性：具有 NONE 亲缘性的列不会对插入的数据进行任何类型转换，数据按其原始输入类型存储。例如
CREATE TABLE none_test (col BLOB);，无论插入什么类型的数据，都按原样存储，即使是可转换为其他类型的数据。
亲缘性对数据存储的影响
- 存储效率：亲缘性机制对 SQLite 的存储效率有显著影响。例如，在具有 NUMERIC 亲缘性的列中，如果插入大量的整数值，它们会以 INTEGER 类型存储，相较于 TEXT 存储，占用空间更小。考虑以下示例：
import sqlite3
# 连接到数据库
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
# 创建具有 NUMERIC 亲缘性的表
cursor.execute('CREATE TABLE numeric_table (col NUMERIC)')
# 插入大量整数
for i in range(10000):
cursor.execute('INSERT INTO numeric_table (col) VALUES (?)', (i,))
conn.commit()
conn.close()
在这个示例中，由于 col 列具有 NUMERIC 亲缘性，插入的整数会以高效的 INTEGER 类型存储，减少了存储空间。如果将列定义为 TEXT 亲缘性：
import sqlite3
# 连接到数据库
conn = sqlite3.connect('test.db')
cursor = conn.cursor()
# 创建具有 TEXT 亲缘性的表
cursor.execute('CREATE TABLE text_table (col TEXT)')
# 插入大量整数
for i in range(10000):
cursor.execute('INSERT INTO text_table (col) VALUES (?)', (i,))
conn.commit()
conn.close()
这些整数会被转换为文本形式存储，每个数字字符都占用一定空间，相比 INTEGER 存储会占用更多空间。
- 查询性能：亲缘性也会影响查询性能。当查询具有合适亲缘性的列时，SQLite 可以更高效地处理数据。例如，在具有 INTEGER 亲缘性的列上进行数值比较查询，比在 TEXT 亲缘性的列上进行同样的数值比较查询要快。假设我们有一个存储用户年龄的表：
import sqlite3
# 连接到数据库
conn = sqlite3.connect('users.db')
cursor = conn.cursor()
# 创建具有 INTEGER 亲缘性的年龄表
cursor.execute('CREATE TABLE users_age (age INTEGER)')
# 插入一些数据
ages = [25, 30, 35, 40]
for age in ages:
cursor.execute('INSERT INTO users_age (age) VALUES (?)', (age,))
# 查询年龄大于 30 的用户
cursor.execute('SELECT age FROM users_age WHERE age > 30')
results = cursor.fetchall()
print(results)
conn.close()
在这个例子中，由于 age 列是 INTEGER 亲缘性，SQLite 可以直接对整数进行比较操作，查询执行速度较快。如果 age 列是 TEXT 亲缘性，每次比较时都需要先将文本转换为数值，这会增加查询的处理时间。
亲缘性与数据转换规则
- 从文本转换为数值：当向具有 NUMERIC、INTEGER 或 REAL 亲缘性的列插入 TEXT 类型数据时，SQLite 会尝试将文本转换为相应的数值类型。转换规则遵循常见的数值解析规则。例如，对于字符串 '123'，可以成功转换为 INTEGER 类型 123；对于字符串 '123.45'，在具有 REAL 亲缘性的列中会转换为 123.45，在具有 INTEGER 亲缘性的列中会截断为 123。如果文本无法解析为有效的数值，如 'abc'，则根据亲缘性决定存储方式，对于 INTEGER 和 REAL 亲缘性，可能存储为 TEXT 或 BLOB，对于 NUMERIC 亲缘性，如果无法转换为数值，则存储为 TEXT。
- 从数值转换为文本：当向具有 TEXT 亲缘性的列插入数值时，SQLite 会将数值转换为文本。转换方式是将数值按照标准的文本表示形式进行转换，例如整数 123 转换为字符串 '123'，浮点数 123.45 转换为 '123.45'。
- 其他转换：BLOB 类型的数据一般不会进行转换，除非插入到具有特定亲缘性且数据可以合理转换的列中。例如，将一个非常小的整数存储为 BLOB 后，插入到具有 INTEGER 亲缘性的列中，SQLite 可能会尝试提取整数并以 INTEGER 类型重新存储，但这种情况相对较少见。
亲缘性在复杂数据结构中的应用
- 表结构设计：在设计 SQLite 数据库表结构时，合理利用亲缘性可以优化存储和查询性能。例如，对于存储日期和时间的列，如果经常需要进行日期计算等数值操作，可以将其定义为 NUMERIC 亲缘性，存储 Unix 时间戳（整数形式）。这样既方便进行日期时间的计算，又能保证存储效率。
import sqlite3
import time
# 连接到数据库
conn = sqlite3.connect('datetime.db')
cursor = conn.cursor()
# 创建具有 NUMERIC 亲缘性的日期时间表
cursor.execute('CREATE TABLE events (event_time NUMERIC)')
# 获取当前时间的 Unix 时间戳并插入
current_time = int(time.time())
cursor.execute('INSERT INTO events (event_time) VALUES (?)', (current_time,))
# 查询事件时间
cursor.execute('SELECT event_time FROM events')
result = cursor.fetchone()
print(result)
conn.close()
- 嵌套数据结构：在处理嵌套数据结构，如 JSON 数据存储在 SQLite 中时，亲缘性也有影响。假设我们将 JSON 数据存储在 TEXT 类型的列中，当需要从中提取数值字段进行计算时，如果列具有 NUMERIC 亲缘性，在解析 JSON 提取数值后，可以更高效地进行数值操作。例如：
import sqlite3
import json
# 连接到数据库
conn = sqlite3.connect('json.db')
cursor = conn.cursor()
# 创建具有 TEXT 亲缘性的 JSON 数据表
cursor.execute('CREATE TABLE json_data (data TEXT)')
# 准备 JSON 数据
json_obj = {'value': 123}
json_str = json.dumps(json_obj)
cursor.execute('INSERT INTO json_data (data) VALUES (?)', (json_str,))
# 提取并计算数值
cursor.execute('SELECT json_extract(data, \'$.value\') FROM json_data')
result = cursor.fetchone()[0]
if result:
num_value = int(result)
new_value = num_value * 2
print(new_value)
conn.close()
如果列定义为 NUMERIC 亲缘性，在 json_extract 提取数值后，SQLite 会更自然地将其作为数值处理，而不需要额外的类型转换步骤。
亲缘性与 SQLite 引擎的内部机制
- 存储引擎层面：SQLite 的存储引擎在处理具有不同亲缘性的列时，采用不同的存储策略。对于 INTEGER 亲缘性的列，存储引擎会优先分配适合整数存储的空间，根据数值大小选择合适的字节数存储，如 1 字节存储较小的整数，8 字节存储较大的整数。对于 TEXT 亲缘性的列，存储引擎会按照数据库编码格式（如 UTF - 8）存储文本数据，并且会处理字符串的长度管理等问题。
- 查询执行层面：在查询执行过程中，亲缘性影响查询优化器的决策。当查询涉及到比较操作时，查询优化器会根据列的亲缘性来选择最优的执行计划。例如，在比较两个具有 INTEGER 亲缘性的列时，优化器会使用针对整数比较的高效算法，而对于 TEXT 亲缘性列的比较，会采用字符串比较算法。如果在查询中混合了不同亲缘性列的比较，优化器需要考虑数据类型转换的成本，以生成最优的执行计划。
实践中的亲缘性考量
- 数据一致性：在使用亲缘性时，要注意数据一致性问题。例如，在一个具有 NUMERIC 亲缘性的列中插入看似数值的文本，如 '123abc'，它可能会以 TEXT 形式存储，这可能导致后续查询结果不符合预期。为了保证数据一致性，在插入数据前，最好进行数据验证，确保插入的数据符合列的亲缘性预期。
- 兼容性与迁移：如果考虑将 SQLite 数据库与其他数据库系统进行交互或迁移，需要注意亲缘性差异。其他数据库系统可能没有亲缘性概念，而是采用严格的数据类型定义。在迁移数据时，可能需要根据目标数据库的类型系统对数据进行预处理，以确保数据准确迁移。
在实际应用开发中，充分理解 SQLite 的亲缘性与存储关系，能够帮助开发者设计出高效、可靠的数据库结构，提升应用的性能和稳定性。通过合理选择列的亲缘性，优化数据存储和查询操作，能够充分发挥 SQLite 轻量级数据库的优势，满足各种应用场景的需求。无论是小型移动应用还是嵌入式设备中的数据管理，亲缘性机制都是 SQLite 高效运行的关键因素之一。