操作符
更新时间：2026-07-24
按类别列出 SQL 表达式可用的操作符，并提供查询示例与结果。
算术
+（加）、-（减）、*（乘）、/（除）、%（取模）。整数相除结果取整，任一侧为浮点则按浮点运算：
SQL
1> SELECT 7 + 2, 7 - 2, 7 * 2, 7 / 2, 7 % 2;
2+---------------------+---------------------+---------------------+---------------------+---------------------+
3| Int64(7) + Int64(2) | Int64(7) - Int64(2) | Int64(7) * Int64(2) | Int64(7) / Int64(2) | Int64(7) % Int64(2) |
4+---------------------+---------------------+---------------------+---------------------+---------------------+
5| 9 | 5 | 14 | 3 | 1 |
6+---------------------+---------------------+---------------------+---------------------+---------------------+
SQL
1> SELECT 7.0 / 2;
2+-----------------------+
3| Float64(7) / Int64(2) |
4+-----------------------+
5| 3.5 |
6+-----------------------+比较
=、!= / <>（等价）、<、<=、>、>=；与 NULL 比较结果为 NULL，判 NULL 用 IS NULL 或 IS DISTINCT FROM：
SQL
1> SELECT 2 = 2 AS eq, 3 != 4 AS neq, 3 <> 4 AS neq2, 2 < 3 AS lt, 2 <= 2 AS lte, 3 > 2 AS gt, 3 >= 3 AS gte;
2+------+------+------+------+------+------+------+
3| eq | neq | neq2 | lt | lte | gt | gte |
4+------+------+------+------+------+------+------+
5| true | true | true | true | true | true | true |
6+------+------+------+------+------+------+------+IS DISTINCT FROM / IS NOT DISTINCT FROM 是把 NULL 当作普通值比较的等值判断：
SQL
1> SELECT 1 IS DISTINCT FROM NULL, NULL IS NOT DISTINCT FROM NULL;
2+--------------------------------+--------------------------------+
3| Int64(1) IS DISTINCT FROM NULL | NULL IS NOT DISTINCT FROM NULL |
4+--------------------------------+--------------------------------+
5| true | true |
6+--------------------------------+--------------------------------+逻辑
AND、OR、NOT，遵循 SQL 三值逻辑（NULL 参与时结果可能为 NULL）：
SQL
1> SELECT true AND false, true OR false, NOT true;
2+----------------------------------+---------------------------------+-------------------+
3| Boolean(true) AND Boolean(false) | Boolean(true) OR Boolean(false) | NOT Boolean(true) |
4+----------------------------------+---------------------------------+-------------------+
5| false | true | false |
6+----------------------------------+---------------------------------+-------------------+模式匹配
LIKE / NOT LIKE（% 匹配任意串、_ 匹配单字符）、ILIKE（忽略大小写）：
SQL
1> SELECT 'influx' LIKE 'in%', 'influx' NOT LIKE 'in%', 'INFLUX' ILIKE 'in%';
2+---------------------------------+-------------------------------------+----------------------------------+
3| Utf8("influx") LIKE Utf8("in%") | Utf8("influx") NOT LIKE Utf8("in%") | Utf8("INFLUX") ILIKE Utf8("in%") |
4+---------------------------------+-------------------------------------+----------------------------------+
5| true | false | true |
6+---------------------------------+-------------------------------------+----------------------------------+正则匹配操作符：~（匹配）、~*（忽略大小写匹配）、!~（不匹配）、!~*（忽略大小写不匹配），正则语法为 Rust regex 方言：
SQL
1> SELECT 'influx' ~ '^in', 'INFLUX' ~* '^in', 'influx' !~ '^db', 'INFLUX' !~* '^db';
2+------------------------------+-------------------------------+-------------------------------+--------------------------------+
3| Utf8("influx") ~ Utf8("^in") | Utf8("INFLUX") ~* Utf8("^in") | Utf8("influx") !~ Utf8("^db") | Utf8("INFLUX") !~* Utf8("^db") |
4+------------------------------+-------------------------------+-------------------------------+--------------------------------+
5| true | true | true | true |
6+------------------------------+-------------------------------+-------------------------------+--------------------------------+范围与集合
BETWEEN ... AND ...（闭区间）、IN (...) 及其 NOT 形式、IS NULL / IS NOT NULL：
SQL
1> SELECT 5 BETWEEN 1 AND 10, 15 NOT BETWEEN 1 AND 10;
2+-----------------------------------------+----------------------------------------------+
3| Int64(5) BETWEEN Int64(1) AND Int64(10) | Int64(15) NOT BETWEEN Int64(1) AND Int64(10) |
4+-----------------------------------------+----------------------------------------------+
5| true | true |
6+-----------------------------------------+----------------------------------------------+
SQL
1> SELECT 2 IN (1, 2, 3), 5 NOT IN (1, 2, 3);
2+------------------------------------------+----------------------------------------------+
3| Int64(2) IN Int64(1), Int64(2), Int64(3) | Int64(5) NOT IN Int64(1), Int64(2), Int64(3) |
4+------------------------------------------+----------------------------------------------+
5| true | true |
6+------------------------------------------+----------------------------------------------+
SQL
1> SELECT NULL IS NULL, 1 IS NOT NULL;
2+--------------+----------------------+
3| NULL IS NULL | Int64(1) IS NOT NULL |
4+--------------+----------------------+
5| true | true |
6+--------------+----------------------+字符串拼接
|| 拼接字符串：
SQL
1> SELECT 'time' || '-' || 'series';
2+---------------------------------------------+
3| Utf8("time") || Utf8("-") || Utf8("series") |
4+---------------------------------------------+
5| time-series |
6+---------------------------------------------+位运算
&（与）、|（或）、^（异或）、<<（左移）、>>（右移），作用于整数：
SQL
1> SELECT 5 & 3 AS b_and, 5 | 3 AS b_or, 5 ^ 3 AS b_xor, 1 << 4 AS shl, 16 >> 2 AS shr;
2+-------+------+-------+-----+-----+
3| b_and | b_or | b_xor | shl | shr |
4+-------+------+-------+-----+-----+
5| 1 | 7 | 6 | 16 | 4 |
6+-------+------+-------+-----+-----+时间运算
timestamp 与 interval 可加减：
SQL
1SELECT TIMESTAMP '2024-01-01T00:00:00Z' + INTERVAL '1 day'; -- 2024-01-02T00:00:00
2SELECT TIMESTAMP '2024-01-01T00:00:00Z' - INTERVAL '6 hours'; -- 2023-12-31T18:00:00AT TIME ZONE 把时间戳标注/转换到指定时区（也见 时间日期函数 的 tz 函数）：
SQL
1> SELECT TIMESTAMP '2024-01-01 00:00:00Z' AT TIME ZONE 'Asia/Shanghai';
2+------------------------------+
3| Utf8("2024-01-01 00:00:00Z") |
4+------------------------------+
5| 2024-01-01T00:00:00+08:00 |
6+------------------------------+上例输入串带 Z（UTC）却没有发生换算：对无时区 timestamp 而言，AT TIME ZONE 是把字符串里的墙上时间标注为目标时区（这里 +08:00），Z 只在解析时被丢弃、不参与换算。要真正做 UTC 到目标时区的换算，需先得到带时区的 timestamp（如 arrow_cast(..., 'Timestamp(Nanosecond, Some("UTC"))')）再 AT TIME ZONE。
元素访问
[n] 取数组第 n 个元素（1 起），['name'] 取 struct / map 字段：
SQL
1> SELECT make_array(10, 20, 30)[2];
2+-----------------------------------------------------+
3| make_array(Int64(10),Int64(20),Int64(30))[Int64(2)] |
4+-----------------------------------------------------+
5| 20 |
6+-----------------------------------------------------+
SQL
1> SELECT named_struct('value', 9.7, 'time', TIMESTAMP '2019-08-28 07:23:00')['value'];
2+------------------------------------------------------------------------------------------+
3| named_struct(Utf8("value"),Float64(9.7),Utf8("time"),Utf8("2019-08-28 07:23:00"))[value] |
4+------------------------------------------------------------------------------------------+
5| 9.7 |
6+------------------------------------------------------------------------------------------+条件表达式
CASE WHEN ... THEN ... [ELSE ...] END：
SQL
1> SELECT CASE WHEN 1 > 0 THEN 'positive' ELSE 'negative' END;
2+-------------------------------------------------------------------------------+
3| CASE WHEN Int64(1) > Int64(0) THEN Utf8("positive") ELSE Utf8("negative") END |
4+-------------------------------------------------------------------------------+
5| positive |
6+-------------------------------------------------------------------------------+类型转换
CAST(expr AS type) 与后缀形式 expr::type 等价；TRY_CAST 在转换失败时返回 NULL 而不报错：
SQL
1> SELECT CAST('42' AS INT) AS c1, '42'::INT AS c2;
2+----+----+
3| c1 | c2 |
4+----+----+
5| 42 | 42 |
6+----+----+
SQL
1> SELECT TRY_CAST('abc' AS INT);
2+-------------+
3| Utf8("abc") |
4+-------------+
5| |
6+-------------+需要精确指定引擎类型（如 UInt64、带时区 timestamp）时用 arrow_cast，见 类型转换与杂项函数。
评价此篇文章