SQLite动态类型系统 #

-- SQLite 使用动态类型系统
-- 与其他数据库的静态类型系统不同

-- 静态类型系统（MySQL、PostgreSQL）
-- 列类型严格限制存储的数据类型
CREATE TABLE users (
    name VARCHAR(50),  -- 只能存储字符串
    age INT            -- 只能存储整数
);

-- SQLite 动态类型系统
-- 列类型只是建议，实际存储类型由值决定
CREATE TABLE users (
    name TEXT,   -- 建议存储文本
    age INTEGER  -- 建议存储整数
);

-- SQLite 允许这样（但不推荐）
INSERT INTO users VALUES (123, 'Hello');

-- 优势：
-- 1. 灵活性高
-- 2. 无需预定义所有类型
-- 3. 支持存储任意类型的数据
-- 4. 简化数据迁移

-- 劣势：
-- 1. 可能存储不一致的数据
-- 2. 需要应用层验证
-- 3. 查询优化器可能选择次优计划

-- SQLite 只有 5 种基本存储类

-- 1. NULL - 空值
INSERT INTO test VALUES (NULL);

-- 2. INTEGER - 有符号整数
-- 根据值自动选择存储大小：
-- 1字节：-128 到 127
-- 2字节：-32768 到 32767
-- 3字节：-8388608 到 8388607
-- 4字节：-2147483648 到 2147483647
-- 6字节：-140737488355328 到 140737488355327
-- 8字节：-9223372036854775808 到 9223372036854775807
INSERT INTO test VALUES (123);

-- 3. REAL - 8字节IEEE浮点数
INSERT INTO test VALUES (3.14);

-- 4. TEXT - UTF-8/UTF-16字符串
INSERT INTO test VALUES ('Hello');

-- 5. BLOB - 二进制数据
INSERT INTO test VALUES (X'01020304');

-- 查看存储类型
SELECT typeof(NULL);      -- 'null'
SELECT typeof(123);       -- 'integer'
SELECT typeof(3.14);      -- 'real'
SELECT typeof('Hello');   -- 'text'
SELECT typeof(X'0102');   -- 'blob'

-- 存储类不可变性
-- 值的存储类在存储后不会改变（除非显式转换）
CREATE TABLE test (value);
INSERT INTO test VALUES (123);
SELECT typeof(value) FROM test;  -- 'integer'

-- 查询时转换
SELECT CAST(value AS TEXT) FROM test;
SELECT typeof(CAST(value AS TEXT)) FROM test;  -- 'text'

-- 类型亲和性决定SQLite如何处理列的数据类型
-- 当声明列类型时，SQLite会根据类型名称确定亲和性

CREATE TABLE example (
    col1 INT,           -- INTEGER 亲和性
    col2 VARCHAR(255),  -- TEXT 亲和性
    col3 FLOAT,         -- REAL 亲和性
    col4 BLOB,          -- BLOB 亲和性
    col5 NUMERIC        -- NUMERIC 亲和性
);

-- 规则1：INTEGER 亲和性
-- 类型名包含：INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT
CREATE TABLE t1 (col INT);
INSERT INTO t1 VALUES (3.14);
SELECT col, typeof(col) FROM t1;  -- 3, 'integer'

-- 规则2：TEXT 亲和性
-- 类型名包含：CHAR, CLOB, TEXT
CREATE TABLE t2 (col VARCHAR(255));
INSERT INTO t2 VALUES (123);
SELECT col, typeof(col) FROM t2;  -- '123', 'text'

-- 规则3：BLOB 亲和性
-- 类型名包含：BLOB 或无类型声明
CREATE TABLE t3 (col BLOB);
INSERT INTO t3 VALUES (123);
SELECT col, typeof(col) FROM t3;  -- 123, 'integer'

-- 规则4：REAL 亲和性
-- 类型名包含：REAL, DOUBLE, FLOAT
CREATE TABLE t4 (col REAL);
INSERT INTO t4 VALUES (123);
SELECT col, typeof(col) FROM t4;  -- 123.0, 'real'

-- 规则5：NUMERIC 亲和性
-- 其他所有类型
CREATE TABLE t5 (col NUMERIC);
INSERT INTO t5 VALUES ('123');
SELECT col, typeof(col) FROM t5;  -- 123, 'integer'

-- 类型亲和性对照表
-- | 声明类型              | 亲和性   |
-- |-----------------------|----------|
-- | INT, INTEGER, BIGINT  | INTEGER  |
-- | CHAR, VARCHAR, TEXT   | TEXT     |
-- | BLOB, 无类型          | BLOB     |
-- | REAL, DOUBLE, FLOAT   | REAL     |
-- | NUMERIC, DECIMAL      | NUMERIC  |
-- | BOOLEAN, DATE         | NUMERIC  |

-- INTEGER 亲和性
CREATE TABLE int_test (col INTEGER);
INSERT INTO int_test VALUES (3.14);    -- 转换为 3
INSERT INTO int_test VALUES ('123');   -- 转换为 123
INSERT INTO int_test VALUES ('abc');   -- 转换为 0
SELECT col, typeof(col) FROM int_test;

-- TEXT 亲和性
CREATE TABLE text_test (col TEXT);
INSERT INTO text_test VALUES (123);    -- 转换为 '123'
INSERT INTO text_test VALUES (3.14);   -- 转换为 '3.14'
SELECT col, typeof(col) FROM text_test;

