Table 12.13 String Comparison Functions and Operators字符串比较函数和运算符
If a string function is given a binary string as an argument, the resulting string is also a binary string. 如果给字符串函数一个二进制字符串作为参数，则得到的字符串也是二进制字符串。A number converted to a string is treated as a binary string. 转换为字符串的数字被视为二进制字符串。This affects only comparisons.这只影响比较。
Normally, if any expression in a string comparison is case-sensitive, the comparison is performed in case-sensitive fashion.通常，如果字符串比较中的任何表达式区分大小写，则以区分大小写的方式执行比较。
If a string function is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the 如果从mysql客户机中调用字符串函数，则二进制字符串将使用十六进制表示法显示，具体取决于--binary-as-hex. --binary-as-hex的值。For more information about that option, see Section 4.5.1, “mysql — The MySQL Command-Line Client”.有关该选项的更多信息，请参阅第4.5.1节，“mysql命令行客户端”。
expr LIKE pat [ESCAPE 'escape_char']
Pattern matching using an SQL pattern. 使用SQL模式进行模式匹配。Returns 返回1 (TRUE) or 0 (FALSE). 1（TRUE）或0（FALSE）。If either 如果expr or pat is NULL, the result is NULL.expr或pat为NULL，则结果为NULL。
The pattern need not be a literal string. 模式不必是文本字符串。For example, it can be specified as a string expression or table column. 例如，可以将其指定为字符串表达式或表列。In the latter case, the column must be defined as one of the MySQL string types (see Section 11.3, “String Data Types”).在后一种情况下，必须将列定义为MySQL字符串类型之一（请参阅第11.3节，“字符串数据类型”）。
Per the SQL standard, 根据SQL标准，LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator: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 | +--------------------------------------+
In particular, trailing spaces are always significant. 特别是，尾随空格总是重要的。This differs from comparisons performed with the 这与使用= operator, for which the significance of trailing spaces in nonbinary strings (CHAR, VARCHAR, and TEXT values) depends on the pad attribute of the the collation used for the comparison. =运算符执行的比较不同，对于=运算符，非二进制字符串（CHAR、VARCHAR和TEXT值）中尾随空格的重要性取决于用于比较的排序规则的pad属性。For more information, see Trailing Space Handling in Comparisons.有关详细信息，请参阅比较中的尾部空间处理。
With 对于LIKE you can use the following two wildcard characters in the pattern:LIKE，可以在模式中使用以下两个通配符：
% matches any number of characters, even zero characters.匹配任意数量的字符，甚至零个字符。
_ matches exactly one character.只匹配一个字符。
mysql>SELECT 'David!' LIKE 'David_';-> 1 mysql>SELECT 'David!' LIKE '%D%v%';-> 1
To test for literal instances of a wildcard character, precede it by the escape character. 要测试通配符的文本实例，请在通配符前面加转义字符。If you do not specify the 如果未指定转义字符，则假定为ESCAPE character, \ is assumed, unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. \，除非启用了NO_BACKSLASH_ESCAPESSQL模式。In that case, no escape character is used.在这种情况下，不使用转义字符。
\% matches one % character.
\_ matches one _ character.
mysql>SELECT 'David!' LIKE 'David\_';-> 0 mysql>SELECT 'David_' LIKE 'David\_';-> 1
To specify a different escape character, use the 要指定其他转义字符，请使用ESCAPE clause:ESCAPE子句：
mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
-> 1
The escape sequence should be one character long to specify the escape character, or empty to specify that no escape character is used. 转义序列应为一个字符长以指定转义字符，或为空以指定不使用转义字符。The expression must evaluate as a constant at execution time. 表达式在执行时必须作为常量计算。If the 如果启用了NO_BACKSLASH_ESCAPES SQL mode is enabled, the sequence cannot be empty.NO_BACKSLASH_ESCAPESSQL模式，则序列不能为空。
The following two statements illustrate that string comparisons are not case-sensitive unless one of the operands is case-sensitive (uses a case-sensitive collation or is a binary string):以下两条语句说明字符串比较不区分大小写，除非其中一个操作数区分大小写（使用区分大小写的排序规则或二进制字符串）：
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
As an extension to standard SQL, MySQL permits 作为标准SQL的扩展，MySQL允许在数值表达式上使用LIKE on numeric expressions.LIKE。
mysql> SELECT 10 LIKE '1%';
-> 1
MySQL uses C escape syntax in strings (for example, MySQL在字符串中使用C转义语法（例如，\n to represent the newline character). \n表示换行符）。If you want a 如果希望LIKE string to contain a literal \, you must double it. LIKE字符串包含文字\，则必须将其写两次。(Unless the （除非启用了NO_BACKSLASH_ESCAPES SQL mode is enabled, in which case no escape character is used.) NO_BACKSLASH_ESCAPESSQL模式，在这种情况下不使用转义字符。）For example, to search for 例如，要搜索\n, specify it as \\n. \n，请将其指定为\\n。To search for 要搜索\, specify it as \\\\; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against.\，请将其指定为\\\\；这是因为反斜杠被解析器剥离了一次，在模式匹配时又被剥离了一次，只剩下一个反斜杠与之匹配。
Exception: At the end of the pattern string, backslash can be specified as 例外：在模式字符串的末尾，反斜杠可以指定为\\. \\。At the end of the string, backslash stands for itself because there is nothing following to escape. 在字符串的末尾，反斜杠代表它自己，因为后面没有要转义的内容。Suppose that a table contains the following values:假设一个表包含以下值：
mysql> SELECT filename FROM t1;
+--------------+
| filename |
+--------------+
| C: |
| C:\ |
| C:\Programs |
| C:\Programs\ |
+--------------+
To test for values that end with backslash, you can match the values using either of the following patterns:要测试以反斜杠结尾的值，可以使用以下模式之一匹配这些值：
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']
This is the same as 这相当于NOT (.expr LIKE pat [ESCAPE 'escape_char'])NOT (。expr LIKE pat [ESCAPE 'escape_char'])
Aggregate queries involving 与包含NOT LIKE comparisons with columns containing NULL may yield unexpected results. NULL的列进行NOT LIKE比较的聚合查询可能会产生意外的结果。For example, consider the following table and data:例如，考虑下表和数据：
CREATE TABLE foo (bar VARCHAR(10)); INSERT INTO foo VALUES (NULL), (NULL);
The query 查询SELECT COUNT(*) FROM foo WHERE bar LIKE '%baz%'; returns 0. SELECT COUNT(*) FROM foo WHERE bar LIKE '%baz%';返回0。You might assume that 您可以假定SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%'; would return 2. SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%';将返回2。However, this is not the case: The second query returns 但是，情况并非如此：第二个查询返回0. 0。This is because 这是因为NULL NOT LIKE always returns exprNULL, regardless of the value of expr. NULL NOT LIKE 始终返回exprNULL，而不管expr的值是什么。The same is true for aggregate queries involving 对于包含NULL and comparisons using NOT RLIKE or NOT REGEXP. NULL和使用NOT RLIKE或NOT REGEXP进行比较的聚合查询也是如此。In such cases, you must test explicitly for 在这种情况下，必须使用NOT NULL using OR (and not AND), as shown here:OR（而不是AND）显式测试NOT NULL，如下所示：
SELECT COUNT(*) FROM foo WHERE bar NOT LIKE '%baz%' OR bar IS NULL;
STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.STRCMP()返回0；如果根据当前排序顺序，第一个参数小于第二个参数，则返回-1；否则返回1。
mysql>SELECT STRCMP('text', 'text2');-> -1 mysql>SELECT STRCMP('text2', 'text');-> 1 mysql>SELECT STRCMP('text', 'text');-> 0
STRCMP() performs the comparison using the collation of the arguments.
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 | +------------------+------------------+
If the collations are incompatible, one of the arguments must be converted to be compatible with the other. 如果排序规则不兼容，则必须将其中一个参数转换为与另一个参数兼容。See Section 10.8.4, “Collation Coercibility in Expressions”.请参阅第10.8.4节，“表达式中的排序规则强制性”。
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 | +---------------------------------------------+