第 15 篇 MySQL 常用函数之字符函数

imodouAbout 10 minMySQL

《MySQL 入门教程》第 15 篇 MySQL 常用函数之字符函数

上一篇讨论了 MySQL 中常用的数学函数，本篇我们介绍常用的 MySQL 字符函数。

字符函数和运算符用于对字符串数据进行处理和分析并返回一个结果。以下是 MySQL 中常见的字符函数和运算符：

函数/运算符	描述
ASCII()	返回第一个字符的 ASCII 码
BIT_LENGTH()	返回以比特为单位的字符串长度
CHAR()	返回每个编码值对应的字符
CHAR_LENGTH()、CHARACTER_LENGTH()	返回以字符为单位的字符串长度
CONCAT()	连接多个字符串
CONCAT_WS()	使用指定分隔符连接字符串
ELT()	返回字符串列表中的第 N 个元素
FIELD()	返回第一个字符串在后续参数列表中的索引位置
FIND_IN_SET()	返回第一个字符串在第二个字符串列表参数中的索引位置
FROM_BASE64()	对 base64 编码字符串进行解码并返回结果
INSERT()	在指定位置插入并替换子串
INSTR()	返回子串第一次出现的索引位置
LCASE()、LOWER()	返回小写形式的字符串
LEFT()	返回字符串左侧的 N 个字符
LENGTH()、OCTET_LENGTH()	返回以字节为单位的字符串长度
LOCATE()、POSITION()	返回子串第一次出现的索引位置
LPAD()	在字符串的左侧进行填充
LTRIM()	删除字符串左侧的空格
ORD()	返回第一个字符的编码
REPEAT()	将字符串重复 N 次
REPLACE()	替换子串
REVERSE()	反转字符串
RIGHT()	返回字符串右侧的 N 个字符
RPAD()	在字符串的右侧进行填充
RTRIM()	删除字符串右侧的空格
SPACE()	返回一个由空格组成的字符串
STRCMP()	比较两个字符串
MID()、SUBSTR()、SUBSTRING()	返回从指定位置开始的子串
SUBSTRING_INDEX()	返回指定分隔符之前的子串
TO_BASE64()	将字符串进行 base-64 编码
TRIM()	删除字符串两侧的空格
UCASE()、UPPER()	返回大写形式的字符串

下面我们通过一些示例来说明这些函数的作用。

15.1 字符与编码转换

ASCII(str) 函数返回字符串 str 第一个字符对应的 ASCII 编码；如果 str 是空字符串，返回 0；如果 str 为 NULL，返回 NULL。例如：

select ascii('ABC'), ascii(''), ascii(null);
ascii('ABC')|ascii('')|ascii(null)|
------------|---------|-----------|
          65|        0|           |

ORD(str) 函数与 ASCII(str) 函数类似，但是对于多字节编码的字符返回的编码计算方式如下：

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

例如：

select ord('ABC'), ord('你好');
ord('ABC')|ord('你好')|
----------|-----------|
        65|   14990752|

字符“A”属于单字节编码，返回的是 ASCII 码；字符“你”属于 UTF-8 中的三字节编码，返回的是它的编码的十进制结果。

CHAR(N,... [USING charset_name]) 函数返回每个编码 N 对应的字符，忽略其中的 NULL 参数。例如：

select char(77, 121, 83, 81, '76');
char(77, 121, 83, 81, '76')|
---------------------------|
MySQL                      |

默认情况下，CHAR() 函数返回的是二进制字符串，USING 子句可以将结果转换为相应的字符集：

select charset(char(65)), charset(char(65 using utf8mb4));
charset(char(65))|charset(char(65 using utf8mb4))|
-----------------|-------------------------------|
binary           |utf8mb4                        |

charset() 函数用于返回字符串的字符集。

15.2 获取字符串长度

BIT_LENGTH(str) 函数返回字符串的比特长度（数量），CHAR_LENGTH(str) 和 CHARACTER_LENGTH(str) 函数返回字符串的字符长度（数量），LENGTH(str) 和 OCTET_LENGTH(str) 函数返回字符串的字节长度。例如：

select bit_length('你'), char_length('你'), length('你');
bit_length('你')|char_length('你')|length('你')|
---------------|----------------|-----------|
             24|               1|          3|

字符“你”在 utf8mb4 字符集中占用 24 个比特，3 个字节。

15.3 连接字符串

CONCAT(str1,str2,...)) 函数用于返回多个字符串连接之后的字符串，任何参数为 NULL 则返回 NULL。例如：

select concat('My', 'SQL', 8.0), concat('My', null, 'SQL');
concat('My', 'SQL', 8.0)|concat('My', null, 'SQL')|
------------------------|-------------------------|
MySQL8.0                |                         |

参数中的数字会自动转换为字符串形式。

另外，对于多个连续出现的常量字符串，也会执行连接操作。例如：

select 'My' 'SQL' '8.0';

📝MySQL 中的逻辑或运算符（||）在启用 SQL 模式 PIPES_AS_CONCAT 时也表示连接字符串。

CONCAT_WS(separator,str1,str2,...)) 函数使用指定的分隔符 separator 连接多个字符串，分隔符为 NULL 则返回 NULL。例如：

