MySQL/语言/运算符

MySQL 使用一些标准 SQL 运算符和一些非标准运算符。它们可用于编写包含常量值、变量、字段中包含的值和/或其他表达式的表达式。

比较运算符

相等

如果要检查两个值是否相等，必须使用 = 运算符

 SELECT True = True -- returns 1
 SELECT True = False -- returns 0

如果要检查两个值是否不同，可以使用 <> 或 != 运算符，它们具有相同的含义

 SELECT True <> False -- returns 1
 SELECT True != True -- returns 0

<> 在 = 返回 0 的地方返回 1，反之亦然。

IS 和 NULL 安全比较

当将 NULL 值与非 NULL 值进行比较时，将得到 NULL。如果要检查一个值是否为空，可以使用 IS

 SELECT (NULL IS NULL) -- returns 1
 SELECT (1 IS NULL) -- returns 0
 SELECT (True IS True) -- returns an error!

可以检查一个值是否为非 NULL

 SELECT (True IS NOT NULL) -- returns 1

还有一个相等运算符，它将 NULL 视为一个正常的值，因此如果两个值都是 NULL，它将返回 1（非 NULL）；如果其中一个值是 NULL，它将返回 0（非 NULL）。

 SELECT NULL <=> NULL -- 1
 SELECT True <=> True -- 1
 SELECT col1 <=> col2 FROM myTable

没有 NULL 安全的不等运算符，但可以键入以下内容

 SELECT NOT (col1 <=> col2) FROM myTable

IS 和布尔比较

IS 和 IS NOT 也可以用于布尔比较。可以将它们与保留字 TRUE、FALSE 和 UNKNOWN（它只是 NULL 的同义词）一起使用。

 SELECT 1 IS TRUE -- returns 1
 SELECT 1 IS NOT TRUE -- returns 0
 SELECT 1 IS FALSE -- returns 0
 SELECT (NULL IS NOT FALSE) -- returns 1: unknown is not false
 SELECT (NULL IS UNKNOWN) -- returns 1
 SELECT (NULL IS NOT UNKNOWN) -- returns 0

大于，小于...

可以检查一个值是否大于另一个值

 SELECT 100 > 0 -- returns 1
 SELECT 4 > 5 -- return 0

也可以检查一个值是否小于另一个值

 SELECT 1 < 2 -- returns 1
 SELECT 2 < 2 -- returns 0

这种类型的比较也适用于 TEXT 值

 SELECT 'a' < 'b' -- returns 1

一般来说，TEXT 比较使用字母顺序。但是，确切的规则由使用的 COLLATION 定义。COLLATION 定义了给定字符集的排序规则。例如，一个 COLLATION 可能是区分大小写的，而另一个 COLLATION 可能是区分大小写的。

可以检查一个值是否等于或大于另一个值。例如，以下查询具有相同的含义

 SELECT `a` >= `b` FROM `myTable`
 SELECT NOT (`a` < `b`) FROM `myTable`

类似地，可以检查一个值是否小于或等于另一个值

 SELECT `a` <= `b` FROM `myTable`

BETWEEN

如果要检查一个值是否包含在给定的范围内（包括边界），可以使用 BETWEEN ... AND ... 运算符。AND 没有其通常的含义。例子

 SELECT 2 BETWEEN 10 AND 100    -- 0
 SELECT 10 BETWEEN 10 AND 100   -- 1
 SELECT 20 BETWEEN 10 AND 100   -- 1

BETWEEN 后的值和 AND 后的值都包含在范围内。

也可以使用 NOT BETWEEN 来检查一个值是否未包含在范围内

 SELECT 8 NOT BETWEEN 5 AND 10 -- returns 0

IN

可以使用 IN 运算符来检查一个值是否包含在一个值列表中

 SELECT 5 IN (5, 6, 7) -- returns 1
 SELECT 1 IN (5, 6, 7) -- returns 0

不要在列表中同时包含数字和字符串，否则结果可能不可预测。如果有数字，应该用引号括起来

 SELECT 4 IN ('a', 'z', '5')

IN 运算符中包含的值的数量没有理论上的限制。

也可以使用 NOT IN

 SELECT 1 NOT IN (1, 2, 3) -- returns 0

逻辑运算符

MySQL 布尔逻辑

MySQL 没有真正的 BOOLEAN 数据类型。

FALSE 是 0 的同义词。空字符串在布尔上下文中被视为 FALSE。

TRUE 是 1 的同义词。所有非 NULL 和非 FALSE 数据在布尔上下文中被视为 TRUE。

UNKNOWN 是 NULL 的同义词。特殊日期 0/0/0 是 NULL。

NOT

NOT 是唯一只有一个操作数的运算符。如果操作数为 TRUE，它返回 0；如果操作数为 FALSE，它返回 1；如果操作数为 NULL，它返回 NULL。

 SELECT NOT 1 -- returns 0
 SELECT NOT FALSE -- returns 1
 SELECT NOT NULL -- returns NULL
 SELECT NOT UNKNOWN -- returns NULL

! 是 NOT 的同义词。

 SELECT !1

