Значения строк в SQLite: синтаксис и примеры запросов

«Значение» — это одно число, строка, BLOB или NULL. Иногда используется уточнённое название «скалярное значение» (scalar value), чтобы подчеркнуть, что речь идёт только об одной величине

«Значение строки» (row value) — это упорядоченный список из двух или более скалярных значений. Иными словами, значение строки — это вектор или кортеж

«Размер» значения строки — это количество скалярных значений, которые оно содержит. Размер значения строки всегда не менее 2. Значение строки с одним столбцом — это просто скалярное значение. Значение строки без столбцов является синтаксической ошибкой

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

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

Синтаксис

SQLite позволяет выражать значения строк двумя способами:

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

SQLite может использовать значения строк в двух контекстах:

• Два значения строк одинакового размера можно сравнивать с помощью операторов <, <=, >, >=, =, <>, IS, IS NOT, IN, NOT IN, BETWEEN или CASE.
• В операторе UPDATE список имён столбцов можно задать равным значению строки того же размера.

Синтаксис значений строк и обстоятельства, при которых они могут использоваться, проиллюстрированы в примерах ниже

Сравнение значений строк

Два значения строк сравниваются путём последовательного просмотра составляющих скалярных значений слева направо. NULL означает «неизвестно». Общий результат сравнения равен NULL, если возможно сделать результат как истинным, так и ложным, подставив альтернативные значения вместо составляющих NULL. Следующий запрос демонстрирует некоторые сравнения значений строк:

SELECT (1,2,3) = (1,2,3),    -- 1
       (1,2,3) = (1,NULL,3), -- NULL
       (1,2,3) = (1,NULL,4), -- 0
       (1,2,3) < (2,3,4),    -- 1
       (1,2,3) < (1,2,4),    -- 1
       (1,2,3) < (1,3,NULL), -- 1
       (1,2,3) < (1,2,NULL), -- NULL
       (1,3,5) < (1,2,NULL), -- 0
       (1,2,NULL) IS (1,2,NULL); -- 1

Результат (1,2,3)=(1,NULL,3) равен NULL, потому что результат может оказаться истинным, если заменить NULL→2, или ложным, если заменить NULL→9. Результат (1,2,3)=(1,NULL,4) не равен NULL, потому что не существует такой подстановки составляющего NULL, которая сделала бы выражение истинным: 3 никогда не будет равно 4 в третьем столбце

Любое из значений строк в предыдущем примере можно заменить подзапросом, возвращающим три столбца, и результат будет тем же

Операторы IN для значений строк

Для оператора IN со значением строки левая часть (далее LHS, от left-hand side) может быть либо заключённым в скобки списком значений, либо подзапросом с несколькими столбцами. Правая часть (далее RHS, от right-hand side) должна быть выражением подзапроса

Значения строк в операторах UPDATE

Значения строк также можно использовать в предложении SET оператора UPDATE. LHS должна быть списком имён столбцов. RHS может быть любым значением строки. Это открывает удобный способ обновлять сразу несколько столбцов результатом одного подзапроса, не дублируя соединение

Примеры запросов SQLite со значениями строк

Пример запроса SQLite с прокручиваемым окном (keyset pagination)

Предположим, приложение хочет отображать список контактов в алфавитном порядке по фамилии и имени в прокручиваемом списке (keyset pagination), который может показывать только 7 контактов одновременно. Инициализировать прокручиваемый список первыми 7 записями несложно:

SELECT * FROM contacts ORDER BY lastname, firstname LIMIT 7;

Когда пользователь прокручивает вниз, приложению нужно найти второй набор из 7 записей. Один из способов сделать это — использовать предложение OFFSET:

SELECT * FROM contacts ORDER BY lastname, firstname LIMIT 7 OFFSET 7;

OFFSET даёт правильный ответ. Однако OFFSET требует времени, пропорционального значению смещения. На самом деле при LIMIT x OFFSET y SQLite вычисляет запрос как LIMIT x+y и отбрасывает первые y значений, не возвращая их приложению. Поэтому по мере прокрутки окна к концу длинного списка и увеличения значения y последовательные вычисления смещения занимают всё больше и больше времени

Более эффективный подход — запомнить последнюю отображаемую запись, а затем использовать сравнение значений строк в предложении WHERE:

SELECT * FROM contacts
 WHERE (lastname,firstname) > (?1,?2)
 ORDER BY lastname, firstname
 LIMIT 7;

Если фамилия и имя из нижней строки предыдущего экрана привязаны к ?1 и ?2, то приведённый выше запрос вычисляет следующие 7 строк. И при наличии подходящего индекса делает это очень эффективно — значительно эффективнее, чем OFFSET. Я считаю этот паттерн одним из наиболее практичных применений значений строк в реальных приложениях

Сравнение дат, хранящихся в отдельных полях

Обычный способ хранения даты в таблице базы данных — в одном поле: в виде unix-метки времени, номера юлианского дня или строки даты ISO-8601. Но некоторые приложения хранят даты в трёх отдельных полях для года, месяца и дня:

CREATE TABLE info(
  year  INT,        -- 4-значный год
  month INT,        -- от 1 до 12
  day   INT,        -- от 1 до 31
  other_stuff BLOB  -- и так далее
);

Когда даты хранятся таким образом, сравнение значений строк предоставляет удобный способ их сопоставлять. Вместо громоздкого условия с тремя отдельными сравнениями можно записать компактное выражение вида (year, month, day) > (?1, ?2, ?3), которое читается так же естественно, как обычное сравнение дат

Мой совет — при проектировании схемы с раздельными полями даты сразу закладывать составной индекс по (year, month, day), чтобы такие сравнения работали эффективно

Пример запроса SQLite по составному ключу

Предположим, нам нужно узнать номер заказа, номер продукта и количество для любого элемента, в котором номер продукта и количество совпадают с номером продукта и количеством любого элемента в заказе номер 365:

SELECT ordid, prodid, qty
  FROM item
 WHERE (prodid, qty) IN
       (SELECT prodid, qty FROM item WHERE ordid = 365);

Приведённый выше запрос можно переписать в виде соединения и без использования значений строк:

SELECT t1.ordid, t1.prodid, t1.qty
  FROM item AS t1, item AS t2
 WHERE t1.prodid=t2.prodid
   AND t1.qty=t2.qty
   AND t2.ordid=365;

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

Даже в форме JOIN запрос можно сделать понятнее с помощью значений строк:

SELECT t1.ordid, t1.prodid, t1.qty
  FROM item AS t1, item AS t2
 WHERE (t1.prodid,t1.qty) = (t2.prodid,t2.qty)
   AND t2.ordid=365;

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

Обновление нескольких столбцов таблицы на основе запроса

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

В системе полнотекстового поиска Fossil документы, участвующие в полнотекстовом поиске (вики-страницы, тикеты, коммиты, файлы документации и т. д.), отслеживаются таблицей ftsdocs (от full text search documents — документы полнотекстового поиска). По мере добавления новых документов в репозиторий они не индексируются сразу. Индексирование откладывается до поступления поискового запроса

Таблица ftsdocs содержит поле idxed, которое равно true, если документ проиндексирован, и false, если нет

Когда поступает поисковый запрос и ожидающие документы индексируются впервые, таблица ftsdocs должна быть обновлена: столбец idxed устанавливается в true, а несколько других столбцов заполняются информацией, относящейся к поиску. Эта другая информация получается с помощью соединения. Запрос выглядит следующим образом:

UPDATE ftsdocs
   SET idxed=1, name=NULL,
       (label,url,mtime) = (
           SELECT printf('Check-in [%.16s] on %s',blob.uuid,
                         datetime(event.mtime)),
                  printf('/timeline?y=ci&c=%.20s',blob.uuid),
                  event.mtime
             FROM event, blob
            WHERE event.objid=ftsdocs.rid
              AND blob.rid=ftsdocs.rid
       )
 WHERE ftsdocs.type='c' AND NOT ftsdocs.idxed

Обновляются пять из девяти столбцов таблицы ftsdocs. Два из изменяемых столбцов, idxed и name, можно обновить независимо от запроса. Но три столбца label, url и mtime требуют запроса с соединением к таблицам event и blob. Без значений строк эквивалентный UPDATE потребовал бы повторения соединения три раза — по одному разу для каждого обновляемого столбца

Ясность представления

Иногда использование значений строк просто делает SQL легче для чтения и написания. Рассмотрим следующие два оператора UPDATE:

UPDATE tab1 SET (a,b)=(b,a);
UPDATE tab1 SET a=b, b=a;

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

Или рассмотрим эти идентичные запросы:

SELECT * FROM tab1 WHERE a=?1 AND b=?2;
SELECT * FROM tab1 WHERE (a,b)=(?1,?2);

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

Типичные ошибки при работе со значениями строк

Работая со значениями строк, разработчики нередко допускают несколько характерных ошибок

Несовпадение размеров. Сравнение значений строк разного размера является ошибкой. Если левая часть содержит два элемента, правая тоже должна содержать ровно два

Использование значения строки с одним столбцом. Значение строки с одним элементом — это просто скалярное значение. SQLite не трактует (x) как значение строки; это просто x в скобках

Ожидание, что RHS оператора IN может быть списком в скобках. Для оператора IN со значением строки правая часть обязана быть подзапросом, а не статическим списком вида IN ((1,2),(3,4)). Попытка использовать список в скобках вместо подзапроса приведёт к синтаксической ошибке

Игнорирование поведения NULL. Сравнение значений строк с NULL-составляющими подчиняется трёхзначной логике. Результат может оказаться NULL там, где разработчик ожидает 0 или 1. Пример из раздела 2.1 наглядно показывает, как именно NULL распространяется в результат

Использование в версиях SQLite до 3.15.0. Значения строк появились в SQLite версии 3.15.0 (2016-10-14). В более ранних версиях любая попытка их использовать приведёт к синтаксической ошибке

Оценка результата: когда значения строк действительно помогают

Значения строк не добавляют новых вычислительных возможностей — любой запрос с ними можно переписать без них. Их главная ценность лежит в двух плоскостях

Первая — производительность. Паттерн прокручиваемого окна из раздела 3.1 — это не просто синтаксический сахар. При наличии составного индекса по (lastname, firstname) запрос с WHERE (lastname, firstname) > (?1, ?2) использует индекс напрямую, тогда как OFFSET вынужден сканировать все предшествующие строки. Разница становится ощутимой уже при нескольких тысячах записей

Вторая — читаемость. Запрос WHERE (a,b)=(?1,?2) явно сигнализирует читателю, что a и b образуют составной ключ и проверяются как единое целое. Это снижает когнитивную нагрузку при ревью кода и уменьшает вероятность ошибки при последующих изменениях

Если ни производительность, ни читаемость не выигрывают — значения строк не дают преимущества перед скалярной записью

Обратная совместимость

Значения строк были добавлены в SQLite версии 3.15.0 (2016-10-14). Попытки использовать значения строк в более ранних версиях SQLite будут генерировать синтаксические ошибки

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

Что такое значение строки в SQLite и чем оно отличается от скалярного значения?

Скалярное значение — это одно число, строка, BLOB или NULL. Значение строки — это упорядоченный список из двух или более скалярных значений, то есть кортеж. Значение строки с одним элементом SQLite не считает значением строки — это просто скалярное значение в скобках

Начиная с какой версии SQLite поддерживаются значения строк?

Значения строк поддерживаются начиная с SQLite 3.15.0, выпущенной 2016-10-14. В более ранних версиях их использование вызывает синтаксическую ошибку

Почему сравнение значений строк эффективнее OFFSET при реализации прокручиваемого окна?

При LIMIT x OFFSET y SQLite вычисляет LIMIT x+y и отбрасывает первые y строк, не возвращая их приложению. Это означает, что время выполнения растёт пропорционально смещению. Сравнение (lastname, firstname) > (?1, ?2) при наличии составного индекса позволяет SQLite перейти сразу к нужной позиции, не сканируя предшествующие строки

Может ли правая часть оператора IN со значением строки быть статическим списком в скобках?

Нет. Для оператора IN со значением строки правая часть обязана быть подзапросом. Статический список в скобках в этом контексте приведёт к синтаксической ошибке

Дают ли значения строк новые вычислительные возможности по сравнению со скалярными выражениями?

Нет. Любой запрос со значениями строк можно переписать без них. Их преимущество — в читаемости и, в ряде случаев, в производительности: составной индекс может быть использован эффективнее, чем при эквивалентной скалярной записи с OFFSET