SQLite - 数据类型

你好，未来的数据库大师们！今天，我们将深入探索SQLite数据类型的迷人世界。如果你是编程新手，不用担心——我将作为你在这个旅程中的友好向导，一步步为你解释所有内容。那么，拿起一杯咖啡（或者如果你喜欢，一杯茶），让我们开始吧！

SQLite - Data Type

SQLite 存储类

首先，让我们谈谈SQLite的存储类。可以把这些看作是SQLite中数据的基本构建块。主要有五种存储类：

NULL
整数 (INTEGER)
实数 (REAL)
文本 (TEXT)
二进制大对象 (BLOB)

让我们通过一些例子来分解这些：

NULL

CREATE TABLE example (
id INTEGER,
name TEXT,
age INTEGER
);

INSERT INTO example (id, name) VALUES (1, 'Alice');

在这个例子中，我们创建了一个表并插入了一行，其中'age'列没有指定。SQLite会自动为其分配一个NULL值。NULL代表数据的缺失——就像说，“我们不知道Alice的年龄。”

整数 (INTEGER)

INSERT INTO example (id, name, age) VALUES (2, 'Bob', 30);

在这里，我们将Bob的年龄插入为30。SQLite将其存储为整数。整数是没有小数点的完整数字。

实数 (REAL)

CREATE TABLE products (
id INTEGER,
name TEXT,
price REAL
);

INSERT INTO products (id, name, price) VALUES (1, '咖啡', 3.99);

价格3.99被存储为实数值，这是SQLite处理小数数字的方式。

文本 (TEXT)

INSERT INTO example (id, name, age) VALUES (3, 'Charlie', '25岁');

尽管我们在存储年龄，但在这里我们使用了文本。SQLite会将"25岁"存储为字符字符串。

二进制大对象 (BLOB)

BLOB代表二进制大对象。它用于存储如图像或文件这样的二进制数据。

CREATE TABLE files (
id INTEGER,
name TEXT,
content BLOB
);

-- 插入BLOB通常在应用程序代码中完成，而不是在原始SQL中

SQLite 亲和类型

现在，让我们来谈谈类型亲和性。这是SQLite处理数据类型灵活性的方式。把它想象成SQLite尽力理解你想存储的数据类型。

SQLite中有五种类型亲和性：

文本 (TEXT)
数值 (NUMERIC)
整数 (INTEGER)
实数 (REAL)
二进制大对象 (BLOB)

下面是一个有趣的表格，展示SQLite如何决定使用哪种亲和性：

如果声明的类型包含...	亲和性是...
INT	INTEGER
CHAR, CLOB, TEXT	TEXT
BLOB	BLOB
REAL, FLOA, DOUB	REAL
其他任何内容	NUMERIC

让我们看看它是如何工作的：

CREATE TABLE affinity_example (
a TEXT,
b NUMERIC,
c INTEGER,
d REAL,
e BLOB
);

INSERT INTO affinity_example VALUES
('123', '456', '789', '101112', x'ABCDEF');

SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM affinity_example;

这将输出：

text, integer, integer, real, blob

注意到'456'由于NUMERIC亲和性变成了整数！

SQLite 亲和性和类型名称

SQLite在列类型名称方面非常灵活。你可以使用标准的SQL类型，SQLite会将它们映射到它的亲和性。以下是一个快速参考：

类型名称	亲和性
INT, INTEGER, TINYINT, SMALLINT, MEDIUMINT, BIGINT, UNSIGNED BIG INT, INT2, INT8	INTEGER
CHARACTER(20), VARCHAR(255), VARYING CHARACTER(255), NCHAR(55), NATIVE CHARACTER(70), NVARCHAR(100), TEXT, CLOB	TEXT
BLOB, 未指定数据类型	BLOB
REAL, DOUBLE, DOUBLE PRECISION, FLOAT	REAL
NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME	NUMERIC

布尔数据类型

SQLite没有单独的布尔类型，但别让它愚弄你！我们仍然可以处理true/false值。SQLite将0视为false，将1视为true。

CREATE TABLE tasks (
id INTEGER PRIMARY KEY,
description TEXT,
completed BOOLEAN
);

INSERT INTO tasks (description, completed) VALUES
('学习SQLite', 0),
('精通SQL', 1);

SELECT * FROM tasks WHERE completed = TRUE;

这将返回"精通SQL"任务，因为其'completed'值为1（true）。

日期和时间数据类型

最后但同样重要的是，让我们来谈谈日期和时间。SQLite没有内置的日期或时间类型，但它提供了几个日期和时间函数来处理这些值。

我们通常将日期和时间存储为TEXT，INTEGER或REAL：

CREATE TABLE events (
id INTEGER PRIMARY KEY,
name TEXT,
date_text TEXT,
date_int INTEGER,
date_real REAL
);

INSERT INTO events (name, date_text, date_int, date_real) VALUES
('SQLite研讨会', '2023-06-15', 1686787200, 1686787200.0);

SELECT name,
date_text,
datetime(date_int, 'unixepoch') as date_from_int,
datetime(date_real, 'unixepoch') as date_from_real
FROM events;

这将输出类似以下内容：

SQLite研讨会, 2023-06-15, 2023-06-15 00:00:00, 2023-06-15 00:00:00

就这样！我们已经涵盖了SQLite中的主要数据类型，从基本存储类到更细致的类型亲和性概念。记住，SQLite的灵活性既是其优势，也是需要注意的地方。始终测试你的数据以确保它按预期存储和检索。

熟能生巧，所以不要害怕尝试这些概念。创建你自己的表，插入一些数据，看看SQLite如何处理不同的情况。在你意识到之前，你将成为SQLite数据类型的专家！

快乐编码，愿你的数据库永远规范化！

Credits: Image by storyset