AND

AND 如果两个操作数都为 TRUE，则返回 1；否则返回 0；如果至少一个操作数为 NULL，则返回 NULL。

 SELECT 1 AND 1 -- returns 1
 SELECT 1 AND '' -- return 0
 SELECT '' AND NULL -- returns NULL

&& 是 AND 的同义词。

 SELECT 1 && 1

OR

OR 如果至少一个操作数为 TRUE，则返回 TRUE；否则返回 FALSE；如果两个操作数都是 NULL，则返回 NULL。

 SELECT TRUE OR FALSE -- returns 1
 SELECT 1 OR 1 -- returns 1
 SELECT FALSE OR FALSE -- returns 0
 SELECT NULL OR TRUE -- returns NULL

|| 是 OR 的同义词。

 SELECT 1 || 0

XOR

XOR（eXclusive OR）如果只有一个操作数为 TRUE，而另一个操作数为 FALSE，则返回 1；如果两个操作数都为 TRUE 或两个操作数都为 FALSE，则返回 0；如果其中一个操作数为 NULL，则返回 NULL。

 SELECT 1 XOR 0 -- returns 1
 SELECT FALSE XOR TRUE -- returns 1
 SELECT 1 XOR TRUE -- returns 0
 SELECT 0 XOR FALSE -- returns 0
 SELECT NULL XOR 1 -- returns NULL

同义词

AND 可以写成 &&
OR 可以写成 ||
NOT 可以写成 !

只有 NOT（通常）与它的同义词有不同的优先级。有关详细信息，请参阅运算符优先级。

算术运算符

MySQL 支持执行所有基本算术运算的操作数。

如果需要，可以使用 '+' 键入正值

 SELECT +1 -- return 1

可以使用 '-' 键入负值。 - 是一个反转运算符

 SELECT -1 -- returns -1
 SELECT -+1 -- returns -1
 SELECT --1 -- returns 1

你可以使用 '+' 进行加法运算。

 SELECT 1 + 1 -- returns 2

你可以使用 '-' 进行减法运算。

 SELECT True - 1 -- returns 0

你可以使用 '*' 乘以一个数字。

 SELECT 1 * 1 -- returns 1

你可以使用 '/' 进行除法运算。返回一个 FLOAT 类型的数字。

 SELECT 10 / 2 -- returns 5.0000
 SELECT 1 / 1 -- returns 1.0000
 SELECT 1 / 0 -- returns NULL (not an error)

你可以使用 DIV 进行整数除法。结果是一个 INTEGER 类型的数字。没有余数。该功能在 MySQL 4.1 中添加。

 SELECT 10 DIV 3 -- returns 3

你可以使用 '%' 或 MOD 获取除法的余数。

 SELECT 10 MOD 3 -- returns 1

使用 + 进行数据类型转换

你可以通过这种方式将 INTEGER 转换为 FLOAT。

 SELECT 1 + 0.0 -- returns 1.0
 SELECT 1 + 0.000 -- returns 1.000
 SELECT TRUE + 0.000 -- returns 1.000

你不能通过添加 0.0 将字符串转换为 FLOAT 值，但你可以将其转换为 INTEGER。

 SELECT '1' + 0 -- returns 1
 SELECT '1' + FALSE -- returns 1
 SELECT <nowiki>''</nowiki> + <nowiki>''</nowiki> -- returns 0

文本运算符

MySQL 中没有连接运算符。

算术运算符将值转换为数字，然后执行算术运算，因此你不能使用 + 连接字符串。

你可以使用 CONCAT() 函数来代替。

LIKE

LIKE 运算符可以用来检查一个字符串是否匹配一个模式。一个简单的例子

 SELECT * FROM articles WHERE title LIKE 'hello world'

模式匹配通常不区分大小写。有两个例外情况

当 LIKE 比较与使用 BINARY 标志声明的列进行比较时（请参见 CREATE TABLE）；
当表达式包含 BINARY 子句时

 SELECT * 'test' LIKE BINARY 'TEST' -- returns 0

你可以在 LIKE 比较中使用两个特殊字符

_ 表示“任何字符”（但必须是 1 个字符，不能是 0 个或 2 个字符）
% 表示“任何字符序列”（即使是 0 个字符或 1000 个字符）

注意，“\” 也会转义引号（“'”），这种行为不能通过 ESCAPE 子句更改。此外，转义字符本身不会被转义。

LIKE 的常见用法

查找以单词“hello”开头的标题

 SELECT * FROM articles WHERE title LIKE 'hello%'

查找以单词“world”结尾的标题

 SELECT * FROM articles WHERE title LIKE '%world'

查找包含单词“gnu”的标题

 SELECT * FROM articles WHERE title LIKE '%gnu%'

这些特殊字符可以包含在模式本身中：例如，你可能需要搜索“_”字符。在这种情况下，你需要“转义”该字符

 SELECT * FROM articles WHERE title LIKE '\_%' -- titles starting with _
 SELECT * FROM articles WHERE title LIKE '\%%' -- titles starting with %