select concat_ws('-', 'My', null, 'SQL'), concat_ws(null, 'My', 'SQL');
concat_ws('-', 'My', null, 'SQL')|concat_ws(null, 'My', 'SQL')|
---------------------------------|----------------------------|
My-SQL                           |                            |

15.4 大小写转换

LOWER(str) 和 LCASE(str) 函数用于将字符串转换为小写形式。例如：

select lower('MySQL'), lcase('MySQL');
lower('MySQL')|lcase('MySQL')|
--------------|--------------|
mysql         |mysql         |

UPPER(str) 和 UCASE(str) 函数用于将字符串转换为大写形式。例如：

select upper('MySQL'), ucase('MySQL');
upper('MySQL')|ucase('MySQL')|
--------------|--------------|
MYSQL         |MYSQL         |

以上大小写转换函数不支持二进制字符串（BINARY、VARBINARY、BLOB)），可以将其转换为非二进制的字符串之后再进程处理。例如：

select lower(binary 'MySQL'), lower(CONVERT(binary 'MySQL' USING utf8mb4));
lower(binary 'MySQL')|lower(CONVERT(binary 'MySQL' USING utf8mb4))|
---------------------|--------------------------------------------|
MySQL                |mysql                                       |

15.5 查找子串位置

INSTR(str,substr) 函数返回子串 substr 在字符串 str 中第一次出现的索引位置，没有找到子串时返回 0。例如：

select instr('MySQL', 'SQL'), instr('Oracle', 'SQL'), instr('MySQL', ''), instr('MySQL', null);
instr('MySQL', 'SQL')|instr('Oracle', 'SQL')|instr('MySQL', '')|instr('MySQL', null)|
---------------------|----------------------|------------------|--------------------|
                    3|                     0|                 1|                    |

LOCATE(substr,str) 函数返回子串 substr 在字符串 str 中第一次出现的索引位置，和 INSTR(str,substr) 函数唯一的不同就是参数的顺序相反；LOCATE(substr,str,pos) 函数返回子串 substr 在字符串 str 中从位置 pos 开始第一次出现的索引位置。例如：

select locate('SQL', 'MySQL'), locate('SQL', 'MySQL vs PostgreSQL', 6);
locate('SQL', 'MySQL')|locate('SQL', 'MySQL vs PostgreSQL', 6)|
----------------------|---------------------------------------|
                     3|                                     17|

第二个 locate() 函数从第 6 个字符开始查找子串“SQL”。

📝MySQL 提供了基于正则表达式的子串位置查找函数 REGEXP_INSTR()，可以实现更高复杂的功能。

FIELD(str,str1,str2,str3,...) 函数返回字符串 str 在后续字符串列表中出现的位置，没有找到时返回 0。例如：

select field('Friday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') as dow;
dow|
---|
  5|

FIND_IN_SET(str,strlist) 函数返回字符串 str 在列表字符串 strlist 中出现的位置，strlist 由 N 个子串使用逗号分隔组成。例如：

select find_in_set('Friday', 'Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday') as dow;
dow|
---|
  5|

15.6 返回子串

SUBSTRING(str,pos)、SUBSTRING(str FROM pos)、SUBSTRING(str,pos,len) 以及 SUBSTRING(str FROM pos FOR len) 函数都可以用于返回从指定位置 pos 开始的子串，len 表示返回子串的长度；pos 为 0 表示返回空字符串。例如：

select substring('MySQL', 3), substring('MySQL' from 3 for 3), substring('MySQL', 0);
substring('MySQL', 3)|substring('MySQL' from 3 for 3)|substring('MySQL', 0)|
---------------------|-------------------------------|---------------------|
SQL                  |SQL                            |                     |

位置参数 pos 可以为负数，此时返回的子串从字符串右侧第 pos 个字符开始。例如：

select substring('MySQL', -5, 2), substring('MySQL' from -3);
substring('MySQL', -5, 2)|substring('MySQL' from -3)|
-------------------------|--------------------------|
My                       |SQL                       |

SUBSTR() 和 **MID()**函数是 SUBSTRING() 函数的同义词，也支持以上 4 种形式。例如：

select substr('MySQL', 3), mid('MySQL' from 3 for 3);
substr('MySQL', 3)|mid('MySQL' from 3 for 3)|
------------------|-------------------------|
SQL               |SQL                      |

SUBSTRING_INDEX(str,delim,count) 函数返回第 count 次出现的分隔符 delim 之前的子串。如果 count 为正数，从左侧开始计数并返回左侧的所有字符；如果 count 为负数，从右侧开始计数并返回右侧的所有字符。例如：

select substring_index('www.mysql.com', '.', 2), substring_index('www.mysql.com', '.', -2);
substring_index('www.mysql.com', '.', 2)|substring_index('www.mysql.com', '.', -2)|
----------------------------------------|-----------------------------------------|
www.mysql                               |mysql.com                                |

📝MySQL 提供了基于正则表达式的子串返回函数 REGEXP_SUBSTR()，可以实现更高复杂的功能。

