Руководство по типам данных SQLite

SQLite — популярная, лёгкая система баз данных, хранящая данные в одном файле. Её простоте способствует гибкая, динамическая система типов, отличающаяся от статических, жёстких типов, используемых в других системах баз данных, таких как MySQL, PostgreSQL или SQL Server, требующих указания типов данных при создании таблиц.

Как работают типы в SQLite

В SQLite не нужно указывать тип данных для столбцов при создании таблицы. Вместо этого SQLite использует динамическую типизацию, то есть присваивает тип каждому вставленному значению на основе его содержимого. Тем не менее при создании столбцов в таблицах SQLite можно указывать типы данных, служащие подсказкой для базы данных, какой тип данных следует ожидать. Однако SQLite всё равно будет хранить данные любого типа в любом столбце, независимо от объявленного типа.

Типы данных/классы хранения в SQLite

Значения SQLite во всех столбцах (даже в тех, что содержат объявленные типы) могут использовать любой из следующих пяти классов хранения:

• TEXT: Любая строка текста, включая числа, даты и другие типы.
• INTEGER: целочисленные значения.
• REAL: значения с плавающей точкой.
• BLOB: двоичные большие объекты, такие как изображения или другие двоичные данные.
• NULL: значение является null (пустым).

Родство/аффинитивность типов

В SQLite используются не строгие типы, а концепцию, называемую родство типов или аффинитивность типов. Это правила, используемые SQLite для определения типа значения, хранящегося в столбце, и класса хранения, используемого для этого значения. Класс хранения — это более общая концепция, группирующая схожие типы.

Хотя между классом хранения и родством типа существует связь, родство имеет отношение к тому, как SQLite интерпретирует данные, вставленные или обновлённые в столбце. Родство типов даёт указания по классу хранения, используемому для значения в столбце, но не ограничивает тип данных, хранимых в этом столбце.

Родство/аффинитивность типов в SQLite следующее:

• TEXT: любая строка текста, включая числа, даты и другие типы.
• NUMERIC: любое число, включая целые числа и значения с плавающей точкой.
• INTEGER: целочисленные значения.
• REAL: значения с плавающей точкой.
• BLOB: двоичные большие объекты, такие как изображения или другие двоичные данные. (Исторически назывался NONE)

При создании столбца можно указать родство с типом, чтобы дать SQLite подсказку о типе данных, предполагаемых к хранению в этом столбце. Затем SQLite будет использовать эту информацию для определения класса хранения значений в этом столбце. Однако SQLite всё равно позволяет хранить данные любого типа в любом столбце, независимо от объявленного типа.

Допустим, вы создали следующую таблицу:

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

В этой таблице столбец id должен хранить целочисленные значения, столбец name — текстовые, а столбец score — значения с плавающей точкой. Однако можно вставить текстовое значение в столбец score, и SQLite сохранит его как текстовое значение без каких-либо проблем.

INSERT INTO users (id, name, score) VALUES (1, 'Alice', 'unknown');
SELECT * FROM users WHERE id = 1;

Это вернёт

1 | Alice | unknown

Родство/аффинитивность типов, объявленное для столбца, определяет, как SQLite интерпретирует и хранит значения в этом столбце. Если объявить столбец с типом INTEGER, SQLite будет пытаться использовать класс хранения INTEGER для значений в этом столбце, когда это возможно. С другой стороны, если используется родство TEXT, SQLite будет использовать класс хранения TEXT для значений в этом столбце, даже если значение может быть сохранено как целое число.

Правила родства/аффинитивности типов в SQLite

SQLite использует следующие правила для определения класса хранения значения на основе его родства/аффинитивности типа:

1. INTEGER: Значение хранится как целое число, если оно может быть преобразовано в целое число без потери информации. Если значение не может быть преобразовано в целое число, SQLite будет хранить его как вещественное значение.
2. REAL: Значение хранится как вещественное, если оно может быть преобразовано в вещественное без потери информации. Если значение не может быть преобразовано в вещественное, SQLite будет хранить его как текст.
3. TEXT: Значения хранятся как текстовые.
4. NUMERIC: SQLite преобразует и сохраняет значение как целое или вещественное, если это возможно.
5. BLOB: значения хранятся в виде блобов. Примечание: Исторически этот параметр назывался NONE, но это сбивало с толку, так как могло означать нет родства типа, поэтому он был переименован в BLOB.

