行值

、定义

“值”是单个数字、字符串、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 t ext s earch doc ument s)。当新文档添加到存储库时,它们不会立即编入索引。索引被推迟,直到有搜索请求。ftsdocs 表包含一个“idxed”字段,如果文档已被索引则该字段为真,否则为假。

当出现搜索请求并且首次为未决文档编制索引时,必须通过将 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 次,每列更新一次。

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 的早期版本中使用行值将产生语法错误。