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

"Значение" это единственное число, последовательность, BLOB или NULL. Иногда полностью определенное имя "скалярное значение" используется, чтобы подчеркнуть, что только единственное количество включается.

"Значение строки" является упорядоченным списком двух или больше скалярных величин. Другими словами, "значение строки" является вектором или кортежем.

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

2. Синтаксис

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

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

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

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

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

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

Два значения строки сравнены, смотря на учредительные скалярные величины слева направо. NULL значит "unknown". Полный результат сравнения NULL, если возможно сделать результат true или false, заменяя альтернативными значениями вместо учредительного 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. LHS должен быть списком имен столбцов. RHS может быть любым значением строки. Например:

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. Прокрутка запросов окна

Предположим, что применение хочет показать список контактов в алфавитном порядке lastname, firstname, в окне, которое может показать только 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;

Если lastname и firstname в нижней строке предыдущего экрана связаны с ?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);

Когда даты сохранены так, сравнения значения строки обеспечивают удобный способ сравнить даты:

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;

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

3.4. Обновите многочисленные колонки таблицы на основе запроса

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

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

Когда поисковый запрос происходит, и документы внесены в указатель впервые, таблица 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 делают точно то же самое. Они производят идентичный bytecode. Но первая форма, форма значения строки, кажется, делает более ясным, что намерение запроса состоит в том, чтобы обменять значения в колонках A и B.

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

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

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

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

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