Особенности, нюансы и подводные камни SQLite

Язык SQL является «стандартом». Однако никакие два SQL-движка баз данных не работают совершенно одинаково. Каждая реализация SQL имеет свои особенности и странности, и SQLite не является исключением из этого правила.

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

Если вы, как пользователь SQLite, столкнулись с какой-либо особенностью SQLite, которая не указана здесь, сообщите об этом разработчикам, оставив краткое сообщение на форуме SQLite SQLite Forum.

2. SQLite — встраиваемая, а не клиент-серверная система

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

То, что SQLite является встраиваемой и бессерверной, а не клиент-серверной системой, — это особенность, а не ошибка.

Клиент-серверные базы данных, такие как MySQL, PostgreSQL, SQL Server, Oracle и другие, являются важной частью современных систем. Эти системы решают важную задачу. Но SQLite решает другую. Как SQLite, так и клиент-серверные базы данных имеют своё предназначение. Разработчики, которые сравнивают SQLite с другими SQL-движками баз данных, должны чётко понимать это различие.

Дополнительную информацию см. в документе Рекомендуемые сценарии использования SQLite.

3. Гибкая типизация

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

Некоторые утверждают, что SQLite имеет «слабую типизацию», а другие SQL-базы данных имеют «сильную типизацию». Мы считаем эти термины неточными и даже пренебрежительными. Мы предпочитаем говорить, что SQLite имеет «гибкую типизацию», а другие SQL-базы данных имеют «жёсткую типизацию».

Подробное обсуждение системы типов в SQLite см. в документе Типы данных в SQLite.

Суть в том, что SQLite очень терпимо относится к типу данных, которые вы вставляете в базу данных. Например, если столбец имеет тип «INTEGER», а приложение вставляет строковое значение, SQLite попытается преобразовать эту строку в целое число, как и любой другой SQL-движок. Таким образом, если вы вставите '1234' в столбец INTEGER, это значение будет преобразовано в целое число 1234 и сохранено. Однако, если вы вставите нечисловую строку, например, 'wxyz', в столбец INTEGER, в отличие от других SQL-баз данных, SQLite не выдаст ошибку. Вместо этого SQLite сохранит фактическое строковое значение в столбце.

Аналогичным образом, SQLite позволяет хранить строку длиной 2000 символов в столбце типа VARCHAR(50). Другие реализации SQL либо выдадут ошибку, либо обрезат строку. SQLite сохранит всю строку длиной 2000 символов без потерь и без жалоб.

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

Гибкая типизация — это особенность SQLite, а не ошибка. Гибкая типизация — это свобода. Тем не менее, мы понимаем, что эта особенность иногда вызывает путаницу у разработчиков, привыкших к работе с другими базами данных, которые более строго следуют правилам типов данных. В ретроспективе, возможно, было бы менее запутанно, если бы SQLite реализовал тип данных ANY, чтобы разработчики могли явно указать, когда они хотят использовать гибкую типизацию, вместо того, чтобы делать её по умолчанию. Как компромисс для тех, кто ожидает жёсткой типизации, в версии SQLite 3.37.0 (2021-11-27) появилась опция строгих таблиц. Эти таблицы либо накладывают обязательные ограничения типов данных, как в других SQL-движках баз данных, либо позволяют использовать явный тип данных ANY для сохранения гибкой типизации SQLite.

3.1. Отсутствие отдельного типа данных BOOLEAN

В отличие от большинства других реализаций SQL, в SQLite нет отдельного типа данных BOOLEAN. Вместо этого TRUE и FALSE (обычно) представляются целыми числами 1 и 0 соответственно. Это не кажется большой проблемой, так как жалоб на это мало. Но это важно отметить.

Начиная с версии SQLite 3.23.0 (2018-04-02), SQLite также распознаёт ключевые слова TRUE и FALSE как псевдонимы для целых чисел 1 и 0 соответственно. Это обеспечивает лучшую совместимость с другими реализациями SQL. Но для обратной совместимости, если есть столбцы с именами TRUE или FALSE, то ключевые слова рассматриваются как идентификаторы, ссылающиеся на эти столбцы, а не на булевы литералы.

3.2. Отсутствие отдельного типа данных DATETIME

В SQLite нет типа данных DATETIME. Вместо этого даты и время можно хранить следующими способами:

В виде текстовой строки в формате ISO-8601. Пример: '2018-04-02 12:13:46'.
В виде целого числа — количество секунд с 1970 года (также известного как «unix time»).
В виде вещественного числа — дробное значение числа Джулианского дня.

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

3.3. Тип данных — необязателен

Поскольку SQLite гибкий и терпимый в отношении типов данных, можно создавать столбцы таблиц без указанного типа. Например:

CREATE TABLE t1(a,b,c,d);

Таблица "t1" содержит четыре столбца "a", "b", "c" и "d", которым не присвоен конкретный тип данных. Вы можете хранить в этих столбцах что угодно.

4. Принуждение к соблюдению внешних ключей отключено по умолчанию

