运算符

运算符由特殊字符或关键字表示;它们不使用函数调用语法。一个运算符可操作任意数量的数据输入(也称为操作数),并返回结果。

常见惯例:

  • 除非另有指定,否则只要有一个操作数是 NULL,所有运算符都会返回 NULL
  • 如果计算结果溢出,所有运算符都将抛出错误。
  • 对于所有浮点运算,仅当其中一个操作数是 +/-infNaN 时才可能返回 +/-infNaN。在其他情况下,系统会返回错误。

运算符优先级

下表按从高到低的优先顺序(即在语句中计算的顺序)列出所有 Cloud Spanner SQL 运算符。

优先顺序 运算符 输入数据类型 名称 运算符元数
1 . JSON
STRUCT
字段访问运算符 二进制
  数组下标运算符 ARRAY 数组位置。必须与 OFFSET 或 ORDINAL 一起使用 - 请参阅数组函数 二进制
  JSON 下标运算符 JSON JSON 格式的字段名称或数组位置。 二进制
2 + 所有数字类型 一元加号 一元
  - 所有数字类型 一元取反 一元
  ~ 整数或 BYTES 按位非 一元
3 * 所有数字类型 二进制
  / 所有数字类型 二进制
  || STRING、BYTES 或 ARRAY<T> 串联运算符 二进制
4 + 所有数字类型 二进制
  - 所有数字类型 二进制
5 << 整数或 BYTES 按位左移 二进制
  >> 整数或 BYTES 按位右移 二进制
6 & 整数或 BYTES 按位与 二进制
7 ^ 整数或 BYTES 按位异或 二进制
8 | 整数或 BYTES 按位或 二进制
9(比较运算符) = 任何可比较类型。请参阅数据类型以获取完整列表。 等于 二进制
  < 任何可比较类型。请参阅数据类型以获取完整列表。 小于 二进制
  > 任何可比较类型。请参阅数据类型以获取完整列表。 大于 二进制
  <= 任何可比较类型。请参阅数据类型以获取完整列表。 小于或等于 二进制
  >= 任何可比较类型。请参阅数据类型以获取完整列表。 大于或等于 二进制
  !=、<> 任何可比较类型。请参阅数据类型以获取完整列表。 不等于 二进制
  [NOT] LIKE STRING 和字节 值与指定的模式 [不] 匹配 二进制
  [NOT] BETWEEN 任何可比较类型。请参阅数据类型以获取完整列表。 值 [不] 在指定的范围内 二进制
  [NOT] IN 任何可比较类型。请参阅数据类型以获取完整列表。 值[不]属于指定的值集 二进制
  IS [NOT] NULL 全部 值 [不] 是 NULL 一元
  IS [NOT] TRUE BOOL 值 [不] 为 TRUE。 一元
  IS [NOT] FALSE BOOL 值 [不] 为 FALSE。 一元
10 NOT BOOL 逻辑非 一元
11 AND BOOL 逻辑与 二进制
12 OR BOOL 逻辑或 二进制

优先级相同的运算符采用左结合方式。这表示这些运算符会从左开始向右组合在一起。例如,以下表达式:

x AND y AND z

会被解释为

( ( x AND y ) AND z )

以下表达式:

x * y / z

会被解释为:

( ( x * y ) / z )

所有比较运算符的优先级相同,但比较运算符不具有结合性。因此,需要使用括号来避免歧义。例如:

(x < y) IS FALSE

字段访问运算符

expression.fieldname[. ...]

说明

获取字段的值。也称为点运算符。可用于访问嵌套字段。例如 expression.fieldname1.fieldname2

输入类型

  • STRUCT
  • JSON

返回类型

  • 对于 STRUCTfieldname 的 SQL 数据类型。如果在结构体中找不到字段,则会抛出错误。
  • 用于 JSONJSON。如果在 JSON 值中找不到某个字段,则返回 SQL NULL

数组下标运算符

array_expression [position_keyword (array_element_id)]

说明

获取特定位置数组中的值。某些数组函数支持。

输入类型

  • position_keywordOFFSETORDINAL。如需了解详情,请参阅 OFFSET 和 ORDINAL
  • array_element_id:表示数组中索引的整数。

返回类型

存储在数组中索引处的类型 T

JSON 下标运算符

json_expression[array_element_id]
json_expression[field_name]

说明

获取 JSON 表达式中的数组元素或字段的值。可用于访问嵌套数据。例如:

(JSON '["apple", "orange", "pear"]')[1] -- Returns JSON 'orange'
(JSON '{"apple": "10", "pear": "5"}')['pear'] -- Returns JSON '5'
(JSON '[ {"fruit": "apple"}, {"fruit": "pear"}]')[0]['fruit'] -- Returns JSON 'apple'
(JSON '[ {"fruit": "apple"}, {"fruit": "pear"}]')[1]['fruit'] -- Returns JSON 'pear'

