返回
网站建设知识
MySQL笔记四之字符串函数
2025-07-22 11:13  点击:1

MySQL笔记四之 字符串函数

1.计算字符串字符数的函数和字符串长度的函数
2.合并字符串函数ConCAT(s1,s2,…)、CONCAT_WS(x,s1,s2,…)
3.替换字符串的函数INSERT(s1,x,len,s2)
4.字母大小转换函数
5.获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)
6.填充字符串的函数LPAD(s1,len,s2)和RPAD(s1,len,s2)
7.删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)
8.删除指定字符串的函数TRIM(s1 FROM s)
9.重复生成字符串的函数REPEAT(s,n)
10.空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)
11.比较字符串大小的函数STRCMP(s1,s2)
12.获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)
13.匹配子串开始位置的函数
14.字符串逆序的函数REVERSE(s)
15.返回指定位置的字符串的函数
16.返回指定字符串位置的函数FIELD(s,s1,s2,…)
17.返回子串位置的函数FIND_IN_SET(s1,s2)
18.选取字符串的函数MAKE_SET(x,s1,s2,…)

字符串函数主要用来处理数据库中的字符串数据，MySQL中字符串函数有：计算字符串长度函数、字符串合并函数、字符串替换函数、字符串比较函数、查找指定字符串位置函数等。

1.计算字符串字符数的函数和字符串长度的函数

mysql> SELECT CHAR_LENGTH('hello'),CHAR_LENGTH('egg');+----------------------+--------------------+| CHAR_LENGTH('hello') | CHAR_LENGTH('egg') |+----------------------+--------------------+|                    5 |                  3 |+----------------------+--------------------+1 row in set (0.00 sec)

总结：CHAR_LENGTH(str)返回值为字符串str所包含的字符个数。一个多字节字符算作一个单字符。

mysql> SELECT LENGTH('hello'),LENGTH('egg');+-----------------+---------------+| LENGTH('hello') | LENGTH('egg') |+-----------------+---------------+|               5 |             3 |+-----------------+---------------+1 row in set (0.00 sec)

总结：LENGTH(str)返回值为字符串的字节长度，使用utf8编码字符集时，一个汉字是3个字节，一个数字或者字母算一个字节。

2.合并字符串函数ConCAT(s1,s2,…)、CONCAT_WS(x,s1,s2,…)

mysql> SELECT ConCAT('My SQL','5.6'),ConCAT('My',NULL,'SQL');+------------------------+-------------------------+| ConCAT('My SQL','5.6') | ConCAT('My',NULL,'SQL') |+------------------------+-------------------------+| My SQL5.6              | NULL                    |+------------------------+-------------------------+1 row in set (0.05 sec)

总结：
a.ConCAT(s1,s2,…)返回结果为连接参数产生的字符串,一个或多个参数。
b.有任何一个参数为NULL,则返回值为NULL.
c.如果所有参数均为非二进制字符串，则结果为非二进制字符串。
d.如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。

mysql> SELECT CONCAT_WS('-','1st','2nd','3rd'),CONCAT_WS('*','1st',NULL,'3rd');+----------------------------------+---------------------------------+| CONCAT_WS('-','1st','2nd','3rd') | CONCAT_WS('*','1st',NULL,'3rd') |+----------------------------------+---------------------------------+| 1st-2nd-3rd                      | 1st*3rd                         |+----------------------------------+---------------------------------+1 row in set (0.00 sec)

总结：
a.CONCAT_WS代表ConCAT WITH Separator，是ConCAT()的特殊形式。
b.第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。
c.分隔符可以是一个字符串，也可以是其他参数。
d.如果分隔符为NULL,则结果为NULL。
f.函数会忽略任何分隔符参数后的NULL值。

3.替换字符串的函数INSERT(s1,x,len,s2)

mysql> SELECT INSERT('Quest',2,4,'What') AS col1,    -> INSERT('Quest',-1,4,'What') AS col2,    -> INSERT('Quest',3,100,'Wh') AS col3;+-------+-------+------+| col1  | col2  | col3 |+-------+-------+------+| QWhat | Quest | QuWh |+-------+-------+------+1 row in set (0.00 sec)

