ASCII(str)

返回字符串 str 最左边字符的数值。如果 str 是空字符串，则返回 0。如果 str 为 NULL，则返回 NULL。ASCII() 适用于 8 位字符。Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for 8-bit characters.

mysql> SELECT ASCII('2');
        -> 50
mysql> SELECT ASCII(2);
        -> 50
mysql> SELECT ASCII('dx');
        -> 100

另请参见 ORD() 函数。See also the ORD() function.

BIN(N)

返回 N 的二进制值的字符串表示形式，其中 N 是一个 longlong (BIGINT) 数字。这相当于 CONV(N,10,2)。如果 N 为 NULL，则返回 NULL。Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL.

mysql> SELECT BIN(12);
        -> '1100'

BIT_LENGTH(str)

返回字符串 str 的长度（以比特为单位）。如果 str 为 NULL，则返回 NULL。Returns the length of the string str in bits. Returns NULL if str is NULL.

mysql> SELECT BIT_LENGTH('text');
        -> 32

CHAR(N,... [USING charset_name])CHAR(N,... [USING charset_name])

CHAR() 将每个参数 N 解释为整数，并返回由这些整数的码值对应的字符组成的字符串。NULL 值会被跳过。CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.

mysql> SELECT CHAR(77,121,83,81,'76');
+--------------------------------------------------+
| CHAR(77,121,83,81,'76')                          |
+--------------------------------------------------+
| 0x4D7953514C                                     |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR(77,77.3,'77.3');
+--------------------------------------------+
| CHAR(77,77.3,'77.3')                       |
+--------------------------------------------+
| 0x4D4D4D                                   |
+--------------------------------------------+
1 row in set (0.00 sec)

默认情况下，CHAR() 返回二进制字符串。要生成指定字符集的字符串，请使用可选的 USING 子句：By default, CHAR() returns a binary string. To produce a string in a given character set, use the optional USING clause:

mysql> SELECT CHAR(77,121,83,81,'76' USING utf8mb4);
+---------------------------------------+
| CHAR(77,121,83,81,'76' USING utf8mb4) |
+---------------------------------------+
| MySQL                                 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR(77,77.3,'77.3' USING utf8mb4);
+------------------------------------+
| CHAR(77,77.3,'77.3' USING utf8mb4) |
+------------------------------------+
| MMM                                |
+------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: '77.3' |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

如果提供了 USING 并且结果字符串对于指定字符集是非法的，将会发出警告。此外，如果启用了严格 SQL 模式，CHAR() 的结果将变为 NULL。If USING is given and the result string is illegal for the given character set, a warning is issued. Also, if strict SQL mode is enabled, the result from CHAR() becomes NULL.

如果在 mysql 客户端内调用 CHAR()，二进制字符串将根据 --binary-as-hex 的值以十六进制显示。有关该选项的更多信息，请参见 第 6.5.1 节，“mysql — The MySQL 命令行客户端”。If CHAR() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

CHAR() 参数大于 255 时会转换为多个结果字节。例如，CHAR(256) 等同于 CHAR(1,0)，而 CHAR(256*256) 等同于 CHAR(1,0,0)：CHAR() arguments larger than 255 are converted into multiple result bytes. For example, CHAR(256) is equivalent to CHAR(1,0), and CHAR(256*256) is equivalent to CHAR(1,0,0):

mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
+----------------+----------------+
| HEX(CHAR(1,0)) | HEX(CHAR(256)) |
+----------------+----------------+
| 0100           | 0100           |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));
+------------------+--------------------+
| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |
+------------------+--------------------+
| 010000           | 010000             |
+------------------+--------------------+
1 row in set (0.00 sec)

CHAR_LENGTH(str)CHAR_LENGTH(str)

返回字符串 str 的长度，以代码点计算。一个多字节字符算作一个代码点。这意味着，对于一个包含两个三字节字符的字符串，LENGTH() 返回 6，而 CHAR_LENGTH() 返回 2，如下面所示：Returns the length of the string str, measured in code points. A multibyte character counts as a single code point. This means that, for a string containing two 3-byte characters, LENGTH() returns 6, whereas CHAR_LENGTH() returns 2, as shown here:

mysql> SET @dolphin:='海豚';
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT LENGTH(@dolphin), CHAR_LENGTH(@dolphin);
+------------------+-----------------------+
| LENGTH(@dolphin) | CHAR_LENGTH(@dolphin) |
+------------------+-----------------------+
|                6 |                     2 |
+------------------+-----------------------+
1 row in set (0.00 sec)

CHAR_LENGTH() 如果 str 为 NULL，则返回 NULL。CHAR_LENGTH() returns NULL if str is NULL.

CHARACTER_LENGTH(str)CHARACTER_LENGTH(str)

CHARACTER_LENGTH() 是 CHAR_LENGTH() 的同义词。CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

