SQLite数据类型 #

-- SQLite 允许在同一列存储不同类型的数据
CREATE TABLE test (value);

INSERT INTO test VALUES (123);        -- 整数
INSERT INTO test VALUES (3.14);       -- 浮点数
INSERT INTO test VALUES ('Hello');    -- 文本
INSERT INTO test VALUES (X'010203');  -- BLOB
INSERT INTO test VALUES (NULL);       -- NULL

SELECT typeof(value), value FROM test;

-- 静态类型系统（如MySQL）
-- 列类型严格限制存储的数据类型

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

-- SQLite 允许这样（但不推荐）
INSERT INTO users VALUES (123, 'Hello');  -- 可以插入，但类型会被转换

-- NULL 表示缺失或未知的值
CREATE TABLE test (id INTEGER, value TEXT);

INSERT INTO test VALUES (1, NULL);

-- NULL 的特点
SELECT NULL = NULL;     -- 结果是 NULL
SELECT NULL IS NULL;    -- 结果是 1
SELECT NULL IS NOT NULL;-- 结果是 0

-- NULL 在运算中
SELECT NULL + 1;        -- 结果是 NULL
SELECT NULL || 'text';  -- 结果是 NULL
SELECT COALESCE(NULL, 'default');  -- 结果是 'default'

-- INTEGER 存储有符号整数
-- 根据值的大小自动选择存储大小

-- 存储大小：
-- 1字节：-128 到 127
-- 2字节：-32768 到 32767
-- 3字节：-8388608 到 8388607
-- 4字节：-2147483648 到 2147483647
-- 6字节：-140737488355328 到 140737488355327
-- 8字节：-9223372036854775808 到 9223372036854775807

CREATE TABLE numbers (value INTEGER);

INSERT INTO numbers VALUES (127);           -- 1字节
INSERT INTO numbers VALUES (32767);         -- 2字节
INSERT INTO numbers VALUES (2147483647);    -- 4字节
INSERT INTO numbers VALUES (9223372036854775807); -- 8字节

-- 查看存储类型
SELECT value, typeof(value) FROM numbers;

-- REAL 存储 8 字节 IEEE 浮点数
CREATE TABLE floats (value REAL);

INSERT INTO floats VALUES (3.14);
INSERT INTO floats VALUES (-1.5e10);
INSERT INTO floats VALUES (1.5E-10);

-- 整数可以存储为 REAL
INSERT INTO floats VALUES (100);  -- 存储为 100.0

SELECT value, typeof(value) FROM floats;

-- TEXT 存储字符串
-- 使用数据库编码（UTF-8 或 UTF-16）

CREATE TABLE texts (value TEXT);

INSERT INTO texts VALUES ('Hello World');
INSERT INTO texts VALUES ('你好世界');
INSERT INTO texts VALUES ('🎉 Emoji');

-- 字符串长度限制：10亿字节

SELECT value, typeof(value), length(value) FROM texts;

-- BLOB 存储二进制数据
-- 完全按输入存储，不做任何转换

CREATE TABLE blobs (value BLOB);

INSERT INTO blobs VALUES (X'0102030405');
INSERT INTO blobs VALUES (X'48656C6C6F');  -- 'Hello' 的十六进制

-- 使用 zeroblob 创建指定大小的 BLOB
INSERT INTO blobs VALUES (zeroblob(100));  -- 100字节的零

SELECT value, typeof(value), length(value) FROM blobs;

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

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

-- INTEGER 亲和性的列优先存储整数

CREATE TABLE int_test (
    col INT,
    col2 INTEGER,
    col3 BIGINT,
    col4 SMALLINT,
    col5 TINYINT,
    col6 MEDIUMINT
);

-- 插入整数
INSERT INTO int_test (col) VALUES (123);
SELECT typeof(col) FROM int_test;  -- integer

-- 插入浮点数（会转换为整数）
INSERT INTO int_test (col) VALUES (3.14);
SELECT col, typeof(col) FROM int_test WHERE col = 3;  -- 3, integer

-- 插入文本数字
INSERT INTO int_test (col) VALUES ('456');
SELECT col, typeof(col) FROM int_test WHERE col = 456;  -- 456, integer

-- TEXT 亲和性的列优先存储文本

CREATE TABLE text_test (
    col TEXT,
    col2 VARCHAR(255),
    col3 CHAR(20),
    col4 CLOB
);

-- 插入文本
INSERT INTO text_test (col) VALUES ('Hello');
SELECT typeof(col) FROM text_test;  -- text

