SQLite显示类型与存储类介绍
2023-07-287.6k 阅读
SQLite 显示类型与存储类概述
SQLite 是一款轻量级的嵌入式数据库，广泛应用于各种设备和应用程序中。在 SQLite 中，数据类型的概念与其他传统数据库有所不同，其中显示类型（Affinity）和存储类（Storage Class）是理解 SQLite 数据处理机制的关键部分。
显示类型（Affinity）
SQLite 的显示类型（Affinity）并非严格意义上的传统数据类型，而是一种根据值的文本表示形式来决定如何存储数据的倾向。SQLite 中的每一列都具有一个显示类型，这决定了该列存储数据时优先采用的存储类。
SQLite 支持以下五种显示类型：
- TEXT：具有 TEXT 显示类型的列，会优先尝试将数据存储为文本。如果插入的数据是数值，它也会尝试转换为文本进行存储。例如，将整数
123插入到 TEXT 显示类型的列中，它会以文本形式"123"存储。 - NUMERIC：NUMERIC 显示类型的列会根据插入值的形式，自动选择最合适的存储类。如果值看起来像整数，它会存储为整数；如果像浮点数，则存储为浮点数；如果值无法识别为数字，则存储为文本。例如，插入
123会存储为整数，而插入123.45会存储为浮点数，插入"abc"则会存储为文本。 - INTEGER：INTEGER 显示类型的列主要用于存储整数值。如果插入的值是整数，它会以整数形式存储。若插入的是浮点数，会尝试将其转换为整数（截断小数部分）后存储。对于非数字值，会尝试将其转换为整数，如果转换失败则报错。比如插入
123.45会存储为123，插入"abc"则会报错。 - REAL：REAL 显示类型的列用于存储浮点数值。任何插入的值都会被转换为浮点数进行存储。如果插入的是整数，也会转换为浮点数存储。例如，插入
123会存储为123.0。 - NONE：NONE 显示类型的列不具备数据类型的亲和性。数据会以其原始的存储类进行存储，不会进行类型转换。例如，插入一个文本值
"123"，它不会尝试转换为其他类型，而是直接以文本形式存储。
存储类（Storage Class）
SQLite 中的存储类决定了数据在数据库文件中的实际存储方式。与显示类型不同，存储类是数据本身所具有的属性，而不是列的属性。SQLite 支持以下五种存储类：
- NULL：表示空值，即没有值。例如，在创建表时，如果某一列没有插入任何值，且没有设置默认值，那么该列的值就是 NULL。
- INTEGER：用于存储有符号整数，根据数值的大小，SQLite 会选择合适的字节数来存储整数。可以存储的整数范围从 -9223372036854775808 到 9223372036854775807。例如：
CREATE TABLE example (id INTEGER);
INSERT INTO example (id) VALUES (123);
- REAL：用于存储 8 字节的 IEEE 754 浮点数值。适用于需要存储小数的数据。例如：
CREATE TABLE example (price REAL);
INSERT INTO example (price) VALUES (123.45);
- TEXT：用于存储文本字符串，以 UTF - 8、UTF - 16BE 或 UTF - 16LE 编码存储，具体取决于编译时的选项。例如：
CREATE TABLE example (name TEXT);
INSERT INTO example (name) VALUES ('John');
- BLOB：用于存储二进制大对象，数据以其原始形式存储，不进行任何转换。例如，存储图片、音频等二进制数据：
CREATE TABLE example (image BLOB);
-- 假设通过编程方式将图片数据以二进制形式插入
显示类型与存储类的关系
显示类型和存储类密切相关，但又有所不同。显示类型是列的属性，它指导 SQLite 在存储数据时如何选择存储类。而存储类是数据实际存储的方式。
当向 SQLite 表中插入数据时，SQLite 首先根据列的显示类型来决定如何处理数据。如果插入的值与列的显示类型兼容，那么它会以相应的存储类存储。例如，向具有 INTEGER 显示类型的列插入一个整数 123，它会以 INTEGER 存储类存储。但如果插入的值与显示类型不完全匹配，SQLite 会尝试进行类型转换。
例如，向具有 TEXT 显示类型的列插入一个整数 123，由于 TEXT 显示类型倾向于将数据存储为文本，所以 123 会被转换为文本 "123"，并以 TEXT 存储类存储。
然而，并非所有的转换都是可能的。比如向具有 INTEGER 显示类型的列插入一个文本 "abc"，由于无法将 "abc" 转换为整数，SQLite 会报错。
显示类型的应用场景
TEXT 显示类型的应用场景
- 存储字符串数据：当存储姓名、地址、描述等文本信息时，TEXT 显示类型是理想的选择。例如，存储用户的姓名：
CREATE TABLE users (name TEXT);
INSERT INTO users (name) VALUES ('Alice');
- 存储不需要进行数值计算的数字：如电话号码、邮政编码等，虽然它们是数字形式，但通常不需要进行数值运算，使用 TEXT 显示类型可以避免不必要的类型转换和潜在的错误。例如：
CREATE TABLE addresses (zip_code TEXT);
INSERT INTO addresses (zip_code) VALUES ('100001');
NUMERIC 显示类型的应用场景
- 存储数值数据且类型不固定：当数据可能是整数、浮点数或文本形式的数字，并且希望 SQLite 根据值的形式自动选择合适的存储类时，NUMERIC 显示类型非常有用。例如，存储商品价格，价格可能是整数，也可能是带有小数的浮点数：
CREATE TABLE products (price NUMERIC);
INSERT INTO products (price) VALUES (100);
INSERT INTO products (price) VALUES (100.50);
- 处理可能包含数字的文本数据：在某些情况下，数据可能包含数字和文本混合的情况，且需要对数字部分进行处理。例如，存储产品规格，可能包含数字和单位：
CREATE TABLE product_specs (spec NUMERIC);
INSERT INTO product_specs (spec) VALUES ('100g');
在这种情况下，虽然 '100g' 整体作为文本存储，但如果需要对数字部分 100 进行处理，SQLite 会根据 NUMERIC 显示类型的规则，在合适的时候尝试提取数字进行计算。
INTEGER 显示类型的应用场景
- 存储整数值且明确不需要小数部分：如用户 ID、商品数量、年龄等。使用 INTEGER 显示类型可以确保数据以整数形式存储，提高存储效率和查询性能。例如：
CREATE TABLE users (user_id INTEGER, age INTEGER);
INSERT INTO users (user_id, age) VALUES (1, 25);
- 用于索引和主键：由于整数在存储和比较时效率较高，在创建索引或定义主键时，使用 INTEGER 显示类型的列可以提高数据库的性能。例如：
CREATE TABLE orders (order_id INTEGER PRIMARY KEY);
REAL 显示类型的应用场景
- 存储精确到小数的数值：如科学计算中的数值、金融领域的利率等。这些数据需要精确表示小数部分，使用 REAL 显示类型可以满足需求。例如：
CREATE TABLE financial_data (interest_rate REAL);
INSERT INTO financial_data (interest_rate) VALUES (0.05);
- 进行浮点数运算：当需要进行浮点数运算，如计算平均值、总和等，且结果可能包含小数时，使用 REAL 显示类型的列可以确保运算结果的准确性。例如：
CREATE TABLE scores (score REAL);
INSERT INTO scores (score) VALUES (85.5);
INSERT INTO scores (score) VALUES (90.0);
SELECT AVG(score) FROM scores;
NONE 显示类型的应用场景
- 存储二进制数据且不希望进行类型转换：当存储 BLOB 数据，如图片、音频、视频等，NONE 显示类型可以确保数据以原始二进制形式存储，不进行任何类型转换。例如：
CREATE TABLE images (image_data BLOB);
-- 通过编程方式将图片数据以二进制形式插入，使用 NONE 显示类型确保数据不被修改
- 存储特殊格式的数据：某些特殊格式的数据，如自定义编码的数据，不希望 SQLite 对其进行类型转换或解析，NONE 显示类型可以满足需求。例如，存储特定加密算法生成的加密数据：
CREATE TABLE encrypted_data (data BLOB);
-- 将加密后的数据以二进制形式插入，NONE 显示类型保证数据完整性
存储类的特性与影响
NULL 存储类的特性
- 表示缺失值：NULL 存储类用于表示数据的缺失或未知。在数据库中，很多情况下某些列可能没有值，例如在用户注册时，某些可选字段可能未填写。使用 NULL 可以明确表示这些缺失值。例如：
CREATE TABLE users (email TEXT, phone_number TEXT);
INSERT INTO users (email) VALUES ('alice@example.com');
-- phone_number 字段为 NULL
- 参与运算的规则：当 NULL 值参与算术运算、比较运算等时，结果通常也是 NULL。例如：
SELECT 1 + NULL; -- 结果为 NULL
SELECT NULL = NULL; -- 结果为 NULL，在 SQLite 中，两个 NULL 值不被认为是相等的
- 对查询结果的影响：在查询中，如果某列的值为 NULL，需要特别注意。例如，在使用
WHERE子句过滤数据时，使用IS NULL或IS NOT NULL来判断 NULL 值。
SELECT * FROM users WHERE phone_number IS NULL;
INTEGER 存储类的特性
- 存储效率：INTEGER 存储类根据数值的大小，选择合适的字节数来存储整数，从 1 字节到 8 字节不等。对于较小的整数，使用较少的字节数存储，提高了存储效率。例如，存储一个较小的用户 ID，可能只需要 1 或 2 字节。
- 范围限制：SQLite 的 INTEGER 存储类可以存储的整数范围从 -9223372036854775808 到 9223372036854775807。如果插入的值超出这个范围，会导致错误。例如：
CREATE TABLE large_numbers (number INTEGER);
INSERT INTO large_numbers (number) VALUES (9223372036854775808); -- 报错，超出范围
- 运算性能：由于 INTEGER 存储类的数值存储格式简单，在进行整数运算时，性能较高。例如，在计算商品数量总和、用户 ID 排序等操作中，使用 INTEGER 存储类的列可以提高运算速度。
REAL 存储类的特性
- 浮点数精度：REAL 存储类使用 8 字节的 IEEE 754 浮点格式存储数值，这意味着在表示小数时可能存在精度问题。例如，某些无限循环小数在存储时会进行近似处理。
SELECT 1.0 / 3.0; -- 结果可能是 0.33333333333333331，存在精度误差
- 适用场景：适用于对精度要求不是特别高，但需要存储小数的数据场景，如一般的统计数据、非精确的测量数据等。在金融领域，由于对精度要求极高，可能不太适合使用 REAL 存储类，而更倾向于使用专门的高精度数值类型或通过特殊处理来保证精度。
- 存储和运算开销：相对于 INTEGER 存储类，REAL 存储类需要更多的存储空间来存储 8 字节的浮点数。在进行运算时，由于浮点数运算的复杂性，运算开销也相对较大。
TEXT 存储类的特性
- 编码方式：TEXT 存储类可以以 UTF - 8、UTF - 16BE 或 UTF - 16LE 编码存储文本，具体取决于编译时的选项。UTF - 8 是一种广泛使用的编码方式，支持全球各种字符集，适用于存储多种语言的文本数据。
- 存储长度：SQLite 对 TEXT 存储类的文本长度没有严格的固定限制，但受限于可用的内存和磁盘空间。在实际应用中，通常需要根据具体需求和系统资源来考虑存储的文本长度。
- 字符串操作：SQLite 提供了丰富的字符串操作函数，用于对 TEXT 存储类的数据进行处理，如拼接、截取、查找等。例如：
SELECT 'Hello,'|| 'World'; -- 拼接字符串，结果为 'Hello, World'
SELECT SUBSTR('Hello World', 1, 5); -- 截取字符串，结果为 'Hello'
BLOB 存储类的特性
- 数据完整性：BLOB 存储类以原始二进制形式存储数据，不进行任何转换或解析，确保数据的完整性。这对于存储图片、音频、视频等二进制数据非常重要，因为任何转换都可能导致数据损坏。
- 存储大小：与 TEXT 存储类类似，BLOB 存储类的存储大小受限于可用的内存和磁盘空间。对于大型的二进制文件，可能需要考虑分块存储或使用专门的文件系统来管理。
- 应用场景：除了常见的多媒体数据存储，BLOB 存储类还可用于存储加密数据、序列化的对象等。例如，在加密通信中，将加密后的消息以 BLOB 形式存储在数据库中。
显示类型与存储类的示例代码
创建具有不同显示类型的表
- 创建 TEXT 显示类型的表：
CREATE TABLE text_table (
id INTEGER PRIMARY KEY,
text_column TEXT
);
INSERT INTO text_table (text_column) VALUES ('This is a text');
- 创建 NUMERIC 显示类型的表：
CREATE TABLE numeric_table (
id INTEGER PRIMARY KEY,
numeric_column NUMERIC
);
INSERT INTO numeric_table (numeric_column) VALUES (123);
INSERT INTO numeric_table (numeric_column) VALUES (123.45);
INSERT INTO numeric_table (numeric_column) VALUES ('456');
- 创建 INTEGER 显示类型的表：
CREATE TABLE integer_table (
id INTEGER PRIMARY KEY,
integer_column INTEGER
);
INSERT INTO integer_table (integer_column) VALUES (10);
INSERT INTO integer_table (integer_column) VALUES (1234567890);
- 创建 REAL 显示类型的表：
CREATE TABLE real_table (
id INTEGER PRIMARY KEY,
real_column REAL
);
INSERT INTO real_table (real_column) VALUES (10.5);
INSERT INTO real_table (real_column) VALUES (3.14159);
- 创建 NONE 显示类型的表（实际应用中多结合 BLOB 存储）：
CREATE TABLE none_table (
id INTEGER PRIMARY KEY,
none_column BLOB
);
-- 假设通过编程方式将二进制数据插入 none_column
显示类型与存储类的查询示例
- 查询 TEXT 显示类型表的数据：
SELECT * FROM text_table;
- 查询 NUMERIC 显示类型表的数据，并观察存储类：
SELECT typeof(numeric_column) FROM numeric_table;
-- 对于插入的整数 123，typeof 函数返回 'integer'
-- 对于插入的浮点数 123.45，typeof 函数返回'real'
-- 对于插入的文本 '456'，typeof 函数返回 'text'
- 查询 INTEGER 显示类型表的数据：
SELECT * FROM integer_table;
- 查询 REAL 显示类型表的数据：
SELECT * FROM real_table;
- 查询 NONE 显示类型表的数据（假设已插入二进制数据）：
SELECT * FROM none_table;
显示类型与存储类在运算中的示例
- INTEGER 显示类型的运算：
CREATE TABLE integer_operations (
num1 INTEGER,
num2 INTEGER
);
INSERT INTO integer_operations (num1, num2) VALUES (10, 5);
SELECT num1 + num2 FROM integer_operations; -- 结果为 15
- REAL 显示类型的运算：
CREATE TABLE real_operations (
num1 REAL,
num2 REAL
);
INSERT INTO real_operations (num1, num2) VALUES (10.5, 5.25);
SELECT num1 + num2 FROM real_operations; -- 结果为 15.75
- TEXT 显示类型在运算中的情况（尝试与数字运算）：
CREATE TABLE text_operations (
text_num TEXT
);
INSERT INTO text_operations (text_num) VALUES ('10');
SELECT 'text_num' + 5 FROM text_operations; -- 报错，无法将文本与数字直接运算
显示类型与存储类的注意事项
显示类型的注意事项
- 类型转换的不确定性：由于显示类型会尝试进行类型转换，在某些情况下可能会导致意外的结果。例如，向具有 NUMERIC 显示类型的列插入文本
"123abc"，虽然123部分可能被识别为数字，但整体转换可能不符合预期，最终可能以文本形式存储。在设计数据库表结构时，应尽量明确数据类型，避免依赖不确定的类型转换。 - 与其他数据库的兼容性：SQLite 的显示类型概念与其他传统数据库的数据类型概念有所不同。在将 SQLite 数据库与其他数据库进行交互或迁移时，需要特别注意数据类型的转换。例如，将 SQLite 中具有 TEXT 显示类型的列迁移到 MySQL 中，可能需要根据实际需求将其转换为合适的 VARCHAR 或 TEXT 类型。
- 对索引的影响：不同的显示类型在创建索引时可能会有不同的性能表现。例如，INTEGER 显示类型的列创建索引时，由于其存储格式简单，索引查找速度通常较快。而 TEXT 显示类型的列创建索引时，由于文本比较的复杂性，索引性能可能相对较低。在设计索引时，应考虑列的显示类型对性能的影响。
存储类的注意事项
- NULL 值的处理：在处理 NULL 值时，需要特别小心。如前所述，NULL 值参与运算的结果通常也是 NULL，在查询和数据处理中，要正确使用
IS NULL和IS NOT NULL来判断 NULL 值，避免因 NULL 值处理不当导致查询结果不准确。 - 存储类转换的性能开销：虽然 SQLite 会根据显示类型尝试进行存储类的转换，但这种转换可能会带来一定的性能开销。例如，将一个大的 TEXT 类型的数字转换为 INTEGER 类型，需要进行解析和转换操作，这可能会影响插入和查询的性能。在设计数据库时，应尽量避免不必要的存储类转换。
- 数据一致性与完整性：确保存储类的正确使用对于维护数据一致性和完整性非常重要。例如，在存储日期时间数据时，如果使用 TEXT 存储类，需要确保数据格式的一致性，以便进行有效的查询和处理。如果使用不合适的存储类，可能会导致数据丢失或错误。
显示类型与存储类的优化策略
显示类型的优化策略
- 合理选择显示类型：根据数据的实际用途和特点，选择最合适的显示类型。如果数据是固定的整数，应选择 INTEGER 显示类型，以提高存储效率和运算性能；如果数据是文本字符串，应选择 TEXT 显示类型。避免使用通用的 NUMERIC 显示类型，除非确实需要自动类型转换的功能。
- 减少类型转换：尽量避免在插入数据时发生不必要的类型转换。例如，如果知道某列应该存储整数，应确保插入的数据是整数类型，而不是先插入文本再让 SQLite 进行转换。可以通过在应用程序层进行数据验证和类型转换，确保插入到数据库中的数据类型与列的显示类型匹配。
- 考虑索引和查询性能：在创建索引时，优先选择 INTEGER 或 REAL 显示类型的列，因为这些类型在索引查找时性能较高。对于 TEXT 显示类型的列，如果需要频繁进行查询，可以考虑创建全文索引，以提高查询性能。
存储类的优化策略
- 优化 NULL 值的使用：尽量减少 NULL 值的出现，在设计表结构时，可以为列设置合理的默认值，避免不必要的 NULL 值。如果无法避免 NULL 值，在查询中应正确处理，以减少对性能的影响。例如，在使用
WHERE子句时，尽量将IS NULL或IS NOT NULL的条件放在最后，以减少全表扫描的可能性。 - 选择合适的存储类：根据数据的实际范围和精度要求，选择合适的存储类。对于整数，如果数值范围较小，可以使用 SMALLINT 或 TINYINT 等较小的整数类型（在 SQLite 中通过 INTEGER 显示类型配合合适的存储类实现），以节省存储空间。对于需要高精度的数值，应避免使用 REAL 存储类，可考虑使用专门的高精度数值库或通过特殊的存储方式来保证精度。
- 管理 BLOB 和 TEXT 数据：对于 BLOB 和 TEXT 数据，由于其存储大小可能较大，应合理管理。可以考虑将大的 BLOB 数据（如图片、视频）存储在文件系统中，而在数据库中只存储文件路径。对于 TEXT 数据，如果长度可能非常大，可以考虑使用分块存储或数据库特定的大文本处理机制，以提高存储和查询性能。
通过深入理解 SQLite 的显示类型与存储类，并遵循上述优化策略，可以更好地设计和管理 SQLite 数据库，提高数据库的性能和可靠性。在实际应用中，还需要根据具体的业务需求和数据特点，灵活运用这些知识，以达到最佳的效果。