SQLite - Типы данных

Привет, будущие маги баз данных! Сегодня мы окунемся в fascinierende мир типов данных SQLite. Не волнуйтесь, если вы новички в программировании - я буду вашим доброжелательным проводником в этом путешествии, объясняя все шаг за шагом. Так что возьмите杯 кофе (или чая, если это ваше дело), и давайте начнем!

Классы хранения SQLite

Сначала давайте поговорим о классах хранения SQLite. Представьте их как основные строительные блоки данных в SQLite. Есть пять основных классов хранения:

1. NULL
2. INTEGER
3. REAL
4. TEXT
5. BLOB

Давайте разберем их с примерами:

NULL

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

INSERT INTO example (id, name) VALUES (1, 'Алиса');

В этом примере мы создали таблицу и inserted一行, где столбец 'age' не указан. SQLite автоматически assigns ему значение NULL. NULL представляет собой отсутствие данных - это как说法, "Мы не знаем возраста Алисы."

INTEGER

INSERT INTO example (id, name, age) VALUES (2, 'Боб', 30);

Здесь мы inserted возраст Боба как 30. SQLite хранит это как INTEGER. Целые числа - это целые числа без десятичных знаков.

REAL

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

INSERT INTO products (id, name, price) VALUES (1, 'Кофе', 3.99);

Цена 3.99 хранится как значение REAL, что является способом обработки десятичных чисел в SQLite.

TEXT

INSERT INTO example (id, name, age) VALUES (3, 'Чарли', '25 лет');

Хотя мы храним возраст, здесь мы использовали TEXT. SQLite будет хранить "25 лет" как строку символов.

BLOB

BLOB означает Binary Large Object. Он используется для хранения двоичных данных, таких как изображения или файлы.

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

-- Вставка BLOB обычно выполняется в коде приложения, а не в сырое SQL

Тип affinity в SQLite

Теперь давайте поговорим о типе affinity. Это способ SQLite быть гибким с типами данных. Представьте, что SQLite старается как можно лучше понять, какой тип данных вы хотите сохранить.

Есть пять типов affinity в SQLite:

1. TEXT
2. NUMERIC
3. INTEGER
4. REAL
5. BLOB

Вот интересная таблица, чтобы показать вам, как SQLite решает, какой affinity использовать:

Если declares тип содержит...	Affinity...
INT	INTEGER
CHAR, CLOB, TEXT	TEXT
BLOB	BLOB
REAL, FLOA, DOUB	REAL
Anything else	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' стал целым числом из-за affinity NUMERIC!

Affinity и имена типов

SQLite quite гибок, когда дело доходит до имен столбцов. Вы можете использовать стандартные SQL типы, и SQLite будет map их к своим affinity. Вот quick справочник:

Name Type	Affinity
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, no datatype specified	BLOB
REAL, DOUBLE, DOUBLE PRECISION, FLOAT	REAL
NUMERIC, DECIMAL(10,5), BOOLEAN, DATE, DATETIME	NUMERIC

Boolean Datatype

SQLite не имеет отдельного типа Boolean, но не дайте это вас обмануть! Мы все равно можем работать с true/false значениями. SQLite treat 0 как false и 1 как true.

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

INSERT INTO tasks (description, completed) VALUES
('Learn SQLite', 0),
('Master SQL', 1);

SELECT * FROM tasks WHERE completed = TRUE;

Это вернет "Master SQL" задачу, потому что значение 'completed' равно 1 (true).

Тип данных даты и времени

Last но не least, давайте поговорим о датах и времени. 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 Workshop', '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 Workshop, 2023-06-15, 2023-06-15 00:00:00, 2023-06-15 00:00:00

И вот и все! Мы рассмотрели основные типы данных в SQLite, от базовых классов хранения до более сложных концепций типа affinity. Помните, что гибкость SQLite - это как его сила, так и что-то, о чем нужно быть внимательным. Всегда тестируйте свои данные, чтобы убедиться, что они хранятся и retrieve как вы ожидаете.

Практика делает perfect, так что не бойтесь экспериментировать с этими концепциями. Создавайте свои таблицы, insert данные и смотрите, как SQLite справляется с разными сценариями. Before вы знаете это, вы будете экспертом по типам данных SQLite!

Счастливого кодирования, и пусть ваши базы данных всегда будут normalized!

Credits: Image by storyset