SQLite 数据库字段类型-详细说明，数据类型详细说明。

SQLite 数据类型

SQLite字段类型详细说明，包含存储类、亲和类型、布尔类型、日期时间类型的存储方式、取值范围及核心特性。

创建 SQLite3 表时可使用的各种数据类型名称，同时也介绍了相应的亲和类型。

一、核心存储类（Storage Classes）

所有数据均以以下5种存储类之一存储，是SQLite数据的底层存储形式：

二、亲和类型（Affinity）

SQLite支持列的亲和类型概念。任何列仍然可以存储任何类型的数据，当数据插入时，该字段的数据将会优先采用亲缘类型作为该值的存储方式。SQLite目前的版本支持以下五种亲缘类型 。

字段类型名称定义“亲和性”（优先存储的类型），而非强制类型。以下是文档明确的类型名称与亲和类型对应关系及特性：

三、Boolean类型

SQLite无专门的Boolean存储类，通过INTEGER存储：

四、Date与Time类型

无专门存储类，可通过TEXT、REAL、INTEGER存储，文档明确格式如下：

关键说明

1. 存储类是数据的实际存储形式，亲和类型仅影响插入时的优先转换规则，不限制实际存储的类型（如INTEGER亲和性字段可存储TEXT）。
2. 若需严格限制取值范围（如年龄0-120），需通过CHECK约束实现（如age INTEGER CHECK (age BETWEEN 0 AND 120)）。
3. 日期时间可通过内置函数转换（如datetime(ts, 'unixepoch')将Unix时间戳转为TEXT格式）。

TEXT类型：文本字符串

• 值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。
• 数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。

NUMERIC类型： 数字

当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。

INTEGER类型：带符号的整数

• 值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。
• 对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。

REAL类型：浮点值

• 值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。
• 其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。

NONE类型

• 不做任何的转换，直接以该数据所属的数据类型进行存储。

BLOB

• 值是一个 blob 数据，完全根据它的输入存储。

NONE 亲和类型、BLOB 字段名称 和 未指定类型字段定义方式 的关联与区别

1. NONE（亲和类型）

定义

• NONE 是 SQLite 的五种亲和类型之一，表示字段在插入数据时 不进行任何类型转换，直接保留原始数据的存储类（Storage Class）。
• 核心特性：
  • 插入的值以原始类型存储（如 TEXT 存为 TEXT，BLOB 存为 BLOB）。
  • 唯一“不干预数据类型”的亲和类型，区别于 TEXT（强制转文本）、NUMERIC（优先转数值）等。

对应字段声明方式

• 两种情况触发 NONE 亲和类型：
  1. 字段明确声明为 BLOB（字段名称为 BLOB）。
  2. 字段未指定类型（如 CREATE TABLE t (col); 中的 col）。

适用场景

• 严格保留原始数据格式：
  例如存储二进制文件（图片、音频）、加密数据、原始字节流等，避免 SQLite 隐式转换导致数据丢失或损坏。

2. BLOB（字段名称）

定义

• BLOB 是 SQLite 中专门用于存储二进制数据的字段名称，其亲和类型为 NONE。
• 存储方式：
  • 原样存储输入的二进制数据，带长度前缀（标识二进制数据的字节数）。
  • 不进行编码转换（如 UTF-8 编码），直接保留原始字节流。

取值范围

• 无严格长度限制：仅受数据库文件大小限制（默认最大 BLOB 大小为 1GB，可通过配置调整）。
• 支持任意二进制数据：如字节流、文件片段、十六进制数据等。

示例值

• X'0102FF'：十六进制表示的二进制数据（对应字节 0x01、0x02、0xFF）。
• X'48656C6C6F'：字符串 "Hello" 的原始二进制数据。
• 图片的二进制字节流：如 PNG 格式的文件二进制数据。

3. 未指定类型（字段定义方式）

定义

• 未指定类型 是字段定义方式的一种，指创建表时字段未声明任何类型（如 CREATE TABLE t (id, name); 中的 id 和 name）。
• 亲和类型：自动推断为 NONE，数据插入时 不进行类型转换，直接以原始存储类存储。

存储方式

• 根据输入数据的存储类决定：
  • 输入为 INTEGER：存为 INTEGER。
  • 输入为 TEXT：存为 TEXT。
  • 输入为 BLOB：存为 BLOB。
  • 输入为 REAL：存为 REAL。

取值范围

• 取决于输入数据的存储类：
  • INTEGER：范围同 SQLite 的 INTEGER 存储类（-128 到 9223372036854775807 等）。
  • TEXT：无严格长度限制，支持 Unicode。
  • BLOB：无严格长度限制，支持任意二进制数据。

示例值

• 123：存储为 INTEGER。
• "未指定类型的文本"：存储为 TEXT。
• X'ABCDEF'：存储为 BLOB。
• 3.14：存储为 REAL。

三者关系总结

关键建议

1. 显式声明字段类型：

   • 对关键业务数据表，优先使用 BLOB 显式声明字段，避免依赖未指定类型导致的隐式推断问题。
   • 例如：CREATE TABLE files (content BLOB);。

2. BLOB 处理注意事项：

   • 插入 BLOB 数据时，使用参数化查询或二进制流操作，避免手动处理导致数据损坏。
   • 检索 BLOB 数据时，直接读取二进制字节流，无需解码。

3. 避免未指定类型的滥用：

   • 未指定类型字段可能导致后续插入数据类型不一致（如先插入 TEXT，后插入 INTEGER），影响数据完整性。
   • 对需要严格类型约束的字段，显式声明类型（如 TEXT、INTEGER）。

示例代码（SQLite）

-- 1. 使用 BLOB 字段存储二进制数据
CREATE TABLE images (
    id INTEGER PRIMARY KEY,
    image_data BLOB  -- 亲和类型为 NONE，存储二进制图片
);

-- 2. 未指定类型的字段（自动推断为 NONE）
CREATE TABLE logs (
    id INTEGER,
    message  -- 亲和类型为 NONE，存储原始数据
);

-- 3. 插入混合类型数据
INSERT INTO logs (id, message) VALUES 
(1, 'Error: File not found'),  -- 存储为 TEXT
(2, 12345),                    -- 存储为 INTEGER
(3, X'010203');                -- 存储为 BLOB

通过上述整合，可清晰理解 NONE 亲和类型、BLOB 字段名称 和 未指定类型字段定义方式 的关联与区别，确保在实际应用中正确选择数据类型，避免隐式转换带来的问题。

参考

• SQLite 数据类型-菜鸟教程
• Datatypes In SQLite Version 3
• Datatypes In SQLite Version 2