Сайт вебмастера

База SQLite и основы SQL - 7. Типы данных

14-11-2021Время чтения ~ 7 мин.SQL5035

Затронем вопрос о типах данных, которые можно хранить в базе. Тип поля мы указываем в момент создания таблицы и считается, что чем точнее будет тип подобран к данным, тем лучше. В других СУБД типы данных играют очень важную роль, поскольку используется строгая типизация. Например тип поля TINYINT будет хранить число в размере одного байта. Когда таблица создаётся, то в ней резервируется место под необходимый объём данных. Именно поэтому, если поле будет содержать числа в один байт, то очевидно, нет смысла указывать INTEGER, поскольку это зарезервирует в базе больше места для поля (4 байта).

Если вы уже знакомы с другими базами, то знаете, что обычно там предлагается довольно много разных типов данных. Например в MySQL их что-то больше 30. Такая ситуация из-за строгой типизации, когда в числовое поле нельзя будет добавить данные другого типа (кроме, конечно, совместимых).

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

В SQLite всего 5 типов данных:

  • NULL — означает что-то вроде «пусто»;
  • INTEGER — целые числа со знаком;
  • REAL — числа с запятой;
  • TEXT — любой текст;
  • BLOB — данные, которые хранятся в том виде, как были добавлены, например содержимое файла.

SQLite сама решает в каком подтипе хранить данные. Например INTEGER может занимать от 1 до 8 байт в зависимости от реального содержимого.

Есть ещё NUMERIC, но о нём отдельно.

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

CREATE TABLE tb (
   id INTEGER PRIMARY KEY AUTOINCREMENT, 
   content
);
 
INSERT INTO tb(content) VALUES (1); -- INTEGER
INSERT INTO tb(content) VALUES ('1'); -- TEXT
INSERT INTO tb(content) VALUES ('a'); -- TEXT
INSERT INTO tb(content) VALUES (1.2); -- REAL
INSERT INTO tb(content) VALUES ('1.2'); -- TEXT
INSERT INTO tb(content) VALUES (NULL); -- NULL

Для того, чтобы узнать тип данных в SQLite мы воспользуемся функцией typeof(). Да, в базе есть возможность использовать разные встроенные функции.

SELECT content, typeof(content) FROM tb;
 
content   typeof(content)
-------------------------
1         integer
1         text
a         text
1.2       real
1.2       text
NULL      null

Как видим никаких чудес — данные ровно в том виде, как и были добавлены. Если у поля не указывать тип данных, то SQLite сама определит с чем имеет дело.

Преобразование типов данных

Ситуация немного меняется, если у поля указан тип данных, но мы попытаемся добавить данные другого типа. Здесь база будет пытаться преобразовать вводимые данные в тип поля.

Тот же самый пример, только задаём поле INTEGER.

CREATE TABLE tb (
   id INTEGER PRIMARY KEY AUTOINCREMENT, 
   content INTEGER
);
 
INSERT INTO tb(content) VALUES (1); -- INTEGER
INSERT INTO tb(content) VALUES ('1'); -- TEXT
INSERT INTO tb(content) VALUES ('a'); -- TEXT
INSERT INTO tb(content) VALUES (1.2); -- REAL
INSERT INTO tb(content) VALUES ('1.2'); -- TEXT
INSERT INTO tb(content) VALUES (NULL); -- NULL
 
SELECT content, typeof(content) FROM tb;
 
content   typeof(content)
-------------------------
1         integer
1         integer
a         text
1.2       real
1.2       real
NULL      null

То есть когда указан тип поля, база воспринимает это как инструкцию по преобразованию входящих данных в этот тип. Если прямого преобразования не получается, то будет попытка привести его к ближайшему подходящему. Именно так работает динамическая типизация в PHP или JavaScript. Здесь такая же ситуация.

Казалось бы радужная картина, но мы все равно стараемся всегда указывать тип данных поля. И главная причина в том, что в SQL-запросах мы часто используем сравнение (WHERE или ON), которое будет корректно работать только с единым типом данных. Потому, что если сравнивать число 3 со строкой «3», то неизвестно какой будет результат сравнения.

Чтобы избежать такой неопределенности, мы и задаём тип данных.

Тип NULL

Тип NULL есть и в других базах — означает «пусто». С NULL нужно просто запомнить несколько правил, чтобы потом не ошибаться.

В первую очередь NULL не равен 0, пустой строке или false. NULL не равен вообще ничему. Более того один NULL не равен другому NULL.

В базах, если поле содержит NULL, то говорят, что это пустое значение.

Чтобы работать с NULL в условиях используют команду IS NULL.

SELECT content, typeof(content) 
FROM tb 
WHERE content IS NULL;
 
content   typeof(content)
-------------------------
NULL      null

Противоположная команда — IS NOT NULL, наоборот проверяет условие «если не NULL»:

SELECT content, typeof(content) 
FROM tb 
WHERE content IS NOT NULL;
 
content   typeof(content)
-------------------------
1         integer
1         integer
a         text
1.2       real
1.2       real

На практике значения NULL лучше всего избегать, поскольку его неопределённость может создавать трудности в анализе полученных данных. Поэтому, когда создаётся таблица, часто можно встретить инструкцию NOT NULL, которая фактически запрещает создание NULL-значений. Вместо этого используется какое-то дефолтное значение. Например для INTEGER это может быть 0, а для текста пустая строка «''»:

CREATE TABLE tb1 (
   id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, 
   i INTEGER NOT NULL DEFAULT 0,
   t TEXT NOT NULL DEFAULT '',
   r REAL NOT NULL DEFAULT 0.1
);

В таком виде данные будут максимально приближены к тому, что мы хотим получить в итоговой выборке.

Тип INTEGER

Целые числа могут быть со знаком и без, а также быть малыми и большими. В зависимости от реального содержимого, SQLite сама решит сколько байт выделить под хранение числа: 1, 2, 3, 4, 6 или 8 байт. То есть максимальное число будет состоять из 20 цифр. В общем хватит для большинства задач.

Тип REAL

Это десятичные дроби или числа с запятой. SQLite такие числа хранит в 8 байтах с плавающей запятой. Наверное тоже хватит для большинства задач. Если требуется большая точность, то такое число наверное стоит хранить как TEXT, чтобы потом использовать в функциях с повышенной точностью, например BC Math в PHP (они как раз работают со строками).

Тип TEXT

Текст хранится в кодировке UTF-8, UTF-16BE или UTF-16LE. То есть мы вообще не задумываемся о кодировках и прочих преобразованиях.

Кстати именно это и является причиной, почему не работает регистронезависимый поиск LIKE.

Что касается занимаемого места, то оно зависит от реального содержимого. Нам не нужно об этом задумываться.

Тип BLOB

Даже не знаю, лично я с ним никогда не работал и никому не посоветую. Такое поле хранит ровно то, что в него добавлено, и часто это используется для хранения содержимого файлов. Например у вас есть какой-то файл, который вы не хотите хранить на диске в открытом виде. Он загружается в BLOB-поле и, когда он запрашивается, то извлекается в виде строки и отправляется в поток вывода.

На практике хранение файлов в базе очень сильно её раздувает в размере, поэтому намного проще ограничить права доступа на сервере, чем организовывать хранение в базе.

Как хранить булево значение

В SQLite нет булевского типа данных, поэтому предлагается использовать обычный INTEGER, где 1 — это true, а 0 — false. Это по сути программистский тип BOOL. Просто и сердито.

Как хранить дату

Отсутствия типа для хранения даты, вначале немного обескураживает, особенно, если вы знакомы с другими СУБД. Но потом понимаешь, что любая дата это не что иное, как метка времени и у неё есть только три формата:

  • строка вида YYYY-MM-DD HH:MM:SS.SSS (стандарт ISO8601) — тип TEXT
  • целое число в Unix Time — (число секунд с 1970 года) — тип INTEGER
  • дробное число — юлианские дни (там используются дробь, как часть суток) — тип REAL

То есть в зависимости от задачи, выбирается подходящий тип поля и там дата хранится в нужном формате.

Что интересно, функции PHP рассчитаны точно на такие же метки времени.

Кроме всего этого, в SQLite есть несколько функций для работы с датами.

SELECT date('now'); -- текущая дата
SELECT time('now'); -- текущее время
SELECT datetime('now'); -- текущие дата и время
SELECT julianday('now'); -- текущий юлианский день

Я наверное просто дам ссылку на официальную документацию, где приведены все функции, их описание и примеры использования.

Если дата не используется явно в условиях sql-запросов, то её удобно хранить в виде числа Unix Time. Если же дата где-то выводится, то лучше выбрать строковое представление в полном формате, включая часовой пояс. Либо в виде «Y-m-d H:i:s» для UTC. Тогда преобразовать любую дату к нужному часовому поясу достаточно просто с помощью php-функций вроде strtotime().

Совместимость типов данных

Поскольку SQLite имеет немного типов данных, то возникает вопрос о том, как перенести sql-запрос из других СУБД, где типов намного больше. В этом плане SQLite достаточно сообразительна и автоматом позволяет использовать любой вариант, который будет автоматом преобразован в наиболее подходящий. Есть такая табличка совместимости:

  • INTEGER — это INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8
  • TEXT — это CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT * CLOB
  • REAL — это REAL DOUBLE DOUBLE PRECISION * FLOAT
  • BLOB — так и будет BLOB
  • NUMERIC — это NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME

Тип данных NUMERIC можно считать универсальным типом, поскольку может преобразоваться к любому из 5 типов данных. Если это будет строка, но в виде корректного числа, то она будет преобразована в INTEGER. Если это строка, где десятичное число, то будет преобразование в REAL. Но при этом, если после преобразования получится целое число, то это будет уже INTEGER. Типы NULL и BLOB остаются без изменений. Остальное будет преобразовано в TEXT (без ограничений длины).

Из-за таких особенностей не стоит использовать NUMERIC в своих запросах, лучше сразу указывать подходящий тип.

Если вы пишите sql-запросы для разных СУБД, то в общем-то нет сложностей использовать подходящий тип для другой базы. Например для даты можно использовать DATETIME и хранить там форматированную строку даты. SQLite автоматом преобразует такой тип к TEXT (через NUMERIC).

Похожие записи
© MAXSITE.ORG, 2006-2024 Сайт использует cookies 0.165s/2.55Mb ЗРОБЛЕНО В УКРАЇНІ