Сопоставление шаблонов при создании столбцов

Стоит отметить, что хотя у SQLite всего пять аффинитивов (включая BLOB, исторически называвшийся NONE), он предоставляет большую гибкость в том, как их называть, что может сделать SQLite более привычным для тех, кто знаком с другими системами баз данных. Для каждого из классов хранилищ существуют псевдонимы, используемые при создании столбцов. Они не зависят от регистра и следуют в следующем порядке:

• INTEGER: любой тип со строкой INT
  • например, (например, INT, INTEGER, SMALLINT)
• TEXT: любой тип со строками CHAR, CLOB или TEXT.
  • Например, CHARACTER, VARCHAR, NCHAR и т. д.
• BLOB: любой тип со строкой BLOB
• REAL: любой тип со строками REAL, FLOA или DOUB
  • Например, FLOAT, DOUBLE и т. д.
• NUMERIC: всё, что не соответствует вышеперечисленным

Какие типы SQLite не поддерживает

SQLite примечателен тем, что не поддерживает некоторые типы, распространённые в других системах баз данных. Однако всё равно можно хранить такие данные в SQLite и работать с ними так, как ожидается, но они будут храниться как один из пяти классов хранения, упомянутых выше.

К типам не поддерживаемым SQLite относятся:

• BOOLEAN/BIT
• DATE
• TIME
• DATETIME/TIMESTAMP

Работа с датами в SQLite

Несмотря на отсутствие в SQLite типа DATE, в SQLite можно работать с датами. Даты можно хранить в виде текстовых, вещественных или целочисленных значений, например, в формате ISO-8601, временных меток Unix или дней юлианского летоисчисления. SQLite предоставляет ряд встроенных функций даты и времени, помогающих работать с датами в запросах. Например, можно использовать функции DATE и TIME для извлечения компонентов даты и времени из текстового значения или функцию strftime для форматирования дат определённым образом.

Как правило, для дат лучше использовать человекочитаемый формат ISO-8601 (например, 2024-09-01 01:48:00), поскольку этот формат наиболее широко поддерживается и с ним легко работать в SQLite.

Ниже приведены примеры того, как можно работать с датами в SQLite:

-- Формат ISO-8601, хранящийся как TEXT (YYYY-MM-DD HH:MM:SS)
INSERT INTO events (name, date) VALUES ('Birthday', '2024-09-01');
INSERT INTO events (name, date) VALUES ('Appointment', '2024-09-01 01:48:00');
-- Временная метка Unix, хранящаяся как INTEGER (целое число секунд с 1970-01-01)
INSERT INTO events (name, date) VALUES ('Meeting', 1723772054);
-- Число дней юлианского календаря, хранится как REAL (Дни с полудня в Гринвиче 24 ноября 4714 года до н.э.)
INSERT INTO events (name, date) VALUES ('Conference', 2459786.574074);

Если вы используете какой-либо из этих форматов, SQLite предоставляет ряд встроенных функций даты и времени, помогающих работать с датами в ваших запросах. Например, можно использовать функции DATE и TIME для извлечения компонентов даты и времени из текстового значения или функцию strftime для форматирования дат определённым образом. SQLite использует класс хранения данных, чтобы определить, как интерпретировать и хранить значения даты; значения, хранящиеся как REAL, будут рассматриваться как числа юлианского дня, INTEGER — как временные метки Unix, а TEXT — как строки, отформатированные по стандарту ISO-8601.

Заключение

Теперь вы знакомы с классами хранения (TEXT, INTEGER, REAL, BLOB и NULL) и родством/аффинитивностью типов (TEXT, NUMERIC, INTEGER, REAL и BLOB), используемыми в системе динамических типов SQLite, а также с взаимосвязью между ними. Помните, что родство типов SQLite используются для определения класса хранения значения в столбце, но они не ограничивают тип данных, хранимых в этом столбце. Можно хранить данные любого типа в любом столбце, независимо от объявленного типа.

Комментарии