输入数据类型

  • JSON expression:包含要返回的数组元素或字段的 JSON 表达式。
  • [array_element_id]INT64 表达式,表示数组中的索引(从零开始)。如果输入负值,或者值大于或等于数组大小,或者 JSON 表达式不表示 JSON 数组,则返回 SQL NULL
  • [field_name]STRING 表达式,表示 JSON 中的字段名称。如果未找到字段名称或 JSON 表达式不是 JSON 对象,则返回 SQL NULL

结果数据类型

JSON

算术运算符

除非以下说明中另有指定,否则所有算术运算符都接受数字类型 T 的输入且结果类型为类型 T:

名称 语法
X + Y
X - Y
X * Y
X / Y
一元加号 + X
一元取反 - X

注意:除以 0 的运算会返回一个错误。如需返回不同的结果,请考虑使用 IEEE_DIVIDE 或 SAFE_DIVIDE 函数。

加法、减法和乘法的结果类型:

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

除法的结果类型:

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

一元加法的结果类型:

INPUTINT64NUMERICFLOAT64
输出INT64NUMERICFLOAT64

一元取反的结果类型:

INPUTINT64NUMERICFLOAT64
输出INT64NUMERICFLOAT64

按位运算符

所有按位运算符均返回与第一个操作符相同的类型和长度。

名称 语法 输入数据类型 说明
按位非 ~ X 整数或 BYTES 对每一位进行逻辑非运算,生成给定二进制值的补码。
按位或 X | Y X:整数或 BYTES
Y:类型与 X 相同
采用长度相等的两位模式,并对每一对相应位执行逻辑兼或运算。如果 X 和 Y 是不同长度的 BYTES,则此运算符会抛出错误。
按位异或 X ^ Y X:整数或 BYTES
Y:类型与 X 相同
采用长度相等的两位模式,并对每一对相应位执行逻辑排斥或运算。如果 X 和 Y 是不同长度的 BYTES,则此运算符会抛出错误。
按位与 X & Y X:整数或 BYTES
Y:类型与 X 相同
采用长度相等的两位模式,并对每一对相应位执行逻辑与运算。如果 X 和 Y 是不同长度的 BYTES,则此运算符会抛出错误。
向左移位 X << Y X:整数或 BYTES
Y:INT64
将第一个操作数 X 向左移位。如果第二个操作数 Y 大于或等于第一个操作数 X 的位长度(例如,如果 X 的类型是 INT64,则其位长度是 64),则此运算符返回 0,或者返回 b'\x00' 字节序列。如果 Y 是负数,则此运算符会抛出错误。
向右移位 X >> Y X:整数或 BYTES
Y:INT64
将第一个操作数 X 向右移位。此运算符不会对带符号的类型执行符号位扩展(也就是说,它会在左侧的空位上填入 0)。如果第二个操作数 Y 大于或等于第一个操作数 X 的位长度(例如,如果 X 的类型是 INT64,则其位长度是 64),则此运算符返回 0,或者返回 b'\x00' 字节序列。如果 Y 是负数,则此运算符会抛出错误。

逻辑运算符

Cloud Spanner SQL 支持 ANDORNOT 逻辑运算符。逻辑运算符仅允许 BOOL 或 NULL 输入,并使用三值逻辑来生成结果。结果可以是 TRUEFALSENULL

x y x AND y x OR y
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
TRUE NULL NULL TRUE
FALSE TRUE FALSE TRUE
FALSE FALSE FALSE FALSE
FALSE NULL FALSE NULL
NULL TRUE NULL TRUE
NULL FALSE FALSE NULL
NULL NULL NULL NULL
x NOT x
TRUE FALSE
FALSE TRUE
NULL NULL

示例

本部分中的示例引用名为 entry_table 的表:

+-------+
| entry |
+-------+
| a     |
| b     |
| c     |
| NULL  |
+-------+
SELECT 'a' FROM entry_table WHERE entry = 'a'

-- a => 'a' = 'a' => TRUE
-- b => 'b' = 'a' => FALSE
-- NULL => NULL = 'a' => NULL

+-------+
| entry |
+-------+
| a     |
+-------+
SELECT entry FROM entry_table WHERE NOT (entry = 'a')

-- a => NOT('a' = 'a') => NOT(TRUE) => FALSE
-- b => NOT('b' = 'a') => NOT(FALSE) => TRUE
-- NULL => NOT(NULL = 'a') => NOT(NULL) => NULL

+-------+
| entry |
+-------+
| b     |
| c     |
+-------+
SELECT entry FROM entry_table WHERE entry IS NULL