SQLite уже давно анализирует ограничения внешних ключей, но возможность фактического принуждения к соблюдению этих ограничений появилась значительно позже, с версией 3.6.19 (2009-10-14). К тому времени, когда была добавлена поддержка принуждения к соблюдению ограничений внешних ключей, существовали миллионы баз данных, содержащих ограничения внешних ключей, некоторые из которых были некорректны. Чтобы избежать разрыва совместимости со старыми базами данных, принуждение к соблюдению ограничений внешних ключей отключено по умолчанию в SQLite.

Приложения могут активировать принуждение к соблюдению внешних ключей во время выполнения с помощью оператора PRAGMA foreign_keys. Или принуждение к соблюдению внешних ключей можно активировать на этапе компиляции, используя опцию компиляции -DSQLITE_DEFAULT_FOREIGN_KEYS=1.

5. Первичные ключи иногда могут содержать NULL

Первичный ключ в таблице SQLite обычно является просто уникальным ограничением. Из-за исторической ошибки значения столбцов первичного ключа могут быть NULL. Это ошибка, но к тому времени, когда проблема была обнаружена, уже существовало так много баз данных, которые зависели от этой ошибки, что было принято решение поддерживать это некорректное поведение в дальнейшем. Вы можете обойти эту проблему, добавив ограничение NOT NULL к каждому столбцу первичного ключа.

Исключения:

Значение столбца INTEGER PRIMARY KEY всегда должно быть целочисленным значением, отличным от NULL, потому что INTEGER PRIMARY KEY — это псевдоним для ROWID. Если вы попытаетесь вставить NULL в столбец INTEGER PRIMARY KEY, SQLite автоматически преобразует NULL в уникальное целое число.
Функции WITHOUT ROWID и STRICT были добавлены после обнаружения этой ошибки, и поэтому таблицы WITHOUT ROWID и STRICT работают корректно: они запрещают NULL в первичном ключе.

6. Агрегированные запросы могут содержать столбцы результатов, не являющиеся агрегированными, которые не указаны в условии GROUP BY

В большинстве реализаций SQL столбцы вывода агрегированного запроса могут ссылаться только на агрегированные функции или столбцы, указанные в условии GROUP BY. Ссылка на обычный столбец в агрегированном запросе не имеет смысла, поскольку каждая строка вывода может быть составлена из двух или более строк в исходной таблице(ах).

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

В SQLite (но не в любой другой реализации SQL, о которой нам известно), если агрегированный запрос содержит единственную функцию min() или max(), то значения столбцов, используемых в выводе, берутся из строки, где было достигнуто значение min() или max(). Если две или более строки имеют одинаковое значение min() или max(), то значения столбцов будут произвольно выбраны из одной из этих строк.

Например, чтобы найти сотрудника с самой высокой зарплатой:
```
SELECT max(salary), first_name, last_name FROM employee;
```
В запросе выше значения для столбцов first_name и last_name будут соответствовать строке, которая удовлетворяла условию max(salary).
Если запрос вообще не содержит агрегатных функций, то к предложению GROUP BY можно добавить предложение в качестве замены для предложения DISTINCT ON. Другими словами, строки вывода фильтруются таким образом, что для каждого набора различных значений в предложении GROUP BY отображается только одна строка. Если две или более строки вывода в противном случае имели бы одинаковый набор значений для столбцов GROUP BY, то одна из строк выбирается произвольно. (SQLite поддерживает DISTINCT, но не DISTINCT ON, чья функциональность обеспечивается вместо этого предложением GROUP BY.)

7. SQLite не выполняет полное сведение Unicode-регистров по умолчанию

SQLite не знает о разнице между заглавными и строчными буквами для всех символов Unicode. Функции SQL, такие как upper() и lower(), работают только с ASCII-символами. Причин для этого две:

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

Полное сведение регистров Unicode поддерживается в SQLite, если оно скомпилировано с опцией -DSQLITE_ENABLE_ICU и связано с библиотекой International Components for Unicode.

8. Поддерживаются строковые литералы в двойных кавычках

Стандарт SQL требует двойных кавычек вокруг идентификаторов и одинарных кавычек вокруг строковых литералов. Например:

"this is a legal SQL column name"
'this is an SQL string literal'

SQLite принимает оба варианта. Но в попытке совместимости с MySQL 3.x (который был одним из наиболее широко используемых СУБД при разработке SQLite) SQLite также интерпретирует строку в двойных кавычках как строковый литерал, если она не соответствует какому-либо допустимому идентификатору.

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

Оглядываясь назад, мы не должны были пытаться заставить SQLite принимать синтаксис MySQL 3.x и никогда не должны были разрешать строковые литералы в двойных кавычках. Однако существует бесчисленное множество приложений, использующих строковые литералы в двойных кавычках, и поэтому мы продолжаем поддерживать эту возможность, чтобы избежать разрыва совместимости со старыми версиями.

