Типы данных в SQLite: полное руководство по классам хранения и аффинитивности

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

Принцип работы типов в SQLite

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

Тем не менее при создании столбцов в таблицах SQLite можно указывать типы данных. Это служит подсказкой для базы данных о том, какой тип данных следует ожидать.

При этом SQLite может хранить данные любого типа в любом столбце, независимо от объявленного типа.

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

В SQLite каждое отдельное значение (а не столбец таблицы) принадлежит к одному из пяти классов хранения (Storage Class). Это физический формат хранения данных в файле БД:

1. NULL: Значение NULL.
2. INTEGER: Целое число со знаком.
3. REAL: Число с плавающей запятой.
4. TEXT: Текстовая строка.
5. BLOB: Последовательность байтов (бинарные данные), хранимая "как есть".

Аффинитивность типов

Аффинитивность типа — это не свойство значения, а свойство столбца. Это рекомендация для SQLite о том, в каком формате предпочтительнее хранить данные в этом столбце.

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

SQLite определяет пять видов аффинитивности столбца:

1. TEXT: Данные в столбце хранятся в текстовом формате.
2. NUMERIC: Столбец предпочитает числовое хранение (INTEGER или REAL), но может принять и TEXT.
3. INTEGER: Ориентирован на хранение целых чисел.
4. REAL: Столбец предпочитает хранение в виде числа с плавающей запятой.
5. BLOB: Для значений в этом столбце преобразования не производятся.

При создании столбца можно указать аффинитивность с типом, чтобы дать 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

При вставке или обновлении значения SQLite применяет следующие правила преобразования, основанные на аффинитивности целевого столбца:

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

Эти правила применяются последовательно и определяют, как исходный класс хранения значения может измениться перед сохранением в столбце.

В таблице ниже показано, как SQLite обрабатывает разные типы значений при вставке в столбец с аффинитивностью INTEGER:

Ключевой вывод: Аффинитивность INTEGER стремится к целочисленному хранению, но в случае неудачи преобразования в целое число, значение сохраняется как REAL. Это ключевое отличие аффинитивности INTEGER от NUMERIC, которая может откатиться и к TEXT.

Сравнение классов хранения и аффинитивности:

Как SQLite определяет аффинитивность по имени типа

Хотя у SQLite всего пять видов аффинитивности, при объявлении типа столбца можно использовать множество имён-псевдонимов. SQLite определяет аффинитивность, сопоставляя название типа с шаблонами:

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

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

В отличие от других СУБД, SQLite не имеет встроенных типов DATE,BOOLEAN и подобных. Однако вы можете хранить такие данные, используя один из пяти классов хранения.

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

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

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

Хотя в SQLite нет отдельного типа DATE, даты можно хранить, используя один из классов хранения: TEXT (формат ISO-8601), INTEGER (временная метка Unix) или REAL (число дней юлианского календаря).

Как правило, для дат лучше использовать человекочитаемый формат 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.

Сравнение типов данных SQLite с другими СУБД

Чтобы лучше понять уникальность подхода SQLite, сравним его систему типов с двумя популярными СУБД:

Ключевые выводы из сравнения

1. SQLite — максимально гибкий: Один столбец может хранить разные типы данных, что упрощает прототипирование
2. MySQL — баланс гибкости и структуры: Поддерживает много типов, но сохраняет строгость объявлений
3. PostgreSQL — максимальная строгость и богатство: Самая развитая система типов с дополнительными возможностями (массивы, JSONB, пользовательские типы)

Практические аспекты производительности

Сравнение подходов к типам данных напрямую влияет на производительность в разных сценариях:

Ключевые выводы о производительности

1. SQLite выигрывает в гибкости и простоте: Меньше проверок типов = быстрее простые операции
2. MySQL и PostgreSQL выигрывают в сложных сценариях: Предсказуемость типов = лучшая оптимизация запросов
3. Для смешанных данных SQLite вне конкуренции: Только SQLite позволяет хранить числа, текст и даты в одном столбце без преобразований
4. Специализированные операции быстрее со статической типизацией: Сортировка, поиск по датам, индексация

Рекомендации по выбору на основе производительности

Когда что выбирать

Заключение

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

Рекомендации по выбору аффинитивности типа:

Часто задаваемые вопросы

❓ В чём главное отличие SQLite от других СУБД в работе с типами данных?

SQLite использует динамическую типизацию, тогда как большинство других СУБД (MySQL, PostgreSQL) используют статическую типизацию. Это означает, что в SQLite тип данных привязан к значению, а не к столбцу.

❓ Может ли это привести к проблемам с данными?

Да, если не быть внимательным. Например, если хранить текстовые значения в столбце с аффинитивностью INTEGER, могут возникнуть проблемы при математических операциях. Рекомендуется следовать объявленным типам для предотвращения ошибок.

❓ Как правильно выбрать аффинитивность для столбца?

• Используйте TEXT для строк и дат в формате ISO-8601
• Используйте INTEGER для целых чисел и временных меток Unix
• Используйте REAL для дробных чисел и дат в формате юлианских дней
• Используйте BLOB для бинарных данных, которые не должны преобразовываться

❓ Почему SQLite сохраняет unknown в REAL столбце?

Это демонстрирует гибкость SQLite: хотя столбец объявлен как REAL, SQLite позволяет хранить там любой класс хранения. На практике так делать не рекомендуется.

❓ Есть ли разница между BIGINT, INT и INTEGER в SQLite?

Нет, нет разницы. Все эти объявления дают столбцу аффинитивность INTEGER. SQLite использует сопоставление по шаблону: любое имя типа, содержащее "INT", получает аффинитивность INTEGER.

❓ Как лучше хранить булевы значения в SQLite?

Рекомендуется использовать INTEGER с значениями 0 (false) и 1 (true). Или TEXT со значениями TRUE/FALSE, если важна читаемость.

❓ Можно ли изменить аффинитивность существующего столбца?

Прямо — нет. Нужно создать новую таблицу с нужной структурой, скопировать данные и удалить старую таблицу. Или использовать ALTER TABLE ... RENAME COLUMN (в новых версиях SQLite).

❓ Влияет ли аффинитивность на производительность?

Косвенно — да. Правильная аффинитивность позволяет SQLite эффективнее использовать индексы и выполнять операции. Например, сравнение чисел в INTEGER столбце быстрее, чем в TEXT.