CONCAT(str1,str2,...)

返回通过连接参数得到的字符串。可以有一个或多个参数。如果所有参数都是非二进制字符串，结果是非二进制字符串。如果参数中包含任何二进制字符串，结果将是二进制字符串。数值参数会被转换为其等效的非二进制字符串形式。Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent nonbinary string form.

CONCAT() 如果任意参数为 NULL，则返回 NULL。CONCAT() returns NULL if any argument is NULL.

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

对于带引号的字符串，可以通过将字符串放在一起进行连接：For quoted strings, concatenation can be performed by placing the strings next to each other:

mysql> SELECT 'My' 'S' 'QL';
        -> 'MySQL'

如果在 mysql 客户端中调用 CONCAT()，二进制字符串结果会根据 --binary-as-hex 的值以十六进制显示。有关该选项的更多信息，请参见 第 6.5.1 节，“mysql — MySQL 命令行客户端”。If CONCAT() is invoked from within the mysql client, binary string results display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

CONCAT_WS(separator,str1,str2,...)CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() 代表带分隔符的连接，是 CONCAT() 的一种特殊形式。第一个参数是其余参数的分隔符。该分隔符将添加在要连接的字符串之间。分隔符可以是字符串，其余参数也可以是字符串。如果分隔符是 NULL，结果就是 NULL。CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

mysql> SELECT CONCAT_WS(',', 'First name', 'Second name', 'Last Name');
        -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',', 'First name', NULL, 'Last Name');
        -> 'First name,Last Name'

CONCAT_WS() 不会跳过空字符串。然而，它会跳过分隔符参数之后的任何 NULL 值。CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

ELT(N,str1,str2,str3,...)

ELT() 返回字符串列表中的第 N 个元素：如果 N = 1，则返回 str1，如果 N = 2，则返回 str2，以此类推。如果 N 小于 1、大于参数数量，或为 NULL，则返回 NULL。ELT() 是 FIELD() 的补充函数。ELT() returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1, greater than the number of arguments, or NULL. ELT() is the complement of FIELD().

mysql> SELECT ELT(1, 'Aa', 'Bb', 'Cc', 'Dd');
        -> 'Aa'
mysql> SELECT ELT(4, 'Aa', 'Bb', 'Cc', 'Dd');
        -> 'Dd'

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

返回一个字符串，对于 bits 中设置为 1 的每个位，返回一个 on 字符串，对于每个位未设置的，返回一个 off 字符串。bits 中的位从右到左（从低位到高位）进行检查。字符串从左到右添加到结果中，以 separator 字符串分隔（默认是逗号字符 ,）。检查的位数由 number_of_bits 指定，如果未指定，默认值为 64。如果 number_of_bits 大于 64，则会自动裁剪为 64。它被视为无符号整数，因此值为 −1 实际上等同于 64。Returns a string such that for every bit set in the value bits, you get an on string and for every bit not set in the value, you get an off string. Bits in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (the default being the comma character ,). The number of bits examined is given by number_of_bits, which has a default of 64 if not specified. number_of_bits is silently clipped to 64 if larger than 64. It is treated as an unsigned integer, so a value of −1 is effectively the same as 64.

mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
        -> 'Y,N,Y,N'
mysql> SELECT EXPORT_SET(6,'1','0',',',10);
        -> '0,1,1,0,0,0,0,0,0,0'

FIELD(str,str1,str2,str3,...)

返回 str 在 str1、str2、str3、... 列表中的索引（位置）。如果未找到 str，则返回 0。Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

如果 FIELD() 的所有参数都是字符串，则所有参数按字符串进行比较。如果所有参数都是数字，则按数字进行比较。否则，参数将按双精度浮点数进行比较。If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.

如果 str 是 NULL，返回值为 0，因为 NULL 与任何值进行相等比较都会失败。FIELD() 是 ELT() 的补充函数。If str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT().