总结：
a.INSERT(s1,x,len,s2)返回字符串s1,其子字符串起始于x位置和被字符串s2取代的len字符。
b.如果x超过字符串长度，则返回值为原始字符串。
c.假如len的长度大于其他字符串的长度，则从位置x开始替换。替换s2的所有。
d.若任何一个参数为NULL，则返回值为NULL。

4.字母大小转换函数

mysql> SELECT LOWER('BEAUTIFUL'),LCASE('Well');+--------------------+---------------+| LOWER('BEAUTIFUL') | LCASE('Well') |+--------------------+---------------+| beautiful          | well          |+--------------------+---------------+1 row in set (0.00 sec)mysql> SELECT UPPER('black'),UCASE('BLacK');+----------------+----------------+| UPPER('black') | UCASE('BLacK') |+----------------+----------------+| BLACK          | BLACK          |+----------------+----------------+1 row in set (0.00 sec)

总结：
a.LOWER(str)或者LCASE(str)可以将字符串str中的字母字符全部转换成小写字母。
b.UPPER(str)或者UCASE(str)函数将字符串中所有字母字符转换成大写字母。

5.获取指定长度的字符串的函数LEFT(s,n)和RIGHT(s,n)

mysql> SELECT LEFT('football',5);+--------------------+| LEFT('football',5) |+--------------------+| footb              |+--------------------+1 row in set (0.00 sec)mysql> SELECT RIGHT('football',4);+---------------------+| RIGHT('football',4) |+---------------------+| ball                |+---------------------+1 row in set (0.00 sec)

总结：
a.LEFT(s,n)返回字符串s开始的最左边n个字符。
b.RIGHT(s,n)返回字符串str最右边n个字符。

6.填充字符串的函数LPAD(s1,len,s2)和RPAD(s1,len,s2)

mysql> SELECT LPAD('hello',4,'??'),LPAD('hello',10,'??');+----------------------+-----------------------+| LPAD('hello',4,'??') | LPAD('hello',10,'??') |+----------------------+-----------------------+| hell                 | ?????hello            |+----------------------+-----------------------+1 row in set (0.00 sec)mysql> SELECT RPAD('hello',4,'?'),RPAD('hello',10,'?');+---------------------+----------------------+| RPAD('hello',4,'?') | RPAD('hello',10,'?') |+---------------------+----------------------+| hell                | hello?????           |+---------------------+----------------------+1 row in set (0.00 sec)

总结：
a.LPAD(s1,len,s2)返回字符串s1，其左边由字符串s2填补到len字符串长度。假如s1的长度大于len，则返回值被缩短至len字符。
b.PRAD(s1,len,s2)返回字符串s1,其右边被字符串s2填补至len字符长度。假如字符串s1的长度大于len,则返回值被缩短至len字符长度。

解释：
字符串“hello”长度大于4，不需要填充，因此LPAD(‘hello’,4,’??’)只返回被缩短的长度为4的字符串hell;字符串“hello”长度小于10，LPAD(‘hello’,10,’??’)返回结果为“?????hello”,左侧填充“?”,长度为10；

7.删除空格的函数LTRIM(s)、RTRIM(s)和TRIM(s)

mysql> SELECT '(  book  )',ConCAT('(',LTRIM('  book  '),')');+------------+-----------------------------------+| (  book  ) | ConCAT('(',LTRIM('  book  '),')') |+------------+-----------------------------------+| (  book  ) | (book  )                          |+------------+-----------------------------------+1 row in set (0.00 sec)mysql> SELECT '(  book  )',ConCAT('(',RTRIM('  book  '),')');+------------+-----------------------------------+| (  book  ) | ConCAT('(',RTRIM('  book  '),')') |+------------+-----------------------------------+| (  book  ) | (  book)                          |+------------+-----------------------------------+1 row in set (0.01 sec)mysql> SELECT '(  book  )',ConCAT('(',TRIM('  book  '),')');+------------+----------------------------------+| (  book  ) | ConCAT('(',TRIM('  book  '),')') |+------------+----------------------------------+| (  book  ) | (book)                           |+------------+----------------------------------+1 row in set (0.00 sec)

