Особенности SQLite: отличия от других СУБД и типичные ошибки

---

Каждая база данных SQL имеет свои особенности, и SQLite не является исключением

Этот документ выделяет основные различия между SQLite и другими реализациями SQL — в помощь разработчикам, которые переносят приложения на SQLite или с SQLite, либо строят системы, работающие с несколькими СУБД

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

SQLite встроен, а не работает по модели клиент-сервер

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

То, что SQLite встроен и бессерверен, а не работает по модели клиент/сервер, — это особенность, а не недостаток

Клиент-серверные базы данных, такие как MySQL или PostgreSQL, решают свои задачи, а SQLite предлагает иной подход, каждый из которых подходит для своих случаев

Дополнительную информацию см. в документе «Appropriate Uses For SQLite»

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

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

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

Некоторые называют SQLite «с нестрогой типизацией», а другие базы данных — «со строгой типизацией». Но на самом деле, в SQLite применима гибкая типизация

Подробное обсуждение системы типов в SQLite см. в документе «Datatypes in SQLite»

SQLite не применяет строгую проверку типов. Например, если в столбец INTEGER вставить строку ‘1234’, она сохраняется как 1234. А если строка нечисловая, например ‘wxyz’, то она также сохранится без выдачи ошибки

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

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

Гибкая типизация — это особенность SQLite, которая может создать путаницу для тех, кто привык к строгим базам данных, но она даёт свободу разработчикам

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

В SQLite нет отдельного типа данных BOOLEAN; значения TRUE и FALSE представлены как целые числа 1 и 0. Это часто не вызывает проблем, хотя стоит помнить об этом

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

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

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

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

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

Тип данных является необязательным

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

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

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

Внешние ключи в SQLite отключены по умолчанию

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

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

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

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

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

Исключения:

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

Неагрегатные столбцы вне GROUP BY в SQLite не вызывают ошибки

В большинстве реализаций 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

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

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

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

Таблицы, необходимые для полного и корректного приведения регистра Unicode, по размеру превышают всю библиотеку SQLite

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

Строковые литералы в двойных кавычках принимаются

Стандарт 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);

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

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

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

Язык 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 используется как идентификатор имени столбца, а не как ключевое слово типа данных

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

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

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

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

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

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

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

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

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

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

SELECT 1='1';

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

SQLite неправильно обрабатывает приоритет соединений через запятую

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

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

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

A JOIN B RIGHT JOIN C JOIN D

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

A JOIN B RIGHT JOIN C JOIN D
→ (A JOIN B) RIGHT JOIN C JOIN D

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

Типичные ошибки при переносе приложений на SQLite с MySQL или PostgreSQL

На практике я замечаю, что большинство проблем при миграции на SQLite или с SQLite сводятся к нескольким повторяющимся сценариям. Вот на что стоит обратить внимание в первую очередь:

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

Забытый PRAGMA foreign_keys = ON. Поскольку принудительное применение внешних ключей отключено по умолчанию, ошибки ссылочной целостности не будут обнаружены до тех пор, пока приложение не переедет на другую СУБД. Мой совет — включать этот PRAGMA при каждом открытии соединения без исключений

Ожидание поведения AUTOINCREMENT как в MySQL. В SQLite AUTOINCREMENT и INTEGER PRIMARY KEY — разные вещи с разной семантикой. Перед использованием стоит изучить документацию SQLite AUTOINCREMENT отдельно

Строковые литералы в двойных кавычках. Разработчики, привыкшие к MySQL, нередко пишут "значение" вместо 'значение'. В SQLite это работает, но при переносе SQL-кода на другую СУБД такие запросы сломаются. На нашем опыте компиляция с -DSQLITE_DQS=0 помогает выявить проблему заранее — ещё до того, как она проявится в продакшне

NULL в PRIMARY KEY. Если таблица не объявлена как STRICT или WITHOUT ROWID, столбцы PRIMARY KEY могут принимать NULL. Добавляйте ограничение NOT NULL явно

Ответы на эти вопросы могут быть для вас полезными

Почему SQLite не выдаёт ошибку при вставке строки в числовой столбец? Потому что SQLite использует гибкую типизацию по умолчанию. Если значение не удаётся преобразовать в нужный тип, оно сохраняется как есть. Чтобы получить строгое поведение, используйте STRICT-таблицы, доступные начиная с версии 3.37.0

Как включить принудительное применение внешних ключей в SQLite? Выполните PRAGMA foreign_keys = ON; после каждого открытия соединения с базой данных. Либо скомпилируйте SQLite с параметром -DSQLITE_DEFAULT_FOREIGN_KEYS=1, чтобы включить это поведение по умолчанию

Почему SELECT 1='1' возвращает false в SQLite, но true в других базах данных? SQLite строго различает целочисленные и текстовые литералы и не выполняет неявное приведение типов при сравнении. Большинство других движков SQL выполняют такое приведение автоматически

Можно ли отключить поддержку строковых литералов в двойных кавычках? Да. Начиная с SQLite 3.29.0 это делается через sqlite3_db_config() с флагами SQLITE_DBCONFIG_DQS_DDL и SQLITE_DBCONFIG_DQS_DML, либо через параметр компиляции -DSQLITE_DQS=0. В CLI начиная с версии 3.41.0 эта возможность отключена по умолчанию

Чем AUTOINCREMENT в SQLite отличается от AUTO_INCREMENT в MySQL? В MySQL AUTO_INCREMENT просто генерирует следующее значение по счётчику. В SQLite ключевое слово AUTOINCREMENT добавляет дополнительное ограничение: значение ROWID никогда не будет повторно использовано, даже если строка с максимальным значением была удалена

Без ключевого слова AUTOINCREMENT INTEGER PRIMARY KEY в SQLite уже автоматически генерирует уникальные значения, но без этой гарантии