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

Язык SQL — это «стандарт». И всё же никакие две системы управления базами данных SQL не работают совершенно одинаково. У каждой реализации SQL есть свои особенности и странности, и SQLite не исключение.

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

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

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 и сохраняется. Но если вставить в столбец INTEGER строку, не являющуюся числом, например 'wxyz', SQLite, в отличие от других баз данных SQL, не выдаст ошибку. Вместо этого SQLite сохранит в столбце исходное строковое значение.

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

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

Гибкая типизация — это преимущество SQLite, а не недостаток. Гибкая типизация даёт свободу. Тем не менее мы понимаем, что эта особенность иногда сбивает с толку разработчиков, привыкших работать с базами данных, где правила типов данных строже. Оглядываясь назад, можно предположить, что было бы меньше путаницы, если бы SQLite реализовала только тип данных ANY, чтобы разработчики могли явно указывать, когда им нужна гибкая типизация, вместо того чтобы делать её поведением по умолчанию. Для тех, кто ожидает жёсткой типизации, в SQLite версии 3.37.0 (2021-11-27) появилась возможность использовать таблицы STRICT. Они либо обеспечивают обязательные ограничения на типы данных, присутствующие в других системах управления базами данных 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, эти ключевые слова трактуются как идентификаторы, ссылающиеся на такие столбцы, а не как литералы BOOLEAN.

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

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

В виде строки TEXT в формате ISO-8601. Пример: '2018-04-02 12:13:46'.
В виде целого числа INTEGER, обозначающего количество секунд с 1970 года (также известного как «время Unix»).
В виде значения REAL, представляющего дробное юлианское число.

Встроенные функции даты и времени 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. Первичные ключи PRIMARY KEY иногда могут содержать NULL

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

Исключения:

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

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

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

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

В SQLite (но не в известных нам других реализациях SQL), если агрегатный запрос содержит одну функцию 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». Для этого столбца тип данных не указан, но он является PRIMARY KEY. Этот столбец не является INTEGER PRIMARY KEY таблицы, поскольку у него не указан тип данных. Токен «INTEGER» используется как идентификатор имени столбца, а не как ключевое слово типа данных.

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

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

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

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

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

Подробные инструкции о том, что делает и чего не делает AUTOINCREMENT в SQLite, приведены в документации по AUTOINCREMENT в SQLite.

12. В текстовых строках допускаются символы NUL

В SQLite символы NUL (код ASCII 0x00 и Unicode \u0000) могут находиться в середине строк. Это может приводить к неожиданному поведению. Дополнительные сведения см. в документе «Символы NUL в строках».

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

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

SELECT 1='1';

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

14. SQLite неверно определяет приоритет соединений через запятую

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

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

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

Но SQLite разбирает предложение FROM следующим образом:

Эта проблема может повлиять на результат только при использовании RIGHT OUTER JOIN или FULL OUTER JOIN в одном предложении FROM с соединениями через запятую, что на практике встречается редко. Проблему легко обойти, используя скобки в предложении FROM:

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

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