总结：
a.LTRIM(s)返回字符串s,字符串左侧空格字符被删除。
b.RTRIM(s)返回字符串s,字符串右侧空格字符被删除。
c.TRIM(s) 删除字符串s两侧的空格。
d.字符串s中间的空格不删除。

8.删除指定字符串的函数TRIM(s1 FROM s)

mysql> SELECT TRIM('xy' FROM 'xyxksdjsxyxsdsjxxyxy');+----------------------------------------+| TRIM('xy' FROM 'xyxksdjsxyxsdsjxxyxy') |+----------------------------------------+| xksdjsxyxsdsjx                         |+----------------------------------------+1 row in set (0.09 sec)

总结：
a.TRIM(s1 FROM s2)函数删除字符串中两端指定的字符。
b.中间的字符串不删除。

9.重复生成字符串的函数REPEAT(s,n)

mysql> SELECT REPEAT('MySQL',3);+-------------------+| REPEAT('MySQL',3) |+-------------------+| MySQLMySQLMySQL   |+-------------------+1 row in set (0.01 sec)

总结：
a.REPEATE(s,n)返回一个有重复的字符串s组成的字符串，字符串s的数目等于n
b.若n<=0,则返回一个空字符串。
c.若s或n为NULL,则返回NULL。

10.空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)

mysql> SELECT ConCAT('(',SPACE(6),')');+--------------------------+| ConCAT('(',SPACE(6),')') |+--------------------------+| (      )                 |+--------------------------+1 row in set (0.00 sec)mysql> SELECT REPLACE('xxx.mysql','x','w');+----------------------------------+| REPLACE('xxx.mysql','x','w') |+----------------------------------+| mysql                    |+----------------------------------+1 row in set (0.00 sec)

总结：
a.SPACE(6)返回一个由n个空格组成的字符串。
b.REPLACE(s,s1,s2)使用字符串s2替代字符串s中所有的字符串s1。

11.比较字符串大小的函数STRCMP(s1,s2)

mysql> SELECT STRCMP('txt','txt2'),STRCMP('txt2','txt'),STRCMP('txt','txt');+----------------------+----------------------+---------------------+| STRCMP('txt','txt2') | STRCMP('txt2','txt') | STRCMP('txt','txt') |+----------------------+----------------------+---------------------+|                   -1 |                    1 |                   0 |+----------------------+----------------------+---------------------+1 row in set (0.02 sec)

总结：
STRCMP(s1,s2)若所有的字符串均相同，则返回0；若根据当前分类次序，第一个参数小于第二个，则返回-1，其他情况返回1.

12.获取子串的函数SUBSTRING(s,n,len)和MID(s,n,len)

mysql>  SELECT SUBSTRING('breakfast',5) AS col1,    ->  SUBSTRING('breakfast',5,3) AS col2,    -> SUBSTRING('lunch',-3) AS col3,    -> SUBSTRING('lunch',-5,3) AS col4;+-------+------+------+------+| col1  | col2 | col3 | col4 |+-------+------+------+------+| kfast | kfa  | nch  | lun  |+-------+------+------+------+1 row in set (0.00 sec)mysql> SELECT MID('breakfast',5) AS col1,    -> MID('breakfast',5,3) AS col2,    -> MID('lunch',-3) AS col3,    -> MID('lunch',-5,3) AS col4;+-------+------+------+------+| col1  | col2 | col3 | col4 |+-------+------+------+------+| kfast | kfa  | nch  | lun  |+-------+------+------+------+1 row in set (0.00 sec)