mysql> SELECT FIELD('Bb', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
        -> 2
mysql> SELECT FIELD('Gg', 'Aa', 'Bb', 'Cc', 'Dd', 'Ff');
        -> 0

FIND_IN_SET(str,strlist)

如果字符串 str 位于由 N 个子字符串组成的字符串列表 strlist 中，则返回 1 到 N 范围内的值。字符串列表是由 , 字符分隔的子字符串组成的字符串。如果第一个参数是常量字符串，第二个参数是类型为 SET 的列，则 FIND_IN_SET() 函数会优化为使用位运算。如果 str 不在 strlist 中，或者 strlist 是空字符串，则返回 0。如果任一参数为 NULL，则返回 NULL。如果第一个参数包含逗号 (,) 字符，则此函数不能正常工作。Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by , characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (,) character.

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2

FORMAT(X,D[,locale])

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. If X或者D is NULL, the function returns NULL.Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part. If X or D is NULL, the function returns NULL.

可选的第三个参数允许指定一个用于结果数字的小数点、千位分隔符和分隔符之间分组的区域设置。允许的区域设置值与 lc_time_names 系统变量的合法值相同（参见 第 12.16 节，“MySQL 服务器区域设置支持”）。如果区域设置为 NULL 或未指定，则默认区域设置为 'en_US'。The optional third parameter enables a locale to be specified to be used for the result number's decimal point, thousands separator, and grouping between separators. Permissible locale values are the same as the legal values for the lc_time_names system variable (see Section 12.16, “MySQL Server Locale Support”). If the locale is NULL or not specified, the default locale is 'en_US'.

mysql> SELECT FORMAT(12332.123456, 4);
        -> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
        -> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
        -> '12,332'
mysql> SELECT FORMAT(12332.2,2,'de_DE');
        -> '12.332,20'

FROM_BASE64(str)

接受一个使用 TO_BASE64() 使用的 base-64 编码规则编码的字符串，并返回解码后的二进制字符串。如果参数为 NULL 或不是有效的 base-64 字符串，则结果为 NULL。有关编码和解码规则的详细信息，请参见 TO_BASE64() 的说明。Takes a string encoded with the base-64 encoded rules used by TO_BASE64() and returns the decoded result as a binary string. The result is NULL if the argument is NULL or not a valid base-64 string. See the description of TO_BASE64() for details about the encoding and decoding rules.

mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
        -> 'JWJj', 'abc'

如果在 FROM_BASE64() 中从 mysql 客户端调用，二进制字符串将以十六进制符号显示。您可以通过在启动 mysql 客户端时，将 --binary-as-hex 设置为 0 来禁用此行为。有关该选项的更多信息，请参见 第 6.5.1 节，“mysql — MySQL 命令行客户端”。If FROM_BASE64() is invoked from within the mysql client, binary strings display using hexadecimal notation. You can disable this behavior by setting the value of the --binary-as-hex to 0 when starting the mysql client. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

HEX(str), HEX(N)

对于字符串参数 str，HEX() 返回 str 的十六进制字符串表示形式，其中 str 中每个字符的每个字节都被转换为两个十六进制数字。（因此，多字节字符将变为多于两个数字。）该操作的逆操作由 UNHEX() 函数执行。For a string argument str, HEX() returns a hexadecimal string representation of str where each byte of each character in str is converted to two hexadecimal digits. (Multibyte characters therefore become more than two digits.) The inverse of this operation is performed by the UNHEX() function.

对于数值参数 N，HEX() 返回将 N 作为 longlong (BIGINT) 数处理后的十六进制字符串表示。这等同于 CONV(N,10,16)。该操作的逆运算由 CONV(HEX(N),16,10) 执行。For a numeric argument N, HEX() returns a hexadecimal string representation of the value of N treated as a longlong (BIGINT) number. This is equivalent to CONV(N,10,16). The inverse of this operation is performed by CONV(HEX(N),16,10).

对于 NULL 参数，此函数返回 NULL。For a NULL argument, this function returns NULL.

mysql> SELECT X'616263', HEX('abc'), UNHEX(HEX('abc'));
        -> 'abc', 616263, 'abc'
mysql> SELECT HEX(255), CONV(HEX(255),16,10);
        -> 'FF', 255

INSERT(str,pos,len,newstr)

返回字符串str，其中从位置pos开始、长度为len的子字符串被字符串newstr替换。如果pos不在字符串长度范围内，则返回原始字符串。如果len不在剩余字符串的长度范围内，则从位置pos起替换剩余的字符串。如果任何参数为NULL，则返回NULL。Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.

mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
        -> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
        -> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
        -> 'QuWhat'

此函数支持多字节。This function is multibyte safe.

INSTR(str,substr)

返回子字符串 substr 在字符串 str 中首次出现的位置。这与 LOCATE() 的两个参数形式相同，只是参数顺序相反。Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.

mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4
mysql> SELECT INSTR('xbar', 'foobar');
        -> 0

此函数支持多字节字符，并且仅在至少一个参数是二进制字符串时区分大小写。如果任一参数为 NULL，此函数返回 NULL。This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string. If either argument is NULL, this functions returns NULL.

LCASE(str)

LCASE() 是 LOWER() 的同义词。LCASE() is a synonym for LOWER().

在视图中使用 LCASE() 时，在存储视图定义时会被重写为 LOWER()。（Bug #12844279）LCASE() used in a view is rewritten as LOWER() when storing the view's definition. (Bug #12844279)

LEFT(str,len)

返回字符串 str 的最左边 len 个字符，如果任一参数为 NULL，则返回 NULL。Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

mysql> SELECT LEFT('foobarbar', 5);
        -> 'fooba'

此函数支持多字节。This function is multibyte safe.

LENGTH(str)

返回字符串 str 的长度，以字节为单位。多字节字符计为多个字节。这意味着对于包含五个 2 字节字符的字符串，LENGTH() 返回 10，而 CHAR_LENGTH() 返回 5。如果 str 为 NULL，则返回 NULL。Returns the length of the string str, measured in bytes. A multibyte character counts as multiple bytes. This means that for a string containing five 2-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5. Returns NULL if str is NULL.

mysql> SELECT LENGTH('text');
        -> 4

LOAD_FILE(file_name)

读取文件并将文件内容作为字符串返回。要使用此函数，文件必须位于服务器主机上，必须指定文件的完整路径名，并且必须拥有FILE权限。文件必须可被服务器读取，且其大小小于max_allowed_packet字节。如果secure_file_priv系统变量设置为非空目录名，则要加载的文件必须位于该目录中。Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full path name to the file, and you must have the FILE privilege. The file must be readable by the server and its size less than max_allowed_packet bytes. If the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

如果文件不存在或由于不满足前述条件而无法读取，函数将返回NULL。If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.

character_set_filesystem 系统变量控制以字面字符串提供的文件名的解释。The character_set_filesystem system variable controls interpretation of file names that are given as literal strings.

mysql> UPDATE t
            SET blob_col=LOAD_FILE('/tmp/picture')
            WHERE id=1;

LOCATE(substr,str), LOCATE(substr,str,pos)

第一个语法返回子字符串 substr 在字符串 str 中第一次出现的位置。第二个语法返回子字符串 substr 在字符串 str 中第一次出现的位置，从位置 pos 开始。如果 substr 不在 str 中，则返回 0。如果任何参数为 NULL，则返回 NULL。The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str. Returns NULL if any argument is NULL.

mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7

这个函数支持多字节，并且只有在至少一个参数是二进制字符串时才区分大小写。This function is multibyte safe, and is case-sensitive only if at least one argument is a binary string.

LOWER(str)

返回字符串 str，其中所有字符根据当前字符集映射转换为小写，如果 str 为 NULL，则返回 NULL。默认字符集为 utf8mb4。Returns the string str with all characters changed to lowercase according to the current character set mapping, or NULL if str is NULL. The default character set is utf8mb4.

mysql> SELECT LOWER('QUADRATICALLY');
        -> 'quadratically'

LOWER()（以及 UPPER()）在应用于二进制字符串（BINARY、VARBINARY、BLOB）时无效。要对二进制字符串进行字母大小写转换，首先使用适合字符串中存储数据的字符集将其转换为非二进制字符串：LOWER() (and UPPER()) are ineffective when applied to binary strings (BINARY, VARBINARY, BLOB). To perform lettercase conversion of a binary string, first convert it to a nonbinary string using a character set appropriate for the data stored in the string:

mysql> SET @str = BINARY 'New York';
mysql> SELECT LOWER(@str), LOWER(CONVERT(@str USING utf8mb4));
+-------------+------------------------------------+
| LOWER(@str) | LOWER(CONVERT(@str USING utf8mb4)) |
+-------------+------------------------------------+
| New York    | new york                           |
+-------------+------------------------------------+

对于 Unicode 字符集的排序规则，LOWER() 和 UPPER() 会根据排序规则名称中的 Unicode 排序算法（UCA）版本进行工作，如果没有指定版本，则使用 UCA 4.0.0。例如，utf8mb4_0900_ai_ci 和 utf8mb3_unicode_520_ci 分别按照 UCA 9.0.0 和 5.2.0 工作，而 utf8mb3_unicode_ci 则按照 UCA 4.0.0 工作。请参阅 第 12.10.1 节，“Unicode 字符集”。For collations of Unicode character sets, LOWER() and UPPER() work according to the Unicode Collation Algorithm (UCA) version in the collation name, if there is one, and UCA 4.0.0 if no version is specified. For example, utf8mb4_0900_ai_ci and utf8mb3_unicode_520_ci work according to UCA 9.0.0 and 5.2.0, respectively, whereas utf8mb3_unicode_ci works according to UCA 4.0.0. See Section 12.10.1, “Unicode Character Sets”.

此函数支持多字节。This function is multibyte safe.

LCASE() 在视图中使用时会重写为 LOWER()。LCASE() used within views is rewritten as LOWER().

LPAD(str,len,padstr)

返回字符串 str，左侧用字符串 padstr 填充至长度 len 个字符。如果 str 长度超过 len，返回值将被截短为 len 个字符。Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

mysql> SELECT LPAD('hi',4,'??');
        -> '??hi'
mysql> SELECT LPAD('hi',1,'??');
        -> 'h'

如果其任意参数为 NULL，返回 NULL。Returns NULL if any of its arguments are NULL.

LTRIM(str)

返回字符串 str，并删除前导空格字符。如果 str 为 NULL，则返回 NULL。Returns the string str with leading space characters removed. Returns NULL if str is NULL.

mysql> SELECT LTRIM('  barbar');
        -> 'barbar'

此函数支持多字节。This function is multibyte safe.

MAKE_SET(bits,str1,str2,...)

返回一个设定值（一个包含以,字符分隔的子字符串的字符串），该值由在bits中相应位被设置的字符串组成。str1对应位 0，str2对应位 1，依此类推。NULL 值在str1、str2、...中不会附加到结果中。Returns a set value (a string containing substrings separated by , characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result.

mysql> SELECT MAKE_SET(1,'a','b','c');
        -> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
        -> 'hello,world'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
        -> 'hello'
mysql> SELECT MAKE_SET(0,'a','b','c');
        -> ''

MID(str,pos), MID(str FROM pos), MID(str,pos,len), MID(str FROM pos FOR len)

MID(str,pos,len) 是 SUBSTRING(str,pos,len) 的同义词。MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).

OCT(N)

返回 N 的八进制值的字符串表示形式，其中 N 是一个 longlong (BIGINT) 数字。这相当于 CONV(N,10,8)。如果 N 为 NULL，则返回 NULL。Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL.

mysql> SELECT OCT(12);
        -> '14'

OCTET_LENGTH(str)

OCTET_LENGTH() 是 LENGTH() 的同义词。OCTET_LENGTH() is a synonym for LENGTH().

ORD(str)

如果字符串 str 的最左边字符是多字节字符，则返回该字符的代码，该代码通过使用其组成字节的数值计算，公式如下：If the leftmost character of the string str is a multibyte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:

  (1st byte code)
+ (2nd byte code * 256)
+ (3rd byte code * 256^2) ...

如果最左边的字符不是多字节字符，ORD() 返回的值与 ASCII() 函数返回的值相同。如果 str 为 NULL，该函数返回 NULL。If the leftmost character is not a multibyte character, ORD() returns the same value as the ASCII() function. The function returns NULL if str is NULL.

mysql> SELECT ORD('2');
        -> 50

POSITION(substr IN str)

POSITION(substr IN str) 是 LOCATE(substr,str) 的同义词。POSITION(substr IN str) is a synonym for LOCATE(substr,str).

QUOTE(str)

对字符串加引号，以生成可在 SQL 语句中作为正确转义数据值使用的结果。返回的字符串会被单引号包围，并且每个反斜杠 (\)、单引号 (')、ASCII NUL 和 Control+Z 前都会加上反斜杠。如果参数为 NULL，则返回值为单词 “NULL”，不加单引号。Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotation marks and with each instance of backslash (\), single quote ('), ASCII NUL, and Control+Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without enclosing single quotation marks.

mysql> SELECT QUOTE('Don\'t!');
        -> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
        -> NULL

有关比较，请参阅在 C API 中对字面字符串加引号的规则，见 第 11.1.1 节，“字符串字面值”，以及 mysql_real_escape_string_quote()。For comparison, see the quoting rules for literal strings and within the C API in Section 11.1.1, “String Literals”, and mysql_real_escape_string_quote().

REPEAT(str,count)REPEAT(str,count)

返回一个由字符串 str 重复 count 次组成的字符串。如果 count 小于 1，则返回空字符串。如果 str 或 count 为 NULL，则返回 NULL。Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count is NULL.

mysql> SELECT REPEAT('MySQL', 3);
        -> 'MySQLMySQLMySQL'

REPLACE(str,from_str,to_str)REPLACE(str,from_str,to_str)

返回字符串 str，其中所有出现的字符串 from_str 都被字符串 to_str 替换。REPLACE() 在搜索 from_str 时执行区分大小写的匹配。Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
        -> 'WwWwWw.mysql.com'

此函数对多字节安全。如果其任何参数为 NULL，则返回 NULL。This function is multibyte safe. It returns NULL if any of its arguments are NULL.

REVERSE(str)REVERSE(str)

返回字符串str的字符顺序反转后的结果，如果str为NULL则返回NULL。Returns the string str with the order of the characters reversed, or NULL if str is NULL.

mysql> SELECT REVERSE('abc');
        -> 'cba'

此函数支持多字节。This function is multibyte safe.

RIGHT(str,len)RIGHT(str,len)

返回字符串str最右侧的len个字符，如果任何参数为NULL则返回NULL。Returns the rightmost len characters from the string str, or NULL if any argument is NULL.

mysql> SELECT RIGHT('foobarbar', 4);
        -> 'rbar'

此函数支持多字节。This function is multibyte safe.

RPAD(str,len,padstr)

返回字符串 str，用字符串 padstr 在右侧填充至长度为 len 个字符。如果 str 长度超过 len，返回值会被截断为 len 个字符。如果 str、padstr 或 len 为 NULL，函数返回 NULL。Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters. If str, padstr, or len is NULL, the function returns NULL.

mysql> SELECT RPAD('hi',5,'?');
        -> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
        -> 'h'

此函数支持多字节。This function is multibyte safe.

RTRIM(str)

返回去掉尾部空格的字符串 str。Returns the string str with trailing space characters removed.

mysql> SELECT RTRIM('barbar   ');
        -> 'barbar'

此函数支持多字节，并且如果 str 为 NULL，则返回 NULL。This function is multibyte safe, and returns NULL if str is NULL.

SOUNDEX(str)

从str返回一个 soundex 字符串，如果str为NULL，则返回NULL。两个发音几乎相同的字符串应该具有相同的 soundex 字符串。标准的 soundex 字符串长度为四个字符，但SOUNDEX()函数返回的是任意长度的字符串。您可以在结果上使用SUBSTRING()来获取标准的 soundex 字符串。str中的所有非字母字符将被忽略。所有 A-Z 范围之外的国际字母字符都被视为元音。Returns a soundex string from str, or NULL if str is NULL. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All nonalphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.

mysql> SELECT SOUNDEX('Hello');
        -> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
        -> 'Q36324'

expr1 SOUNDS LIKE expr2expr1 SOUNDS LIKE expr2

这与 SOUNDEX(expr1) = SOUNDEX(expr2) 相同。This is the same as SOUNDEX(expr1) = SOUNDEX(expr2).

SPACE(N)

返回一个由 N 个空格字符组成的字符串，如果 N 为 NULL，则返回 NULL。Returns a string consisting of N space characters, or NULL if N is NULL.

mysql> SELECT SPACE(6);
        -> '      '

SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)SUBSTR(str,pos), SUBSTR(str FROM pos), SUBSTR(str,pos,len), SUBSTR(str FROM pos FOR len)

