База SQLite и основы SQL - 7. Типы данных
14-11-2021Время чтения ~ 7 мин.SQL 6009
Затронем вопрос о типах данных, которые можно хранить в базе. Тип поля мы указываем в момент создания таблицы и считается, что чем точнее будет тип подобран к данным, тем лучше. В других СУБД типы данных играют очень важную роль, поскольку используется строгая типизация. Например тип поля 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).