-- BLOB 亲和性（不转换）
CREATE TABLE blob_test (col BLOB);
INSERT INTO blob_test VALUES (123);
INSERT INTO blob_test VALUES ('text');
SELECT col, typeof(col) FROM blob_test;
-- 123, 'integer'
-- 'text', 'text'

-- NUMERIC 亲和性（智能转换）
CREATE TABLE num_test (col NUMERIC);
INSERT INTO num_test VALUES ('123');   -- 转换为 123 (integer)
INSERT INTO num_test VALUES ('3.14');  -- 转换为 3.14 (real)
INSERT INTO num_test VALUES ('hello'); -- 保持 'hello' (text)
SELECT col, typeof(col) FROM num_test;

-- 比较规则

-- 相同类型比较
SELECT 1 = 1;           -- 1
SELECT 'a' = 'a';       -- 1

-- 不同类型比较
-- 规则：NULL < BLOB < TEXT < INTEGER < REAL

-- NULL 比较
SELECT NULL = 1;        -- NULL
SELECT NULL IS NULL;    -- 1

-- INTEGER 和 REAL
SELECT 1 = 1.0;         -- 1 (数值比较)
SELECT 1 < 1.5;         -- 1

-- TEXT 和 INTEGER
SELECT '1' = 1;         -- 1 (文本转换为数值)
SELECT '1.5' = 1.5;     -- 1
SELECT 'abc' = 0;       -- 0 (文本转换为0，但不相等)

-- CAST 表达式

-- 转换为 INTEGER
SELECT CAST('123' AS INTEGER);    -- 123
SELECT CAST(3.14 AS INTEGER);     -- 3
SELECT CAST('abc' AS INTEGER);    -- 0

-- 转换为 REAL
SELECT CAST('3.14' AS REAL);      -- 3.14
SELECT CAST(123 AS REAL);         -- 123.0

-- 转换为 TEXT
SELECT CAST(123 AS TEXT);         -- '123'
SELECT CAST(3.14 AS TEXT);        -- '3.14'

-- 转换为 BLOB
SELECT CAST('Hello' AS BLOB);     -- X'48656C6C6F'

-- 转换为 NUMERIC
SELECT CAST('123' AS NUMERIC);    -- 123 (integer)
SELECT CAST('3.14' AS NUMERIC);   -- 3.14 (real)

-- SQLite 没有独立的布尔类型
-- 使用 INTEGER 存储：0 = false, 1 = true

CREATE TABLE flags (
    id INTEGER PRIMARY KEY,
    is_active BOOLEAN DEFAULT 1
);

INSERT INTO flags (is_active) VALUES (TRUE);   -- 存储为 1
INSERT INTO flags (is_active) VALUES (FALSE);  -- 存储为 0
INSERT INTO flags (is_active) VALUES (1);
INSERT INTO flags (is_active) VALUES (0);

SELECT is_active, typeof(is_active) FROM flags;
-- 1, 'integer'
-- 0, 'integer'

-- SQLite 没有独立的日期时间类型
-- 使用 TEXT, REAL 或 INTEGER 存储

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    name TEXT,
    
    -- TEXT 格式：'YYYY-MM-DD HH:MM:SS'
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    
    -- REAL 格式：Julian day number
    julian_date REAL,
    
    -- INTEGER 格式：Unix timestamp
    unix_time INTEGER
);

-- 日期时间函数
SELECT date('now');                    -- 当前日期
SELECT time('now');                    -- 当前时间
SELECT datetime('now');                -- 当前日期时间
SELECT julianday('now');               -- Julian day
SELECT strftime('%s', 'now');          -- Unix timestamp

-- SQLite 3.38+ 内置 JSON 支持
-- JSON 数据存储为 TEXT

CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    data TEXT  -- JSON存储为TEXT
);

INSERT INTO documents (data) 
VALUES ('{"name": "Alice", "age": 25}');

-- JSON 函数
SELECT json_extract(data, '$.name') FROM documents;
SELECT json_object('name', 'Alice', 'age', 25);

-- typeof() 返回存储类型名称
SELECT typeof(NULL);        -- 'null'
SELECT typeof(123);         -- 'integer'
SELECT typeof(3.14);        -- 'real'
SELECT typeof('text');      -- 'text'
SELECT typeof(X'0102');     -- 'blob'

-- 检查列的实际存储类型
CREATE TABLE mixed (value);
INSERT INTO mixed VALUES (123);
INSERT INTO mixed VALUES (3.14);
INSERT INTO mixed VALUES ('text');

SELECT value, typeof(value) FROM mixed;

-- 推荐：保持数据类型一致
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER,
    salary REAL
);

INSERT INTO users (name, age, salary) 
VALUES ('John', 25, 50000.00);

-- 不推荐：混合类型
INSERT INTO users (name, age, salary) 
VALUES (123, 'twenty-five', 'fifty thousand');

-- 使用CHECK约束验证数据类型
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL CHECK(typeof(name) = 'text'),
    age INTEGER CHECK(typeof(age) = 'integer'),
    salary REAL CHECK(typeof(salary) = 'real')
);

-- Python 示例：应用层验证
import sqlite3

def insert_user(conn, name, age):
    if not isinstance(name, str):
        raise ValueError("name must be string")
    if not isinstance(age, int):
        raise ValueError("age must be integer")
    
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO users (name, age) VALUES (?, ?)",
        (name, age)
    )
    conn.commit()