有时，你可能想要使用与“\”不同的转义字符。例如，你可以使用“/”

 SELECT * FROM articles WHERE title LIKE '/_%' ESCAPE '/'

当你使用 = 运算符时，尾部空格将被忽略。当你使用 LIKE 时，它们将被考虑在内。

 SELECT 'word' = 'word ' -- returns 1
 SELECT 'word' LIKE 'word ' -- returns 0

LIKE 也适用于数字。

 SELECT 123 LIKE '%2%' -- returns 1

如果你想检查一个模式是否不匹配，你可以使用 NOT LIKE

 SELECT 'a' NOT LIKE 'b' -- returns 1

SOUNDS LIKE

你可以使用 SOUNDS LIKE 来检查两个文本值是否以相同的方式发音。SOUNDS LIKE 使用 SOUNDEX 算法，该算法基于英语规则，非常近似（但简单，因此速度快）。

 SELECT `word1` SOUNDS LIKE `word2` FROM `wordList` -- short form
 SELECT SOUNDEX(`word1`) = SOUNDEX(`word2`) FROM `wordList` -- long form

SOUNDS LIKE 是 MySQL 对 SQL 的特有扩展。它在 MySQL 4.1 中添加。

正则表达式

你可以使用 REGEXP 来检查一个字符串是否使用正则表达式匹配一个模式。

 SELECT 'string' REGEXP 'pattern'

你可以使用 RLIKE 作为 REGEXP 的同义词。

位运算符

位非

 SELECT ~0 -- returns 18446744073709551615
 SELECT ~1 -- returns 18446744073709551614

位与

 SELECT 1 & 1 -- returns 1
 SELECT 1 & 3 -- returns 1
 SELECT 2 & 3 -- returns 2

位或

 SELECT 1 | 0 -- returns 1
 SELECT 3 | 0 -- returns 3
 SELECT 4 | 2 -- returns 6

位异或

 SELECT 1 ^ 0 -- returns 1
 SELECT 1 ^ 1 -- returns 0
 SELECT 3 ^ 1 -- returns 2

左移

 SELECT 1 << 2 -- returns 4

右移

 SELECT 1 >> 2 -- 0

条件

IF

IF ... THEN ... ELSE ... END IF; 结构仅在存储过程中起作用。为了在存储过程之外管理条件，我们可以使用^[1]: IF(condition, ifTrue, ifFalse);。

示例：SELECT IF(-1 < 0, 0, 1); 返回 0。

包含多个条件的示例（switch）^[2]^[3]

    IF n > m THEN SET s = '>';
    ELSEIF n = m THEN SET s = '=';
    ELSE SET s = '<';
    END IF;

CASE

SELECT CASE WHEN condition THEN ifTrue ELSE ifFalse END;

示例：SELECT CASE WHEN '-1 < 0' THEN 0 ELSE 1 END; 返回 0。

包含多个条件的示例^[4]

    CASE v
      WHEN 2 THEN SELECT v;
      WHEN 3 THEN SELECT 0;
      ELSE
        BEGIN
        END;
    END CASE;

在一个请求中

SELECT CASE v
    WHEN 1 THEN 'a'
    WHEN 2 THEN 'b'
    WHEN 3 THEN 'c'
    WHEN 4 THEN 'd'
    ELSE 0
    END as value

优先级

运算符优先级

运算符优先级表

 INTERVAL
 BINARY, COLLATE
 !
 - (unary minus), ~ (unary bit inversion)
 ^
 *, /, DIV, %, MOD
 -, +
 <<, >>
 &
 |
 =, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
 BETWEEN, CASE, WHEN, THEN, ELSE
 NOT
 &&, AND
 XOR
 ||, OR
 :=

修饰符

PIPES_AS_CONCAT - 如果启用此 SQL 模式，|| 优先于 ^，但 - 和 ~ 优先于 ||。
HIGH_NOT_PRECEDENCE - 如果启用此 SQL 模式，NOT 与 ! 的优先级相同。

使用括号

你可以使用括号强制 MySQL 在运算符优先级之外，先评估一个子表达式，然后再评估另一个。

 SELECT (1 + 1) * 5 -- returns 10

你也可以使用括号使表达式对人类更易读，即使它们不影响优先级。

 SELECT 1 + (2 * 5) -- the same as 1 + 2 * 5

赋值运算符

你可以使用 = 运算符将值赋予列。

 UPDATE `myTable` SET `uselessField`=0

当你想将值赋予变量时，你必须使用 := 运算符，因为使用 = 会产生歧义（它是赋值还是比较？）。

 SELECT @myvar := 1

你也可以使用 SELECT INTO 将值赋予一个或多个变量。

[1] ttps://dev.mysqlserver.cn/doc/refman/5.7/en/control-flow-functions.html

[2] ttps://dev.mysqlserver.cn/doc/refman/5.7/en/if.html

[3] ttps://dev.mysqlserver.cn/doc/refman/5.7/en/case.html

[4] ttps://dev.mysqlserver.cn/doc/refman/5.7/en/case.html

[1]

[2]

[3]

[4]