SUBSTR() 是 SUBSTRING() 的同义词。SUBSTR() is a synonym for SUBSTRING().

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

没有 len 参数的形式会从字符串 str 的位置 pos 开始返回一个子字符串。有 len 参数的形式会从字符串 str 的位置 pos 开始返回一个长度为 len 的子字符串。使用 FROM 的形式是标准 SQL 语法。也可以为 pos 使用负值。在这种情况下，子字符串的起始位置是从字符串末尾向前数 pos 个字符，而不是从开头开始。pos 的任何形式都可以使用负值。pos 的值为 0 时会返回一个空字符串。The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function. A value of 0 for pos returns an empty string.

对于所有形式的 SUBSTRING()，要提取子字符串的起始字符位置从 1 开始计算。For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

此函数对多字节安全。如果其任何参数为 NULL，则返回 NULL。This function is multibyte safe. It returns NULL if any of its arguments are NULL.

如果 len 小于 1，结果为空字符串。If len is less than 1, the result is the empty string.

SUBSTRING_INDEX(str,delim,count)

返回字符串 str 中在分隔符 delim 出现 count 次之前的子字符串。如果 count 为正数，返回最后一个分隔符（从左边开始计数）左侧的所有内容。如果 count 为负数，返回最后一个分隔符（从右边开始计数）右侧的所有内容。SUBSTRING_INDEX() 在搜索 delim 时执行区分大小写的匹配。Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'

