MySQL的函数和运算符 - 字符串函数和运算符（2）

数据库杂货铺 2021-06-20

881

● FIND_IN_SET(str,strlist)

如果字符串 str 位于由 N 个子字符串组成的字符串列表 strlist 中，则返回 1 到 N 之间的值。字符串列表是一个由 , 字符分隔的子字符串组成的字符串。如果第一个参数是常量字符串，第二个参数是 SET 类型的列，则 FIND_IN_SET() 函数将优化为使用位算术。如果 str 不在 strlist 中或 strlist 是空字符串，则返回 0。如果任一参数为 NULL，则返回 NULL。如果第一个参数包含逗号（,）字符，则此函数无法正常工作。

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

● FORMAT(X,D[,locale])

将数字 X 格式化为形如 '#,###,###.##' 的格式，四舍五入到 D 位小数，并以字符串形式返回结果。如果 D 为 0，则结果没有小数点或小数部分。

可选的第三个参数允许指定区域设置，以用于结果数字的小数点、千位分隔符和分隔符之间的分组。允许的区域设置值与 lc_time_names 系统变量的合法值相同。如果未指定区域设置，则默认值为 '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() 获取 Base64 编码字符串，作为参数传递给 FROM_BASE64()，将解码结果以二进制字符串返回。如果参数为 NULL 或不是有效的 Base64 字符串，则结果为 NULL。

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

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

● HEX(str), HEX(N)