LEFT(str,len) 函数返回字符串 str 左侧的 len 个字符，RIGHT(str,len) 函数返回字符串 str 右侧的 len 个字符。例如：

select left('MySQL', 2), right('MySQL', 3);
left('MySQL', 2)|right('MySQL', 3)|
----------------|-----------------|
My              |SQL              |

LTRIM(str) 函数返回删除字符串 str 左侧所有空格之后的子串，RTRIM(str) 函数返回删除字符串 str 右侧所有空格之后的子串。例如：

select ltrim('  my sql  ') as lt, rtrim('  my sql  ') as rt;
lt      |rt      |
--------|--------|
my sql  |  my sql|

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) 和 TRIM([remstr FROM] str) 函数返回删除字符串 str 两侧/左侧/右侧所有 remstr 字符串之后的子串，默认删除两侧字符串（BOTH），remstr 默认为空格。例如：

select trim('  my sql  ') as tr, trim(both '1' from '111mysql111') as tr;
tr    |tr   |
------|-----|
my sql|mysql|

ELT(N,str1,str2,str3,...) 函数返回字符串列表中的第 N 个元素，如果 N 的值小于 1 或者大于列表中的字符个数则返回 NULL。例如：

select elt(7, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') as dow;
dow   |
------|
Sunday|

15.7 替换子串

REPLACE(str,from_str,to_str) 函数用于将字符串 str 中的所有 from_str 替换为 to_str，返回替换后的字符串。例如：

select replace('www.mysql.net', 'net', 'com'), replace('mysql', 'my', '');
replace('www.mysql.net', 'net', 'com')|replace('mysql', 'my', '')|
--------------------------------------|--------------------------|
www.mysql.com                         |sql                       |

INSERT(str,pos,len,newstr) 函数用于在字符串 str 的指定位置 pos 之后插入子串 newstr，替换随后的 len 个字符。例如：

select insert('mysql.com', 7, 3, 'cn'), insert('mysql.com', 10, 3, 'cn');
insert('mysql.com', 7, 3, 'cn')|insert('mysql.com', 10, 3, 'cn')|
-------------------------------|--------------------------------|
mysql.cn                       |mysql.com                       |

第二个 insert() 函数中的 pos 不在有效范围之内，返回原始字符串。

REVERSE(str) 函数用于将字符串 str 中的字符顺序进行反转。例如：

select reverse('123');
reverse('123')|
--------------|
321           |

📝MySQL 提供了基于正则表达式的子串替换函数 REGEXP_REPLACE()，可以实现更高复杂的功能。

15.8 填充字符串

LPAD(str,len,padstr) 函数在字符串 str 的左侧使用 padstr 进行填充，直到长度为 len；RPAD(str,len,padstr) 函数在字符串 str 的右侧使用 padstr 进行填充，直到长度为 len。例如：

select lpad('MySQL 8.0', 12,'-') as lpad, lpad('MySQL 8.0', 5,'-') as lpad,
       rpad('MySQL 8.0', 12,'-') as rpad, rpad('MySQL 8.0', 5,'-') as rpad;
lpad        |lpad |rpad        |rpad |
------------|-----|------------|-----|
---MySQL 8.0|MySQL|MySQL 8.0---|MySQL|

当字符串 str 的长度大于 len 时，相当于从右侧截断字符串。

REPEAT(str,count) 函数用于将字符串 str 复制 count 次并返回结果。例如：

select repeat('🔥', 3);
repeat('🔥', 3)|
--------------|
🔥🔥🔥        |

SPACE(N) 函数返回一个由 N 个空格组成的字符串。例如：

select concat('my', space(5), 'sql') as space;
space     |
----------|
my     sql|

15.9 字符串比较

STRCMP(expr1,expr2) 函数用于比较两个字符串。如果两者相同则返回 0，如果第一个字符串小于（按照排序规则）第二个字符串则返回 -1，否则返回 1。例如：

select strcmp('mysql', 'MySQL') as strcmp, strcmp('mysql' collate utf8mb4_0900_as_cs, 'MySQL') as strcmp;
strcmp|strcmp|
------|------|
     0|    -1|

第一个函数使用 utf8mb4 默认的排序规则 utf8mb4_0900_ai_ci，不区分大小写；第二个函数使用排序规则 utf8mb4_0900_as_cs，区分大小写。

比较运算符（<、>、=、!= 等）也可以直接用于比较两个字符串，例如：

select 'mysql' = 'MySQL', 'mysql' != 'MySQL' collate utf8mb4_0900_as_cs as cmp;
'mysql' = 'MySQL'|cmp|
-----------------|---|
                1|  1|

📝除了 STRCMP() 函数之外，也可以使用 LIKE 和 NOT LIKE 运算符执行简单的模式匹配。

15.10 Base64 编码与解码

TO_BASE64(str) 函数用于将字符串 str 进行 Base64 编码，FROM_BASE64(str) 函数用于将 Base64 编码后的字符串解码成二进制字符串。例如：

select to_base64('MySQL'), from_base64('TXlTUUw=');
to_base64('MySQL')|from_base64('TXlTUUw=')|
------------------|-----------------------|
TXlTUUw=          |MySQL                  |