-- a => 'a' IS NULL => FALSE
-- b => 'b' IS NULL => FALSE
-- NULL => NULL IS NULL => TRUE

+-------+
| entry |
+-------+
| NULL  |
+-------+

比较运算符

比较运算符始终返回 BOOL。通常情况下,两个操作数的类型必须相同才能进行比较运算。如果操作数的类型不同,并且 Cloud Spanner SQL 可以在没有精度损失的情况下将这些类型的值转换为某种通用类型,那么 Cloud Spanner SQL 通常会将这些类型强制转换为该通用类型以进行比较;Cloud Spanner SQL 通常会将字面量强制转换为非字面量类型(如果存在)。如需了解可以进行比较的数据类型,请参阅数据类型

STRUCT 仅支持 4 种比较运算符:等于 (=)、不等于(!= 和 <>)及 IN

这些数据类型在比较时遵循以下规则:

  • FLOAT64:所有带 NaN 的比较都返回 FALSE(!=<> 除外,它们返回的是 TRUE)。
  • BOOL:FALSE 小于 TRUE。
  • STRING:字符串按代码点逐一进行比较,这意味着只有先对字符串进行标准化处理,才能保证按规范相等的字符串比较为相等。
  • NULL:根据惯例,带 NULL 输入的任何运算均返回 NULL
名称 语法 说明
小于 X < Y 如果 X 小于 Y,则返回 TRUE。
小于或等于 X <= Y 如果 X 小于或等于 Y,则返回 TRUE。
大于 X > Y 如果 X 大于 Y,则返回 TRUE。
大于或等于 X >= Y 如果 X 大于或等于 Y,则返回 TRUE。
等于 X = Y 如果 X 等于 Y,则返回 TRUE。
不等于 X != Y
X <> Y
如果 X 不等于 Y,则返回 TRUE。
BETWEEN X [NOT] BETWEEN Y AND Z

如果 X [不] 在指定的范围内,则返回 TRUE。“X BETWEEN Y AND Z”的结果等于“Y <= X AND X <= Z”,但 X 在第一项运算中只计算一次。

LIKE X [NOT] LIKE Y 检查第一个操作数 X 中的 STRING 是否与第二个操作数 Y 指定的模式相匹配。表达式可包含以下字符:
  • 百分号“%”会匹配任意数量的字符或字节
  • 下划线“_”与单个字符或字节相匹配
  • 您可以使用两个反斜线转义“\”、“_”或“%”,例如 "\\%"。如果使用原始字符串,则只需要一个反斜线,例如 r"\%"
IN 多个 - 见下文 如果右操作数为空,则返回 FALSE。如果左操作数为 NULL,则返回 NULL。如果右操作数包含 NULL,则返回 TRUE 或 NULL(永远不会返回 FALSE)。IN 运算符任一侧的参数是一般表达式。不是所有操作数都必须要是文本,但最常见的情况是在右侧使用文本。X 只计算一次。

在测试数据类型为 STRUCT 的值的相等性时,可能出现一个或多个字段为 NULL 的情况。在此类情况下:

  • 如果所有非 NULL 字段值均相等,则比较运算会返回 NULL。
  • 如果任一 NULL 字段值不相等,则比较返回 False。

下表显示了在 STRUCT 数据类型具有带 NULL 值的字段时如何进行比较。

Struct1 Struct2 Struct1 = Struct2
STRUCT(1, NULL) STRUCT(1, NULL) NULL
STRUCT(1, NULL) STRUCT(2, NULL) FALSE
STRUCT(1,2) STRUCT(1, NULL) NULL

IN 运算符

IN 运算符支持以下语法:

search_value [NOT] IN value_set

value_set:
  {
    (expression[, ...])
    | (subquery)
    | UNNEST(array_expression)
  }

说明

在一组值中检查是否存在相等的值。语义规则适用,但一般而言,如果发现相等值,IN 将返回 TRUE,如果排除相等值,则返回 FALSE,否则返回 NULL。如果找到相等值,NOT IN 返回 FALSE,如果排除相等值,则返回 TRUE,否则返回 NULL

  • search_value:与一组值进行比较的表达式。
  • value_set:与搜索值进行比较的一个或多个值。

    • (expression[, ...]):表达式列表。
    • (subquery):返回单列的子查询。该列中的值是一组值。如果未生成任何行,则值集为空。
    • UNNEST(array_expression):一个 UNNEST 运算符,可返回一个数组表达式中的值列。这等同于:

      IN (SELECT element FROM UNNEST(array_expression) AS element)
      

语义规则