对于字符串参数 str，HEX() 返回 str 的十六进制字符串表示形式，其中 str 中每个字符的每个字节都转换为两位十六进制数字(因此，多字节字符将超过两位数。）此操作的逆操作由 UNHEX() 函数执行。

对于数值参数 N，HEX() 返回 N 值的十六进制字符串表示形式，N 值被视为长长整数（BIGINT）。这相当于 CONV(N,10,16)。其逆操作为 CONV(HEX(N),16,10)。

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。

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

此函数能处理多字节字符串。

● INSTR(str,substr)

返回字符串 str 中第一个出现的子字符串 substr 的位置。这与 LOCATE() 的两个参数形式相同，只是参数的顺序相反。

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

此函数可以安全处理多字节字符，并且仅当至少有一个参数是二进制字符串时才区分大小写。

● LCASE(str)

LCASE() 是 LOWER() 的同义词。

在存储视图定义时，视图中使用的 LCASE() 重写为 LOWER()。

● LEFT(str,len)

返回字符串 str 中最左边的 len 个字符，如果任何参数为 NULL，则返回 NULL。

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

此函数能安全处理多字节字符。

● LENGTH(str)

返回字符串 str 的长度，以字节为单位。多字节字符计为多个字节。这意味着，对于包含五个 2 字节字符的字符串，LENGTH() 返回 10，而 CHAR_LENGTH() 返回 5。

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

注意

Length() OpenGIS 空间函数在 MySQL 中称为 ST_Length()。

● LOAD_FILE(file_name)

读取文件并以字符串形式返回文件内容。要使用此功能，文件必须位于服务器主机上，必须指定文件的完整路径名，并且必须具有 FILE 权限。文件必须可被服务器读取，并且其大小小于 max_allowed_packet 字节数。如果 secure_file_priv 系统变量设置为非空目录名，则要加载的文件必须位于该目录中(在 MySQL 8.0.17 之前，文件必须是所有人都可读的，而不仅仅是服务器可读的。）

如果文件不存在或由于不满足上述条件之一而无法读取，则函数将返回 NULL。

character_set_filesystem 系统变量控制以字面量字符串形式给出的文件名的解释。

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

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

第一种语法返回字符串 str 中第一次出现子字符串 substr 的位置。第二种语法返回字符串 str 中从位置 pos 处开始，第一次出现子字符串 substr 的位置。如果 substr 不在 str 中，则返回 0。如果任何参数为 NULL，则返回 NULL。

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

此函数是多字节安全的，并且仅当至少有一个参数是二进制字符串时才区分大小写。

● LOWER(str)

返回字符串 str，其中所有字符根据当前字符集映射更改为小写。字符集默认值为 utf8mb4。

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

应用于二进制字符串（BINARY, VARBINARY, BLOB）时，LOWER()（和 UPPER()）无效。要执行二进制字符串的字母大小写转换，请首先使用适合字符串中存储数据的字符集将其转换为非二进制字符串：

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 排序规则算法（Unicode Collation Algorithm UCA）版本进行处理（如果有），如果没有指定版本，则根据 UCA 4.0.0 进行处理。例如，utf8mb4_0900_ai_ci 和 utf8_unicode_520_ci 分别根据 UCA 9.0.0 和 5.2.0 工作，而 utf8_unicode_ci 根据 UCA 4.0.0 工作。

此函数是多字节安全的。

视图中使用的 LCASE() 重写为 LOWER()。

● LPAD(str,len,padstr)

返回字符串 str，用字符串 padstr 左填充，填充到字符串长度为 len 个字符。如果 str 长于 len，则返回值将缩短为 len 个字符。

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

● LTRIM(str)

删除字符串 str 的前导空格字符，然后返回结果。

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

此函数是多字节安全的。

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

返回一个集合值（由 , 字符分隔的子字符串组成的字符串），集合值中的字符串由位集中具有相应位的字符串组成。str1 对应于位 0，str2 对应于位 1，依此类推。str1, str2, ... 中的 NULL 值不会附加到结果。

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');
        -> ''

如何理解上面的示例（下面的解释来自网络，感谢！）：

bits 转为二进制表示，如 1 转为 0001，倒过来排序，则为 1000，将 bits 后面的字符串 str1,str2 等，放置在这个倒过来的二进制排序中，取出值为 1 对应的字符串，则得到 hello

1|4 表示进行或运算，即 0001 | 0100，结果为 0101，倒过来排序是 1010，则 'hello','nice','world' 得到的是 hello word。'hello','nice',NULL,'world' 得到的是 hello。NULL 不会附加到结果。

● MID(str,pos,len)

MID(str,pos,len) 是 SUBSTRING(str,pos,len) 的同义词。

● OCT(N)

返回 N 的八进制值的字符串表示形式，其中 N 是长长整数（BIGINT）。这相当于 CONV(N,10,8)。如果 N 为 NULL，则返回 NULL。

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

● OCTET_LENGTH(str)

OCTET_LENGTH() 是 LENGTH() 的同义词。

● ORD(str)

如果字符串 str 的最左边的字符是多字节字符，则返回该字符的代码，该代码使用以下公式从其组成字节的数值计算得出：

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

如果最左边的字符不是多字节字符，ORD() 将返回与 ASCII() 函数相同的值。

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

● POSITION(substr IN str)

POSITION(substr IN str) 是 LOCATE(substr,str) 的同义词。

● QUOTE(str)

引用字符串以生成结果，该结果可用作 SQL 语句中正确转义的数据值。返回的字符串用单引号括起来，每个反斜杠（\）、单引号（'）、ASCII NUL 和 Control+Z 实例前面都有一个反斜杠。如果参数为 NULL，则返回值为单词 “NULL”，不包含单引号。

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

● REPEAT(str,count)

返回由字符串 str 重复 count 次组成的字符串。如果 count 小于 1，则返回空字符串（字符长度0）。如果 str 或 count 为 NULL，则返回 NULL。

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

● REPLACE(str,from_str,to_str)

返回字符串 str，其中所有出现的字符串 from_str 替换为 to_str 字符串。REPLACE() 在搜索 from_str 时区分大小写。

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

此函数是多字节安全的。

● REVERSE(str)

将字符串 str 的字符顺序颠倒后作为结果返回。

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

此函数是多字节安全的。

● RIGHT(str,len)

返回字符串 str 中最右边的 len 个字符，如果任何参数为 NULL，则返回 NULL。

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

此函数是多字节安全的。

● RPAD(str,len,padstr)

返回字符串 str，用字符串 padstr 右填充，直到长度为 len 个字符。如果 str 长于 len，则返回值将缩短为 len 个字符。

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

此函数是多字节安全的。

● RTRIM(str)

返回删除了尾部空格字符的字符串 str。

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

此函数是多字节安全的。

官方文档：

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

mysql

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

MySQL的函数和运算符 - 字符串函数和运算符（2）

评论