Значения-строки

1. Определения

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

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

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

2. Синтаксис

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

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

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

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

Синтаксис значений-строк и случаи, в которых их можно использовать, показаны в примерах ниже.

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

Два значения-строки сравниваются путём проверки составляющих их скалярных значений слева направо. 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 в третьем столбце.

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

CREATE TABLE t1(a,b,c);
INSERT INTO t1(a,b,c) VALUES(1,2,3);
SELECT (1,2,3)=(SELECT * FROM t1); -- 1

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

Для оператора IN со значением-строкой левая часть (далее «LHS») может быть списком значений в круглых скобках или подзапросом с несколькими столбцами. Правая часть (далее «RHS») должна быть выражением подзапроса.

CREATE TABLE t2(x,y,z);
INSERT INTO t2(x,y,z) VALUES(1,2,3),(2,3,4),(1,NULL,5);
SELECT
   (1,2,3) IN (SELECT * FROM t2),  -- 1
   (7,8,9) IN (SELECT * FROM t2),  -- 0
   (1,3,5) IN (SELECT * FROM t2);  -- NULL

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

Значения-строки также можно использовать в предложении SET оператора UPDATE. Левая часть должна быть списком имён столбцов. Правая часть может быть любым значением-строкой. Например:

UPDATE tab3 
   SET (a,b,c) = (SELECT x,y,z
                    FROM tab4
                   WHERE tab4.w=tab3.d)
 WHERE tab3.e BETWEEN 55 AND 66;

3. Примеры использования значений-строк

3.1. Запросы для прокручиваемых окон

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

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

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

CREATE TABLE info(
  year INT,          -- 4 digit year
  month INT,         -- 1 through 12
  day INT,           -- 1 through 31
  other_stuff BLOB   -- blah blah blah
);

Если даты хранятся таким образом, сравнение значений-строк позволяет удобно сравнивать даты:

SELECT * FROM info
 WHERE (year,month,day) BETWEEN (2015,9,12) AND (2016,9,12);

3.3. Поиск по многоколоночным ключам

Предположим, нам нужно узнать номер заказа, номер товара и количество для любой позиции, у которой номер товара и количество совпадают с номером товара и количеством любой позиции в заказе номер 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;

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

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

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

В системе полнотекстового поиска Fossil документы, участвующие в полнотекстовом поиске (страницы wiki, заявки, фиксации изменений, файлы документации и т. д.), отслеживаются в таблице под названием «ftsdocs» (документы полнотекстового поиска). Новые документы, добавленные в репозиторий, индексируются не сразу. Индексирование откладывается до поступления поискового запроса. Таблица 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 потребовал бы повторить объединение три раза — по одному разу для каждого обновляемого столбца.

3.5. Наглядность представления

Иногда значения-строки просто упрощают чтение и запись 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.

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

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

Последнее обновление этой страницы: 2025-05-31 13:08:22Z