使用 IN 运算符时,以下语义按以下顺序应用:

  • 如果 value_set 为空,则返回 FALSE
  • 如果 search_valueNULL,则返回 NULL
  • 如果 value_set 包含等于 search_value 的值,则返回 TRUE
  • 如果 value_set 包含 NULL,则返回 NULL
  • 返回 FALSE

使用 NOT IN 运算符时,以下语义按以下顺序应用:

  • 如果 value_set 为空,则返回 TRUE
  • 如果 search_valueNULL,则返回 NULL
  • 如果 value_set 包含等于 search_value 的值,则返回 FALSE
  • 如果 value_set 包含 NULL,则返回 NULL
  • 返回 TRUE

以下表达式的语义:

x IN (y, z, ...)

被定义为等效于:

(x = y) OR (x = z) OR ...

并且子查询和数组形式具有相似定义。

x NOT IN ...

等效于:

NOT(x IN ...)

UNNEST 形式会像 FROM 子句中的 UNNEST 一样处理数组扫描:

x [NOT] IN UNNEST(<array expression>)

该形式通常与 ARRAY 参数一起使用。例如:

x IN UNNEST(@array_parameter)

如需详细了解如何使用此语法,请参阅数组主题。

IN 可通过 Struct 构造函数语法与多部分键搭配使用。例如:

(Key1, Key2) IN ( (12,34), (56,78) )
(Key1, Key2) IN ( SELECT (table.a, table.b) FROM table )

如需了解详情,请参阅结构体类型

返回数据类型

BOOL

示例

在以下示例中,您可以使用这些 WITH 子句模拟 WordsItems 的临时表:

WITH Words AS (
  SELECT 'Intend' as value UNION ALL
  SELECT 'Secure' UNION ALL
  SELECT 'Clarity' UNION ALL
  SELECT 'Peace' UNION ALL
  SELECT 'Intend' UNION ALL
 )
SELECT * FROM Words;

+----------+
| value    |
+----------+
| Intend   |
| Secure   |
| Clarity  |
| Intend   |
+----------+
WITH
  Items AS (
    SELECT STRUCT('blue' AS color, 'round' AS shape) AS info UNION ALL
    SELECT STRUCT('blue', 'square') UNION ALL
    SELECT STRUCT('red', 'round')
  )
SELECT * FROM Items;

+----------------------------+
| info                       |
+----------------------------+
| {blue color, round shape}  |
| {blue color, square shape} |
| {red color, round shape}   |
+----------------------------+

使用 IN 和表达式的示例:

SELECT * FROM Words WHERE value IN ('Intend', 'Secure');

+----------+
| value    |
+----------+
| Intend   |
| Secure   |
| Intend   |
+----------+

使用 NOT IN 和表达式的示例:

SELECT * FROM Words WHERE value NOT IN ('Intend');

+----------+
| value    |
+----------+
| Secure   |
| Clarity  |
+----------+

使用 IN、标量子查询和表达式的示例:

SELECT * FROM Words WHERE value IN ((SELECT 'Intend'), 'Clarity');

+----------+
| value    |
+----------+
| Intend   |
| Clarity  |
| Intend   |
+----------+

使用 INUNNEST 操作的示例:

SELECT * FROM Words WHERE value IN UNNEST(['Secure', 'Clarity']);

+----------+
| value    |
+----------+
| Secure   |
| Clarity  |
+----------+

包含 INSTRUCT 的示例:

SELECT
  (SELECT AS STRUCT Items.info) as item
FROM
  Items
WHERE (info.shape, info.color) IN (('round', 'blue'));

+------------------------------------+
| item                               |
+------------------------------------+
| { {blue color, round shape} info } |
+------------------------------------+

IS 运算符

IS 运算符会针对其所测试的条件返回 TRUE 或 FALSE。与数学函数中定义的 IS_INF 和 IS_NAN 函数不同,IS 运算符永远不会返回 NULL(即使是对于 NULL 输入也是如此)。如果存在 NOT,则该运算符会对输出 BOOL 值执行取反运算。

函数语法 输入数据类型 结果数据类型 说明

X IS [NOT] NULL
任何值类型 BOOL 如果操作数 X 计算结果为 NULL,则返回 TRUE;否则返回 FALSE。

X IS [NOT] TRUE
BOOL BOOL 如果 BOOL 操作数计算结果为 TRUE,则返回 TRUE;否则返回 FALSE。

X IS [NOT] FALSE
BOOL BOOL 如果 BOOL 操作数计算结果为 FALSE,则返回 TRUE;否则返回 FALSE。

串联运算符

串联运算符用于将多个值合并为一个值。

函数语法 输入数据类型 结果数据类型

STRING || STRING [ || ... ]
STRING STRING

BYTES || BYTES [ || ... ]
BYTES STRING

ARRAY<T> || ARRAY<T> [ || ... ]
ARRAY<T> ARRAY<T>