此函数支持多字节。This function is multibyte safe.

SUBSTRING_INDEX() 如果其任何参数为 NULL，则返回 NULL。SUBSTRING_INDEX() returns NULL if any of its arguments are NULL.

TO_BASE64(str)

将字符串参数转换为 Base64 编码形式，并以连接的字符集和排序规则返回结果作为字符串。如果参数不是字符串，则在转换之前会先将其转换为字符串。如果参数为 NULL，结果为 NULL。Base64 编码的字符串可以使用 FROM_BASE64() 函数进行解码。Converts the string argument to base-64 encoded form and returns the result as a character string with the connection character set and collation. If the argument is not a string, it is converted to a string before conversion takes place. The result is NULL if the argument is NULL. Base-64 encoded strings can be decoded using the FROM_BASE64() function.

mysql> SELECT TO_BASE64('abc'), FROM_BASE64(TO_BASE64('abc'));
        -> 'JWJj', 'abc'

存在不同的 base-64 编码方案。以下是 TO_BASE64() 和 FROM_BASE64() 使用的编码和解码规则：Different base-64 encoding schemes exist. These are the encoding and decoding rules used by TO_BASE64() and FROM_BASE64():

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

返回删除了所有 remstr 前缀或后缀的字符串 str。如果没有指定 BOTH、LEADING 或 TRAILING，则默认为 BOTH。remstr 是可选的，如果未指定，则删除空格。Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

