1. Обзор

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

Этот документ стремится подчеркнуть основные различия между SQLite и другими внедрениями SQL как помощь разработчикам, которые портируют к или от 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, должны ясно понять это различие.

См. подробности здесь.

3. Гибкие типы

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

Некоторые комментаторы говорят, что SQLite "со слабым контролем типов" и что другие базы данных SQL "со строгим контролем типов". Мы полагаем, что эти условия неточны. Мы предпочитаем говорить, что SQLite "гибко типизировано" и что другие базы данных SQL типизированы строго.

См. здесь подробности.

Ключевой пункт то, что 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 version 3.37.0 (2021-11-27) ввел опцию таблицы STRICT. Они налагают обязательные ограничения типа данных, найденные в других системах базы данных SQL, или позволяют явному типу данных ANY сохранять гибкую типизацию SQLite.

3.1. Нет отдельного типа BOOLEAN

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

С SQLite version 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 time").
• Как значение REAL, которое является фракционным Julian day number .

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

3.3. Тип данных дополнительный

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

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

У таблицы "t1" есть четыре колонки "a", "b", "c" и "d", у которых нет конкретного назначенного типа данных. Можно сохранить что-либо, что вы хотите, в любой из тех колонок.

4. Осуществление внешнего ключа выключено по умолчанию

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

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

5. PRIMARY KEY могут иногда содержать NULL

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

Значение столбца INTEGER PRIMARY KEY должно всегда быть целым числом не-NULL. Колонки PRIMARY KEY таблиц WITHOUT ROWID также должны быть не NULL.

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

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

SQLite не проводит в жизнь это ограничение. Колонки вывода агрегатного запроса могут быть произвольными выражениями, которые включают колонки, не найденные в пункте GROUP BY. У этой особенности есть два использования:

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

   Например, найти самого высокооплачиваемого сотрудника:

   SELECT max(salary), first_name, last_name FROM employee;
   

   В запросе выше значения для колонок first_name и last_name будут соответствовать строке, которая удовлетворила условие max(salary).

2. Если запрос не содержит агрегатных функций вообще, то пункт GROUP BY может быть добавлен вместо DISTINCT ON. Другими словами, строки вывода фильтрованы так, чтобы только одну строку показали для каждого отличного набора значений в пункте GROUP BY. Если у двух или больше строк вывода иначе был бы тот же самый набор значений для колонок GROUP BY, то одна из строк выбрана произвольно. SQLite поддерживает DISTINCT, но не DISTINCT ON, функциональность которого обеспечивается вместо этого GROUP BY.

7. Не делает полной свертки регистра в Unicode по умолчанию

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

1. Хотя стабильный теперь, когда SQLite был сначала разработан, правила для сворачивания регистра unicode были все еще в движении. Это означает, что поведение, возможно, изменилось с каждым новым выпуском unicode, разрушив запросы и портя индексы в процессе.
2. Таблицы, необходимые, чтобы сделать полное и надлежащее сворачивание 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 (который был одним из наиболее широко используемых RDBMS, когда 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 в to 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 и SQLTIE_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 стремилось следовать Postel's Law , в котором говорится частично "Быть либеральным в том, что вы принимаете". Это раньше считалось хорошим дизайном, что система примет изворотливые входы и попытается сделать лучшее, что можно, не жалуясь слишком много. Но в последнее время, люди сообразили, что иногда лучше быть строгим в том, что вы принимаете, чтобы более легко найти ошибки во входе.

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

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

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

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

NUL-символы (код ASCII 0x00 и Unicode \u0000) могут появиться посреди последовательностей в SQLite. Это может привести к неожиданному поведению. Посмотрите подробности здесь.

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

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

SELECT 1='1';

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