MySQL的函数和运算符 - 字符串比较函数和运算符

林员外聊编程 2021-06-22

267

字符串比较函数和运算符

名称	介绍
LIKE	简单模式匹配
NOT LIKE	简单模式匹配的否定
STRCMP()	比较两个字符串

如果给字符串函数一个二进制字符串作为参数，则得到的字符串也是二进制字符串。转换为字符串的数字被视为二进制字符串。这只影响比较。

通常，如果字符串比较中的任何表达式区分大小写，则以区分大小写的方式执行比较。

如果从 mysql 客户端中调用字符串函数，则二进制字符串将使用十六进制表示法显示，具体取决于 --binary-as-hex 的值。

● expr LIKE pat [ESCAPE 'escape_char']

使用 SQL 模式进行模式匹配。返回 1（TRUE）或0（FALSE）。如果 expr 或 pat 为 NULL，则结果为 NULL。

模式不必是字面量字符串。例如，可以将其指定为字符串表达式或表的某列。在后一种情况下，必须将列定义为 MySQL 字符串类型之一。

根据 SQL 标准，LIKE 基于每个字符执行匹配，因此它可以生成不同于 = 比较运算符的结果：

mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

特别是，尾随空格总是影响很大的。这与使用 = 运算符执行的比较不同，对于 = 运算符，非二进制字符串（CHAR、VARCHAR 和 TEXT 值）中尾随空格的重要性取决于用于比较的排序规则的填充属性。

对于 LIKE，可以在模式中使用以下两个通配符：

■ % 匹配任意数量的字符，甚至零个字符。

■ _ 只匹配一个字符。

mysql> SELECT 'David!' LIKE 'David_';
        -> 1
mysql> SELECT 'David!' LIKE '%D%v%';
        -> 1

要测试通配符的字面量实例，请在通配符前面加转义符。如果未指定 ESCAPE 字符，则假定为 \，除非启用了 NO_BACKSLASH_ESCAPES SQL 模式。在这种情况下，不使用转义字符。

■ \% 匹配一个 % 字符。

■ \_ 匹配一个 _ 字符。

mysql> SELECT 'David!' LIKE 'David\_';
        -> 0
mysql> SELECT 'David_' LIKE 'David\_';
        -> 1

要指定其他转义字符，请使用 ESCAPE 子句：

mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1

转义序列应为一个字符长，或为空（零长度）以指定不使用转义字符。表达式在执行时必须计算为常量。如果启用了 NO_BACKSLASH_ESCAPES SQL 模式，则序列不能为空。

以下两条语句说明字符串比较不区分大小写，除非其中一个操作数区分大小写（使用区分大小写的排序规则或二进制字符串）：

mysql> SELECT 'abc' LIKE 'ABC';
        -> 1
mysql> SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_0900_as_cs;
        -> 0
mysql> SELECT 'abc' LIKE _utf8mb4 'ABC' COLLATE utf8mb4_bin;
        -> 0
mysql> SELECT 'abc' LIKE BINARY 'ABC';
        -> 0

作为标准 SQL 的扩展，MySQL 允许在数值表达式上使用 LIKE。

mysql> SELECT 10 LIKE '1%';
        -> 1

注意

MySQL 在字符串中使用 C 转义语法（例如，\n 表示换行符）。如果希望 LIKE 字符串包含字面量 \，则必须将其加倍(除非启用了 NO_BACKSLASH_ESCAPES SQL 模式，在这种情况下不使用转义字符）。例如，要搜索 \n，请将其指定为 \\n。要搜索 \，请将其指定为 \\\\；这是因为反斜杠被解析器剥离了一次，在模式匹配时又被剥离了一次，只剩下一个反斜杠与之匹配。

例外：在模式字符串的末尾，反斜杠可以指定为 \\。在字符串的末尾，反斜杠代表它自己，因为后面没有要转义的内容。假设一个表包含以下值：

mysql> SELECT filename FROM t1;
+--------------+
| filename     |
+--------------+
| C:           |
| C:\          |
| C:\Programs  |
| C:\Programs\ |
+--------------+

要测试以反斜杠结尾的值，可以使用以下模式之一匹配这些值：

mysql> SELECT filename, filename LIKE '%\\' FROM t1;
+--------------+---------------------+
| filename     | filename LIKE '%\\' |
+--------------+---------------------+
| C:           |                   0 |
| C:\          |                   1 |
| C:\Programs  |                   0 |
| C:\Programs\ |                   1 |
+--------------+---------------------+


mysql> SELECT filename, filename LIKE '%\\\\' FROM t1;
+--------------+-----------------------+
| filename     | filename LIKE '%\\\\' |
+--------------+-----------------------+
| C:           |                     0 |
| C:\          |                     1 |
| C:\Programs  |                     0 |
| C:\Programs\ |                     1 |
+--------------+-----------------------+

● expr NOT LIKE pat [ESCAPE 'escape_char']

这与 NOT (expr LIKE pat [ESCAPE 'escape_char']) 相同。

注意

对包含 NULL 的列进行 NOT LIKE 比较的聚合查询可能会产生意外的结果。例如，考虑下表和数据：

CREATE TABLE foo (bar VARCHAR(10));


INSERT INTO foo VALUES (NULL), (NULL);

查询 SELECT COUNT(*) FROM foo WHERE bar LIKE '%baz%'; 返回 0。您可能认为 SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%'; 将返回 2。但是，情况并非如此：第二个查询返回 0。这是因为 NULL NOT LIKE expr 总是返回 NULL，而与 expr 的值无关。对于包含 NULL 和使用 NOT RLIKE 或 NOT REGEXP 进行比较的聚合查询也是如此。在这种情况下，必须使用 OR 显式测试 NOT NULL，如下所示：

SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%' OR bar IS NULL;

● STRCMP(expr1,expr2)

如果字符串相同，STRCMP() 返回 0；如果根据当前排序顺序，第一个参数小于第二个参数，则返回 -1；否则返回 1。

mysql> SELECT STRCMP('text', 'text2');
        -> -1
mysql> SELECT STRCMP('text2', 'text');
        -> 1
mysql> SELECT STRCMP('text', 'text');
        -> 0

STRCMP() 使用参数的排序规则执行比较。

mysql> SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs;
mysql> SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs;
mysql> SELECT STRCMP(@s1, @s2), STRCMP(@s3, @s4);
+------------------+------------------+
| STRCMP(@s1, @s2) | STRCMP(@s3, @s4) |
+------------------+------------------+
|                0 |               -1 |
+------------------+------------------+

如果排序规则不兼容，则必须将其中一个参数转换为与另一个参数兼容。

mysql> SET @s1 = _utf8mb4 'x' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s2 = _utf8mb4 'X' COLLATE utf8mb4_0900_ai_ci;
mysql> SET @s3 = _utf8mb4 'x' COLLATE utf8mb4_0900_as_cs;
mysql> SET @s4 = _utf8mb4 'X' COLLATE utf8mb4_0900_as_cs;
-->
mysql> SELECT STRCMP(@s1, @s3);
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT)
and (utf8mb4_0900_as_cs,IMPLICIT) for operation 'strcmp'
mysql> SELECT STRCMP(@s1, @s3 COLLATE utf8mb4_0900_ai_ci);
+---------------------------------------------+
| STRCMP(@s1, @s3 COLLATE utf8mb4_0900_ai_ci) |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+

官方文档：

https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html

mysql

文章转载自林员外聊编程，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的函数和运算符 - 字符串比较函数和运算符

评论