mysql> SELECT TRIM('  bar   ');
        -> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
        -> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
        -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
        -> 'barx'

此函数对多字节安全。如果其任何参数为 NULL，则返回 NULL。This function is multibyte safe. It returns NULL if any of its arguments are NULL.

UCASE(str)

UCASE() 是 UPPER() 的同义词。UCASE() is a synonym for UPPER().

UCASE() 在视图中使用时会被重写为 UPPER()。UCASE() used within views is rewritten as UPPER().

UNHEX(str)

对于字符串参数 str，UNHEX(str) 将参数中的每对字符解释为十六进制数，并将其转换为该数字表示的字节。返回值是二进制字符串。For a string argument str, UNHEX(str) interprets each pair of characters in the argument as a hexadecimal number and converts it to the byte represented by the number. The return value is a binary string.

mysql> SELECT UNHEX('4D7953514C');
        -> 'MySQL'
mysql> SELECT X'4D7953514C';
        -> 'MySQL'
mysql> SELECT UNHEX(HEX('string'));
        -> 'string'
mysql> SELECT HEX(UNHEX('1267'));
        -> '1267'

参数字符串中的字符必须是合法的十六进制数字：'0' .. '9'、'A' .. 'F'、'a' .. 'f'。如果参数包含任何非十六进制数字，或参数本身为 NULL，结果为 NULL：The characters in the argument string must be legal hexadecimal digits: '0' .. '9', 'A' .. 'F', 'a' .. 'f'. If the argument contains any nonhexadecimal digits, or is itself NULL, the result is NULL:

mysql> SELECT UNHEX('GG');
+-------------+
| UNHEX('GG') |
+-------------+
| NULL        |
+-------------+

mysql> SELECT UNHEX(NULL);
+-------------+
| UNHEX(NULL) |
+-------------+
| NULL        |
+-------------+

如果传递给 UNHEX() 的参数是 BINARY 列，也可能会出现 NULL 结果，因为在存储时值会用 0x00 字节进行填充，但在检索时这些字节不会被去掉。例如，'41' 存储到 CHAR(3) 列时会存为 '41 '，且检索出来时为 '41'（尾随填充空格被去掉），因此该列值调用 UNHEX() 返回 X'41'。相比之下，'41' 存储到 BINARY(3) 列时会存为 '41\0'，检索出来时为 '41\0'（尾随填充的 0x00 字节未被去掉）。'\0' 不是合法的十六进制数字，因此该列值调用 UNHEX() 返回 NULL。A NULL result can also occur if the argument to UNHEX() is a BINARY column, because values are padded with 0x00 bytes when stored but those bytes are not stripped on retrieval. For example, '41' is stored into a CHAR(3) column as '41 ' and retrieved as '41' (with the trailing pad space stripped), so UNHEX() for the column value returns X'41'. By contrast, '41' is stored into a BINARY(3) column as '41\0' and retrieved as '41\0' (with the trailing pad 0x00 byte not stripped). '\0' is not a legal hexadecimal digit, so UNHEX() for the column value returns NULL.

对于数值参数 N，HEX(N) 的逆操作不是通过 UNHEX() 来执行的。请改用 CONV(HEX(N),16,10)。有关 HEX() 的说明，请参见描述。For a numeric argument N, the inverse of HEX(N) is not performed by UNHEX(). Use CONV(HEX(N),16,10) instead. See the description of HEX().

如果在 mysql 客户端中调用 UNHEX()，二进制字符串将根据 --binary-as-hex 的值以十六进制表示。有关该选项的更多信息，请参阅 第 6.5.1 节，“mysql — The MySQL 命令行客户端”。If UNHEX() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.

UPPER(str)

返回将字符串str中所有字符根据当前字符集映射转换为大写后的字符串，如果str为NULL则返回NULL。默认字符集是utf8mb4。Returns the string str with all characters changed to uppercase according to the current character set mapping, or NULL if str is NULL. The default character set is utf8mb4.

mysql> SELECT UPPER('Hej');
        -> 'HEJ'

请参阅 LOWER() 的描述，其中包含也适用于 UPPER() 的信息。这包括关于如何对二进制字符串（BINARY、VARBINARY、BLOB）进行大小写转换的方法，这些函数对其不起作用，以及关于 Unicode 字符集大小写折叠的信息。See the description of LOWER() for information that also applies to UPPER(). This included information about how to perform lettercase conversion of binary strings (BINARY, VARBINARY, BLOB) for which these functions are ineffective, and information about case folding for Unicode character sets.

此函数支持多字节。This function is multibyte safe.

UCASE() 在视图中使用时会被重写为 UPPER()。UCASE() used within views is rewritten as UPPER().

WEIGHT_STRING(str [AS {CHAR|BINARY}(N)] [flags])

此函数返回输入字符串的权重字符串。返回值是一个二进制字符串，表示该字符串的比较和排序值，如果参数为 NULL，则返回 NULL。它具有以下属性：This function returns the weight string for the input string. The return value is a binary string that represents the comparison and sorting value of the string, or NULL if the argument is NULL. It has these properties:

WEIGHT_STRING() 是一个用于调试的函数，供内部使用。它的行为可能在不同的 MySQL 版本之间无通知地变化。它可用于排序规则的测试和调试，特别是在你添加新的排序规则时。参见 第 12.14 节，“向字符集添加排序规则”。WEIGHT_STRING() is a debugging function intended for internal use. Its behavior can change without notice between MySQL versions. It can be used for testing and debugging of collations, especially if you are adding a new collation. See Section 12.14, “Adding a Collation to a Character Set”.

此列表简要总结了论点。更多细节请参见列表后的讨论。This list briefly summarizes the arguments. More details are given in the discussion following the list.

输入字符串，str，是一个字符串表达式。如果输入的是非二进制（字符）字符串，例如CHAR、VARCHAR或TEXT值，则返回值包含该字符串的排序权重。如果输入的是二进制（字节）字符串，例如BINARY、VARBINARY或BLOB值，则返回值与输入相同（二进制字符串中每个字节的权重就是字节值）。如果输入是NULL，WEIGHT_STRING()返回NULL。The input string, str, is a string expression. If the input is a nonbinary (character) string such as a CHAR, VARCHAR, or TEXT value, the return value contains the collation weights for the string. If the input is a binary (byte) string such as a BINARY, VARBINARY, or BLOB value, the return value is the same as the input (the weight for each byte in a binary string is the byte value). If the input is NULL, WEIGHT_STRING() returns NULL.

示例：Examples:

mysql> SET @s = _utf8mb4 'AB' COLLATE utf8mb4_0900_ai_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 1C471C60               |
+------+---------+------------------------+

mysql> SET @s = _utf8mb4 'ab' COLLATE utf8mb4_0900_ai_ci;
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 1C471C60               |
+------+---------+------------------------+

mysql> SET @s = CAST('AB' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| AB   | 4142    | 4142                   |
+------+---------+------------------------+

mysql> SET @s = CAST('ab' AS BINARY);
mysql> SELECT @s, HEX(@s), HEX(WEIGHT_STRING(@s));
+------+---------+------------------------+
| @s   | HEX(@s) | HEX(WEIGHT_STRING(@s)) |
+------+---------+------------------------+
| ab   | 6162    | 6162                   |
+------+---------+------------------------+

前面的示例使用 HEX() 来显示 WEIGHT_STRING() 的结果。由于结果是二进制值，当结果包含不可打印的值时，HEX() 特别有用，可以将其以可打印的形式显示：The preceding examples use HEX() to display the WEIGHT_STRING() result. Because the result is a binary value, HEX() can be especially useful when the result contains nonprinting values, to display it in printable form:

mysql> SET @s = CONVERT(X'C39F' USING utf8mb4) COLLATE utf8mb4_czech_ci;
mysql> SELECT HEX(WEIGHT_STRING(@s));
+------------------------+
| HEX(WEIGHT_STRING(@s)) |
+------------------------+
| 0FEA0FEA               |
+------------------------+

对于非NULL的返回值，如果其长度在VARBINARY的最大长度之内，则值的数据类型为VARBINARY，否则数据类型为BLOB。For non-NULL return values, the data type of the value is VARBINARY if its length is within the maximum length for VARBINARY, otherwise the data type is BLOB.

可以使用AS子句将输入字符串转换为非二进制或二进制字符串，并强制其为给定长度：The AS clause may be given to cast the input string to a nonbinary or binary string and to force it to a given length:

mysql> SET NAMES 'latin1';
mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
+-------------------------------------+
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
+-------------------------------------+
| 41422020                            |
+-------------------------------------+
mysql> SET NAMES 'utf8mb4';
mysql> SELECT HEX(WEIGHT_STRING('ab' AS CHAR(4)));
+-------------------------------------+
| HEX(WEIGHT_STRING('ab' AS CHAR(4))) |
+-------------------------------------+
| 1C471C60                            |
+-------------------------------------+

mysql> SELECT HEX(WEIGHT_STRING('ab' AS BINARY(4)));
+---------------------------------------+
| HEX(WEIGHT_STRING('ab' AS BINARY(4))) |
+---------------------------------------+
| 61620000                              |
+---------------------------------------+

flags 子句目前未使用。The flags clause currently is unused.

如果从 mysql 客户端内部调用 WEIGHT_STRING()，则二进制字符串将根据 --binary-as-hex 的值以十六进制表示。有关该选项的更多信息，请参阅 第 6.5.1 节，“mysql — MySQL 命令行客户端”。If WEIGHT_STRING() is invoked from within the mysql client, binary strings display using hexadecimal notation, depending on the value of the --binary-as-hex. For more information about that option, see Section 6.5.1, “mysql — The MySQL Command-Line Client”.