-- 插入数字（会转换为文本）
INSERT INTO text_test (col) VALUES (123);
SELECT col, typeof(col) FROM text_test WHERE col = '123';  -- 123, text

-- 插入浮点数
INSERT INTO text_test (col) VALUES (3.14);
SELECT col, typeof(col) FROM text_test WHERE col = '3.14';  -- 3.14, text

-- BLOB 亲和性的列不做类型转换

CREATE TABLE blob_test (
    col BLOB,
    col2  -- 无类型声明也是 BLOB 亲和性
);

-- 插入什么就存储什么
INSERT INTO blob_test VALUES (123, 123);
INSERT INTO blob_test VALUES ('Hello', 'Hello');
INSERT INTO blob_test VALUES (3.14, 3.14);
INSERT INTO blob_test VALUES (X'010203', X'010203');

SELECT typeof(col), typeof(col2) FROM blob_test;
-- integer, integer
-- text, text
-- real, real
-- blob, blob

-- REAL 亲和性的列优先存储浮点数

CREATE TABLE real_test (
    col REAL,
    col2 DOUBLE,
    col3 FLOAT
);

-- 插入浮点数
INSERT INTO real_test (col) VALUES (3.14);
SELECT typeof(col) FROM real_test;  -- real

-- 插入整数（会转换为浮点数）
INSERT INTO real_test (col) VALUES (100);
SELECT col, typeof(col) FROM real_test WHERE col = 100;  -- 100.0, real

-- NUMERIC 亲和性智能选择存储类型

CREATE TABLE numeric_test (
    col NUMERIC,
    col2 DECIMAL(10,2),
    col3 BOOLEAN,
    col4 DATE,
    col5 DATETIME
);

-- 插入整数
INSERT INTO numeric_test (col) VALUES (123);
SELECT typeof(col) FROM numeric_test;  -- integer

-- 插入浮点数
INSERT INTO numeric_test (col) VALUES (3.14);
SELECT typeof(col) FROM numeric_test WHERE col = 3.14;  -- real

-- 插入文本数字
INSERT INTO numeric_test (col) VALUES ('456');
SELECT typeof(col) FROM numeric_test WHERE col = 456;  -- integer

-- 插入无法转换的文本
INSERT INTO numeric_test (col) VALUES ('Hello');
SELECT typeof(col) FROM numeric_test WHERE col = 'Hello';  -- text

-- BOOLEAN 类型
INSERT INTO numeric_test (col3) VALUES (TRUE);   -- 存储为 1
INSERT INTO numeric_test (col3) VALUES (FALSE);  -- 存储为 0
SELECT col3, typeof(col3) FROM numeric_test;     -- 1, integer / 0, integer

-- 类型亲和性决定存储时的转换规则

-- 规则1：BLOB 亲和性 - 不转换
CREATE TABLE t1 (col BLOB);
INSERT INTO t1 VALUES (123);     -- 存储为 integer
INSERT INTO t1 VALUES ('text');  -- 存储为 text

-- 规则2：TEXT 亲和性 - 转换为文本
CREATE TABLE t2 (col TEXT);
INSERT INTO t2 VALUES (123);     -- 转换为 '123'
INSERT INTO t2 VALUES (3.14);    -- 转换为 '3.14'

-- 规则3：INTEGER 亲和性 - 转换为整数
CREATE TABLE t3 (col INTEGER);
INSERT INTO t3 VALUES (3.14);    -- 转换为 3
INSERT INTO t3 VALUES ('123');   -- 转换为 123

-- 规则4：REAL 亲和性 - 转换为浮点数
CREATE TABLE t4 (col REAL);
INSERT INTO t4 VALUES (123);     -- 转换为 123.0
INSERT INTO t4 VALUES ('3.14');  -- 转换为 3.14

-- 规则5：NUMERIC 亲和性 - 智能选择
CREATE TABLE t5 (col NUMERIC);
INSERT INTO t5 VALUES ('123');   -- 转换为 integer 123
INSERT INTO t5 VALUES ('3.14');  -- 转换为 real 3.14
INSERT INTO t5 VALUES ('hello'); -- 保持 text 'hello'

-- 比较操作时的类型转换规则

-- 相同类型比较
SELECT 1 = 1;           -- 1 (integer = integer)
SELECT 'a' = 'a';       -- 1 (text = text)

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

-- NULL 比较
SELECT NULL = 1;        -- 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，但不相等)

-- BLOB 比较
SELECT X'61' = 'a';     -- 0 (BLOB 和 TEXT 不相等)