总结：
a.SUBSTRING(s,n,len)带有len参数的格式，从字符串s返回一个长度同len字符相同的子字符串。
b.起始于位置n,也可能对n使用一个负值，若为负值，则子字符串的位置起始于字符串结尾n字符，即倒数第n个字符，二不是字符串的开头位置。
c.MID(s,n,len)与SUBSTRING(s,n,len)的作用相同。

13.匹配子串开始位置的函数

mysql> SELECT LOCATE('ball','foatball'),POSITION('ball'IN'football'),INSTR('football','ball');+---------------------------+------------------------------+--------------------------+| LOCATE('ball','foatball') | POSITION('ball'IN'football') | INSTR('football','ball') |+---------------------------+------------------------------+--------------------------+|                         5 |                            5 |                        5 |+---------------------------+------------------------------+--------------------------+1 row in set (0.00 sec)

总结：
LOCATE(str1,str2)、POSITION(str1 IN str)和INSTR(str str1)3个函数的作用相同，返回字符串str1在字符串str的开始位置。

14.字符串逆序的函数REVERSE(s)

mysql> SELECT REVERSE('abc');+----------------+| REVERSE('abc') |+----------------+| cba            |+----------------+1 row in set (0.00 sec)

总结：
REVERSE(s)将字符串s反转，返回的字符串的顺序和s字符串顺序相反。

15.返回指定位置的字符串的函数

mysql> SELECT ELT(3,'1str','2nd','3rd'),ELT(3,'net','os');+---------------------------+-------------------+| ELT(3,'1str','2nd','3rd') | ELT(3,'net','os') |+---------------------------+-------------------+| 3rd                       | NULL              |+---------------------------+-------------------+1 row in set (0.00 sec)

总结：
ELT(N,字符串1,字符串2,字符串3,…,字符串M)若N=1，则返回字符串1，若N=2,则返回字符串2，依次类推。若N小于1或大于参数的数目，则返回NULL。

16.返回指定字符串位置的函数FIELD(s,s1,s2,…)

mysql> SELECT FIELD('Hi','hihi','Hey','Hi','bas') as col1,    -> FIELD('Hi','Hey','Lo','foo') as col1;+------+------+| col1 | col1 |+------+------+|    3 |    0 |+------+------+1 row in set (0.00 sec)

总结：
FIELD(s,s1,s2,…)返回字符串s在列表s1,s2,…中第一次出现的位置，在找不到s的情况下，返回值为0.如果s为NULL,则返回值为0，原因是NULL不能同任何值进行同等比较。

17.返回子串位置的函数FIND_IN_SET(s1,s2)

mysql> SELECT FIND_IN_SET('Hi','hihi,Hey,Hi,bas');+-------------------------------------+| FIND_IN_SET('Hi','hihi,Hey,Hi,bas') |+-------------------------------------+|                                   3 |+-------------------------------------+1 row in set (0.00 sec)

总结：
a.FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置，字符串列表是一个由多个逗号‘,’分开的字符串组成的列表。
b.如果s1不在s2或s2为空字符串，则返回值为0.
c.如果任意一个参数为NULL,则返回值为NULL。
d.这个函数在第一个参数包含一个逗号‘,’时将无法正常运行。

18.选取字符串的函数MAKE_SET(x,s1,s2,…)

mysql> SELECT MAKE_SET(1,'a','b','c') as col1,    -> MAKE_SET(1|4,'hello','nice','world') as col2,    -> MAKE_SET(1|4,'hello','nice',NULL,'world') as col3,    -> MAKE_SET(0,'a','b','c') as col4;+------+-------------+-------+------+| col1 | col2        | col3  | col4 |+------+-------------+-------+------+| a    | hello,world | hello |      |+------+-------------+-------+------+1 row in set (0.00 sec)

总结：
MAKE_SET(x,s1,s2,…)返回由x的二进制数指定的相应位的字符串组成的字符串，s1对应比特1,s2对应比特01以此类推。s1,s2…中的NULL值不会添加到结果中。

解释：
1的二进制为0001,4的二进制为0100,1与4进行或操作之后的二进制为0101，从右到左第1位和第3位为1。