Начиная с SQLite 3.27.0 (2019-02-07), использование строкового литерала в двойных кавычках приводит к отправке сообщения об ошибке в журнал ошибок.

Начиная с SQLite 3.29.0 (2019-07-10), использование строковых литералов в двойных кавычках можно отключить во время выполнения, используя действия SQLITE_DBCONFIG_DQS_DDL и SQLITE_DBCONFIG_DQS_DML для sqlite3_db_config(). Значения по умолчанию можно изменить во время компиляции, используя опцию компиляции -DSQLITE_DQS=N. Разработчикам приложений рекомендуется компилировать с -DSQLITE_DQS=0 для отключения недоработки со строковыми литералами в двойных кавычках по умолчанию. Если это невозможно, отключите строковые литералы в двойных кавычках для отдельных баз данных, используя такой C-код:

sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DDL, 0, (void*)0);
sqlite3_db_config(db, SQLITE_DBCONFIG_DQS_DML, 0, (void*)0);

Или, если строковые литералы в двойных кавычках отключены по умолчанию, но нужно выборочно включить их для некоторых исторических баз данных, это можно сделать, используя тот же C-код, что показано выше, за исключением того, что третий параметр изменен с 0 на 1.

Начиная с SQLite 3.41.0 (2023-02-21), SQLITE_DBCONFIG_DQS_DDL и SQLITE_DBCONFIG_DQS_DML по умолчанию отключены в CLI. Используйте командную точку ".dbconfig", чтобы повторно включить устаревшее поведение, если это необходимо.

9. Ключевые слова часто могут использоваться как идентификаторы

Язык SQL богат ключевыми словами. Большинство реализаций SQL не позволяют использовать ключевые слова в качестве идентификаторов (наименований таблиц или столбцов), если они не заключены в двойные кавычки. Но SQLite более гибкий. Многие ключевые слова могут использоваться как идентификаторы без необходимости в кавычках, если эти ключевые слова используются в контексте, где ясно, что они предназначены для идентификатора.

Например, следующее утверждение допустимо в SQLite:

CREATE TABLE union(true INT, with BOOLEAN);

То же самое утверждение SQL завершится ошибкой во всех других реализациях SQL, о которых нам известно, из-за использования ключевых слов «union», «true» и «with» в качестве идентификаторов.

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

CREATE TRIGGER AFTER INSERT ON tableX BEGIN
  INSERT INTO tableY(b) VALUES(new.a);
END;

Триггер, созданный предыдущим утверждением, называется «AFTER», и это триггер «BEFORE». Токен «AFTER» используется как идентификатор, а не как ключевое слово, поскольку это единственный способ разобрать утверждение. Еще один пример:

CREATE TABLE tableZ(INTEGER PRIMARY KEY);

Таблица tableZ имеет один столбец под названием «INTEGER». Для этого столбца не указан тип данных, но это является первичным ключом. Столбец не является целым первичным ключом для таблицы, потому что для него не указан тип данных. Токен «INTEGER» используется как идентификатор для имени столбца, а не как ключевое слово типа данных.

10. Сомнительный SQL разрешен без ошибок или предупреждений

Первоначальная реализация SQLite стремилась следовать закону Постеля, который частично гласит: «Будьте либеральны в том, что вы принимаете». Это раньше считалось хорошим дизайном — система принимала сомнительный ввод и пыталась сделать все возможное, не жалуясь слишком сильно. Но в последнее время люди начали понимать, что иногда лучше быть строже в том, что вы принимаете, чтобы легче находить ошибки в вводе.

11. AUTOINCREMENT работает не так, как в MySQL

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

См. документацию SQLite AUTOINCREMENT для подробных инструкций о том, что делает и не делает функция AUTOINCREMENT в SQLite.

12. Символы NUL разрешены в строках текста

Символы NUL (ASCII код 0x00 и Unicode \u0000) могут появляться посередине строк в SQLite. Это может привести к неожиданному поведению. См. документ "Символы NUL в строках" для получения дополнительной информации.

13. SQLite различает целочисленные и строковые литералы

SQLite говорит, что следующий запрос возвращает ложь:

SELECT 1='1';

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

14. SQLite неправильно определяет приоритет объединений с запятой

SQLite придает всем операторам объединения равный приоритет и обрабатывает их слева направо. Но это не совсем правильно. Должно быть, что объединения с запятой имеют более низкий приоритет, чем все другие операторы объединения. Другими словами, предложение FROM вроде этого:

... FROM a, b RIGHT JOIN c, d ...

Должно быть обработано следующим образом:

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

Проблема может повлиять на результат только при использовании правого внешнего объединения или полного внешнего объединения в одном операторе FROM с запятыми, что в практике случается редко. И эту проблему легко решить с помощью скобок в операторе FROM:

... FROM a, (b RIGHT JOIN c), d ...

Эта страница была изменена в последний раз 14 августа 2024 г. в 17:04:32 по UTC

SQLite is in the Public Domain.
https://sqlite.org/quirks.html