-- 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'
SELECT CAST(X'48656C6C6F' AS TEXT); -- 'Hello'

-- 转换为 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)

-- typeof() 返回值的存储类型

SELECT typeof(NULL);      -- 'null'
SELECT typeof(123);       -- 'integer'
SELECT typeof(3.14);      -- 'real'
SELECT typeof('Hello');   -- '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 ('Hello');
INSERT INTO mixed VALUES (X'0102');
INSERT INTO mixed VALUES (NULL);

SELECT value, typeof(value) FROM mixed;

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

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

-- 插入布尔值
INSERT INTO flags (is_active, has_permission) VALUES (TRUE, FALSE);
INSERT INTO flags (is_active, has_permission) VALUES (1, 0);
INSERT INTO flags (is_active, has_permission) VALUES ('true', 'false');

SELECT 
    id,
    is_active,
    typeof(is_active),
    has_permission,
    typeof(has_permission)
FROM flags;

-- 布尔比较
SELECT * FROM flags WHERE is_active = TRUE;
SELECT * FROM flags WHERE is_active = 1;
SELECT * FROM flags WHERE is_active;  -- 简写

-- 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
);

-- 插入日期时间
INSERT INTO events (name, created_at, julian_date, unix_time)
VALUES (
    'Event 1',
    '2024-01-15 10:30:00',
    julianday('2024-01-15 10:30:00'),
    strftime('%s', '2024-01-15 10:30:00')
);

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

-- 日期计算
SELECT date('now', '+1 day');          -- 明天
SELECT date('now', '-7 days');         -- 7天前
SELECT date('now', '+1 month');        -- 下个月
SELECT datetime('now', '+1 hour');     -- 1小时后

-- 日期格式化
SELECT strftime('%Y-%m-%d', 'now');    -- 2024-01-15
SELECT strftime('%H:%M:%S', 'now');    -- 10:30:00
SELECT strftime('%Y年%m月%d日', 'now'); -- 2024年01月15日

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

CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    data JSON
);

-- 插入 JSON 数据
INSERT INTO documents (data) VALUES ('{"name": "Alice", "age": 25}');
INSERT INTO documents (data) VALUES ('[1, 2, 3, 4, 5]');

-- JSON 函数
SELECT json_extract(data, '$.name') FROM documents;
SELECT json_extract(data, '$[0]') FROM documents WHERE id = 2;

-- 创建 JSON
SELECT json_object('name', 'Alice', 'age', 25);
SELECT json_array(1, 2, 3, 4, 5);

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

-- SQLite 没有内置的 IS_INTEGER 等函数
-- 可以使用 typeof 实现

SELECT 
    value,
    CASE typeof(value)
        WHEN 'integer' THEN '是整数'
        WHEN 'real' THEN '是浮点数'
        WHEN 'text' THEN '是文本'
        WHEN 'blob' THEN '是二进制'
        ELSE '是NULL'
    END AS type_desc
FROM test_table;

-- 注意：字符串与数字比较时，字符串会转换为数字

-- 创建测试表
CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT);
INSERT INTO test (value) VALUES ('123');
INSERT INTO test (value) VALUES ('abc');
INSERT INTO test (value) VALUES ('123abc');

-- 查询
SELECT * FROM test WHERE value = 123;
-- 返回：id=1 ('123' 转换为 123)
-- 返回：id=3 ('123abc' 转换为 123)

-- 解决方案：显式比较字符串
SELECT * FROM test WHERE value = '123';
-- 只返回：id=1

-- INTEGER PRIMARY KEY 是特殊的
-- 它是 ROWID 的别名

CREATE TABLE users (
    id INTEGER PRIMARY KEY,  -- 自增主键
    name TEXT
);

-- AUTOINCREMENT 可选
CREATE TABLE users2 (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

-- 区别：
-- INTEGER PRIMARY KEY：重用已删除的ID
-- INTEGER PRIMARY KEY AUTOINCREMENT：永不重用ID

-- 浮点数精度问题
SELECT 0.1 + 0.2;           -- 0.30000000000000004
SELECT 0.1 + 0.2 = 0.3;     -- 0 (不相等！)

-- 解决方案：使用 ROUND
SELECT ROUND(0.1 + 0.2, 2);  -- 0.3
SELECT ROUND(0.1 + 0.2, 2) = 0.3;  -- 1

-- 或使用整数存储金额
-- 存储分而不是元
CREATE TABLE prices (
    id INTEGER PRIMARY KEY,
    price_cents INTEGER  -- 以分为单位
);