Row Values

1.定义

2.语法

2.1.行值比较

2.2.UPDATE语句中的行值

3.行值的示例用法

3.1.滚动窗口查询

3.2.比较存储为单独字段的日期

3.3.按多列键搜索

3.4.基于查询更新表的多个列

3.5.介绍的清晰度

4.向后兼容性

1. Definitions

“值”是单个数字，字符串，BLOB或NULL。有时，限定名称“标量值”用于强调只涉及单个数量。

“行值”是两个或多个标量值的有序列表。换句话说，“行值”是一个向量。

行值的“大小”是行值包含的标量值的数量。行值的大小始终至少为2.具有单列的行值只是标量值。没有列的行值是语法错误。

2. Syntax

SQLite允许以两种方式表示行值：

一个括号，逗号分隔的标量值列表。

包含两个或更多结果列的子查询表达式。

SQLite可以在两个上下文中使用行值：

使用运算符<，<=，>，> =，=，<>，IS，IS NOT，IN，NOT IN，BETWEEN或CASE可以比较相同大小的两行值。

在UPDATE语句中，可以将列名称列表设置为相同大小的行值。

下面的示例说明了行值的使用情况以及可以使用哪些行值的情况。

2.1. Row Value Comparisons

通过查看从左到右的组成标量值来比较两行值。NULL表示“未知”。比较的总体结果是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，结果可能为true;如果替换NULL→9，结果可能为false。“（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

对于行值为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.2. Row Values In UPDATE Statements

行值也可以用在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. Example Uses Of Row Values

3.1. Scrolling Window Queries

假设一个应用程序想要在一个滚动窗口中以姓氏，名字的字母顺序显示一个联系人列表，该窗口一次只能显示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. Comparison of dates stored as separate fields

在数据库表中存储日期的常用方法是作为单个字段，如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. Search against multi-column keys

假设我们想要知道订单号码，产品编号和数量与产品编号和数量与订单编号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. Update multiple columns of a table based on a query

行值表示法对于从单个查询的结果更新表中的两列或更多列非常有用。一个例子就是Fossil版本控制系统的全文搜索功能。

在Fossil全文搜索系统中，参与全文搜索的文档（维基页面，门票，签到，文档文件等）由名为“ftsdocs”（全文搜索文档）的表格进行跟踪。随着新文档被添加到存储库，它们不会马上被索引。索引被推迟到有一个搜索请求。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”可以独立于查询进行更新。但是，“标签”，“url”和“mtime”三列都需要对“事件”和“blob”表进行连接查询。如果没有行值，等效的UPDATE将要求连接重复三次，每次更新一列。

3.5. Clarity of presentation

有时使用行值只会使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. Backwards Compatibility

行值已添加到SQLite 版本3.15.0（2016-10-14）。尝试在以前版本的SQLite中使用行值会产生语法错误。

 SQLite is in the Public Domain.