SQLite -- 一个遵守君子协定的数据库

用惯了Oracle、PostgreSQL等数据库，今天接触到SQLite，简单尝试了下使用，顿感震惊！！！
与传统的关系型数据库（如 MySQL、PostgreSQL 等）相比，它的约束是真的宽松。具体来说：在 SQLite 中，数据类型没有严格的约束，这意味着 SQLite 不会强制执行列的类型约束，你可以向任何列插入任何类型的数据，而 SQLite 会根据数据的内容自动选择存储方式。

1. SQLite 的底层类型：五个存储类

SQLite “底层”采用了所谓的 存储类（storage classes）而非传统意义上的数据类型。SQLite 中的存储类有以下几种：

• NULL：表示无值。
• INTEGER：用于存储整数。
• REAL：用于存储浮点数（通常为 8 字节浮动点数）。
• TEXT：用于存储文本数据（以 UTF-8 或 UTF-16 编码）。
• BLOB：用于存储二进制数据。

2. SQLite 的类型约束：宽松无力

SQLite 支持在表中定义列的数据类型，但这并不是强制的，也不起约束作用，甚至你可以不指定或者随便起个名字。
SQLite 使用一种称为 类型亲和性（type affinity）的机制来决定如何存储数据。

• 类型亲和性：列的数据类型与实际存储的类型可能并不完全匹配。SQLite 会根据列定义的类型和实际插入的数据类型来自动选择存储的格式。这意味着，即使你定义了一个列为 INTEGER 类型，实际上也可以向该列插入 TEXT 类型的字符串。

例如：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

虽然 id 列被定义为 INTEGER，但是你仍然可以插入 TEXT 类型的值：

INSERT INTO users (id, name) VALUES ('string_id', 'Alice');

SQLite 会存储该值为文本，而不是整数。这是因为 SQLite 的类型系统更宽松，允许存储不同类型的数据，即使它与列定义的类型不匹配。

3. 列类型的亲和性规则：声明成底层类也不能保证数据类型

SQLite 会根据列的声明类型选择最适合存储数据的存储类，以下是 SQLite 的 类型亲和性规则：

• NULL 类型亲和性：列声明为 NULL 类型时，SQLite 不会强制约束数据类型，任何数据类型都可以插入。
• INTEGER 类型亲和性：INTEGER 列优先接受整数值，但也可以接受其他类型的值，如 TEXT、REAL 等，SQLite 会根据需要进行转换。
• TEXT 类型亲和性：TEXT 列优先接受文本数据，但也可以接受整数、浮点数等类型，SQLite 会进行转换。
• REAL 类型亲和性：REAL 列优先接受浮点数，但也可以接受整数或文本类型，SQLite 会进行转换。
• BLOB 类型亲和性：BLOB 列用于存储原始二进制数据，不会进行任何转换。

4. 实例演示：数据类型有多宽松？！

假设我们创建了一个表，定义了不同类型的列：

CREATE TABLE test (
    id INTEGER,
    name helloWorld, -- 不指定也可以
    age INTEGER
);

你可以插入与列定义类型不匹配的数据：

-- 向 id 列插入字符串
INSERT INTO test (id, name, age) VALUES ('abc', 'Alice', 30);

-- 向 age 列插入浮动数
INSERT INTO test (id, name, age) VALUES (1, 'Bob', 25.5);

上述 SQL 语句不会引发错误，因为 SQLite 允许插入与列类型不匹配的值，并会自动进行类型转换：

• 在第一个插入中，'abc' 被存储为文本，而不是整数。
• 在第二个插入中，25.5（浮点数）会存储为 REAL 类型，尽管 age 列被定义为 INTEGER 类型。

5. 如何让它有所约束？

虽然 SQLite 不会强制执行列的类型约束，但仍然可以通过以下几种方式来规范化：

5.1 使用触发器（Triggers）进行类型检查

可以使用触发器来检查数据的类型，并在不符合要求时触发错误。例如，假设你希望 age 列只接受整数，可以创建一个触发器来检查插入的数据类型：

CREATE TRIGGER check_age_type
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
    -- 检查插入数据的 age 列是否为整数
    SELECT
    CASE
        WHEN NEW.age NOT LIKE '%[^0-9]%' THEN NULL  -- 确保 age 为数字
        ELSE RAISE(ABORT, 'age must be an integer') -- 否则中止
    END;
END;

5.2 使用 CHECK 约束

虽然 SQLite 对类型不强制约束，但可以使用 CHECK 约束来控制列的数据范围。例如，要求 age 列只允许正整数值：

CREATE TABLE test (
    id INTEGER,
    name TEXT,
    age INTEGER CHECK(age > 0) CHECK(typeof(age) = 'integer') 
);

这个 CHECK 约束可以确保 age 列只能插入大于 0 的整数，且保证不能随便插数据进去。

6. 总结

SQLite 的数据类型约束相对宽松，它采用的是动态类型系统。虽然你可以为列定义数据类型，但SQLite 不会严格地检查插入数据的类型。如果你定义一个 INTEGER 列，可以插入文本、浮点数、整数等类型的数据，SQLite 会根据需要自动转换这些数据。

这种宽松的设计使得 SQLite 非常灵活，适合嵌入式应用或轻量级的数据库需求，但也可能导致数据的一致性和准确性问题，特别是当你希望强制执行类型安全时。为了增强数据的正确性，开发者可以使用触发器和 CHECK 约束来手动控制数据类型。