面试题：SQLite中亲缘性如何影响数据存储

Question

请阐述SQLite执行中的亲缘性概念，以及它是怎样影响数据实际存储的？举例说明不同亲缘性设置下，相同数据类型的数据存储差异。

Answer 1

SQLite亲缘性概念

SQLite中的亲缘性（Affinity）指的是一种数据类型的特性，它决定了在某个特定列中优先存储的数据类型。SQLite有五种亲缘性类型：TEXT、NUMERIC、INTEGER、REAL 和 NONE。

1. TEXT 亲缘性：当插入数据时，会尝试将数据转换为文本字符串进行存储。如果数据本身就是文本或者可以轻松转换为文本（例如数字字符串），就会以文本形式存储。
2. NUMERIC 亲缘性：列会优先尝试将数据存储为数值类型。数据会按照能容纳该值的最小数值类型存储，如整数会存储为 INTEGER，小数会存储为 REAL。
3. INTEGER 亲缘性：该列会尝试将数据存储为整数。如果值无法准确表示为整数（如小数），存储可能会失败或出现截断。
4. REAL 亲缘性：列优先存储数据为浮点数（REAL 类型）。任何数值，包括整数，都可能被存储为浮点数。
5. NONE 亲缘性：列不优先考虑任何数据类型，数据会以其原始输入形式存储，不进行类型转换。

对数据实际存储的影响

亲缘性影响了数据插入到列中时的存储方式。它决定了SQLite在遇到不同类型数据时，如何进行类型转换和存储优化。

不同亲缘性设置下相同数据类型的数据存储差异示例

假设我们有以下SQLite表定义：

CREATE TABLE example (
    text_col TEXT,
    numeric_col NUMERIC,
    integer_col INTEGER,
    real_col REAL,
    none_col NONE
);

插入整数 10

• TEXT 亲缘性 (text_col)：存储为文本字符串 "10"。
• NUMERIC 亲缘性 (numeric_col)：存储为 INTEGER 类型的 10。
• INTEGER 亲缘性 (integer_col)：存储为 INTEGER 类型的 10。
• REAL 亲缘性 (real_col)：存储为 REAL 类型的 10.0。
• NONE 亲缘性 (none_col)：可能存储为与输入相同的形式（例如，如果通过 INSERT 语句以数值形式输入，可能存储为数值表示）。

插入小数 3.14

• TEXT 亲缘性 (text_col)：存储为文本字符串 "3.14"。
• NUMERIC 亲缘性 (numeric_col)：存储为 REAL 类型的 3.14。
• INTEGER 亲缘性 (integer_col)：可能会截断为 3 存储（如果允许截断），否则插入可能失败。
• REAL 亲缘性 (real_col)：存储为 REAL 类型的 3.14。
• NONE 亲缘性 (none_col)：可能存储为与输入相同的形式（例如数值 3.14）。

通过这些示例可以看到，相同的数据值在不同亲缘性设置的列中存储方式会有所不同，这体现了SQLite亲缘性对数据存储的影响。