小巧。快速。可靠。
三选二。
行值
► 目录

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 语句中的行值

行值也可以在 UPDATE 语句的 SET 子句中使用。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. 滚动窗口查询

假设一个应用程序想要按姓氏、名字的字母顺序显示联系人列表，在一个只能显示 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”（full text search documents）的表跟踪。将新文档添加到存储库后，不会立即对其建立索引。索引会被延迟，直到有搜索请求。ftsdocs 表包含一个“idxed”字段，如果文档已建立索引，则该字段为 true，否则为 false。

当发生搜索请求并且首次对挂起的文档建立索引时，必须通过将 idxed 列设置为 true 以及用与搜索相关的其他信息填充其他几个列来更新 ftsdocs 表。这些其他信息是从连接中获得的。查询如下

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 中使用行值将生成语法错误。