一步一步学MySQL---18 MySQL常用函数（1）

18.1 字符串函数

MySQL所支持的字符串函数有：

函数	功能
CANCAT(str1,str2,…strn)	连接字符串str1、str2、……、strn为一个完整字符串
INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr
LOWER(str)	将字符串str中所有字符变为小写
UPPER(str)	将字符串str中所有字符变为大写
LEFT(str,x)	返回字符串str最左边的x个字符
RIGHT(str,x)	返回字符串str最右边的x个字符
LPAD(str,n,pad)	使用字符串pad对字符串str最左边进行填充，直到长度为n个字符长度
RPAD(str,n,pad)	使用字符串pad对字符串str最右边进行填充，直到长度为n个字符长度
LTRIM(str)	去掉字符串str左边的空格
RTRIM(str)	去掉字符串str右边的空格
REPEAT(str,x)	返回字符串str重复z次的结果
REPLACE(str,a,b)	使用字符串b替换字符串str中所有出现的字符串a
STRCMP(str1,str2)	比较字符串str1和str2
TRIM(str)	去掉字符串str行头和行尾的空格
SUBSTRING(str,x,y)	返回字符串str中从x位置起y个字符串长度的字符串

18.1.1 合并字符串函数CONCAT()和CONCAT_WS()

语法：

CONCAT(S1,S2,…,SN) 或者 CONCAT_WS(SEP,S1,S2,…,SN)

（1）mysql> select concat(“My”,”S”,”QL”) 合并后的字符串;

（2）mysql> select concat(“My”,”S”,”QL”,NULL) 合并后的字符串;

（3）mysql> select concat(curdate(),18.35) 合并后的字符串;

（4）
mysql> select concat_ws(‘|’,curdate(),18.35) 合并后的字符串;

mysql> select concat_ws(‘-‘,’0514’,88461234) 合并后的字符串;

mysql> select concat_ws(NULL,’0514’,88461234) 合并后的字符串;

mysql> select concat_ws(‘-‘,’0514’,NULL,88461234) 合并后的字符串;

说明：

（1）当函数CONCAT()传入的参数中有一个NULL时，返回的结果将为NULL。

（2）函数CONCAT()不仅可以接受字符串参数，还可以接受其他类型的参数。

（3）函数CONCAT_WS()是函数CONCAT()的特殊形式，其形如CONCAT_WS(SEP,s1,s2,…,sn)，与CONCAT()相比，多了一个表示分隔符的SEP参数，即不仅将传入的参数连接起来，而且还会通过分隔符将各个字符串隔开。

（4）函数CONCAT_WS()中分隔符可以是一个字符串，也可以是其他参数。如果分隔符是NULL，则返回的结果将为NULL。

（5）函数CONCAT_WS()中当分隔符参数后的值存在NULL时，返回的结果将忽略NULL。

18.1.2 比较字符串大小的函数STRCMP()

语法：

STRCMP(str1,str2)

说明：如果参数str1大于str2，则结果返回1；如果参数str1小于str2，则结果返回-1；如果参数str1等于str2，则结果返回0。

mysql> select strcmp(“abc”,”abd”),
-> strcmp(“abc”,”abc”),
-> strcmp(“abc”,”abb”);

18.1.3 获取字符串长度函数LENGTH()和字符数函数CHAR_LENGTH()

语法：

LENGTH(str) 或者 CHAR_LENGTH(str)

---

mysql> select “MySQL” 英文字符串,
-> length(“MySQL”) 字符串字节长度,
-> char_length(“MySQL”) 字符串字符数,
-> “上海浦东” 中文字符串,
-> length(“上海浦东”) 字符串字节长度,
-> char_length(“上海浦东”) 字符串字符数;

说明：MySQL中，英文字符占1个字节；对于汉字字符，如果用的字符集是GBK，则汉字字符占2个字节，如果用的字符集是UTF-8，则汉字字符占3个字节。由于本机使用的默认字符集是UTF-8

查看MySQl默认字符集命令：

mysql> SHOW VARIABLES LIKE ‘character%’;

mysql> SHOW VARIABLES LIKE ‘collation_%’;

18.1.4 实现字母大小写转换函数UPPER()和LOWER()

MySQL中可以通过UPPER()和UCASE()函数实现将字符串的所有字母转换成大写字母。
语法：

UPPER(s) 或者 UCASE(s)

---

mysql> select “mysql” 字符串,
-> upper(‘mysql’) 转换后字符串,
-> ucase(“mysql”) 转换后字符串;

MySQL中可以通过LOWER()和LCASE()函数实现将字符串的所有字母转换成大写字母。
语法：

LOWER(s) 或者 LCASE(s)

---

mysql> select ‘MYSQL’ 字符串,
-> lower(“MYSQL”) 转换后字符串,
-> lcase(‘MYSQL’) 转换后字符串;

18.1.5 返回字符串位置的函数FIND_IN_SET()

MySQL中通过函数FIND_IN_SET()来获取相匹配字符串的位置。

语法：

FIND_IN_SET(str1,str2)

说明：上述函数返回字符串str2中与str1相匹配的字符串位置，参数str2中将包含若干个用逗号隔开的字符串。

mysql>select find_in_set(‘ShangHai’,’BeiJing,TianJin,NanJing,ShangHai’);

18.1.6 返回指定字符串位置的函数FIELD()

MySQL中通过函数FIELD()来获取相匹配字符串的位置

语法：

FIELD(str,str1,str2,…)

说明：上述函数返回第一个与字符串str相匹配的字符串的位置

mysql> select field(‘ShangHai’,’BeiJing’,’TianJin’,’NanJing’,’ShangHai’);

18.1.7 返回字符串相匹配的开始位置

MySQL中通过三个函数来获取子字符串相匹配的开始位置，分别是LOCATE()、POSITION()和INSTR()。

语法：

LOCATE(str1,str) //返回参数str中字符串str1的开始位置

POSITION(str1 in str) //返回参数str中字符串str1的开始位置

INSTR(str, str1) //返回参数str中字符串str1的开始位置

---

mysql> select locate(‘SQL’, ‘MySQl’),
-> position(‘SQL’ in ‘MySQL’),
-> instr(‘MySQL’, ‘SQL’);

18.1.8 返回指定位置的字符串ELT()函数

MySQL中通过函数ELT()来获取指定位置的字符串。

语法：

ELT(n,str1,str2…) //返回第n和字符串

---

mysql> select elt(3,’ShangHai’,’BeiJing’,’TianJin’,’NanJing’,’HangZhou’);

18.1.9 选择字符串的MAKE_SET()函数

MySQL中通过函数MAKE_SET()来获取字符串。

语法：

MAKE_SET(num,str1,str2,..strn)

说明：上述函数首先将num转换成二进制数，然后按照二进制数从参数str1，str2，…，strn中选择相应的字符串。在通过二进制数来选择字符串时，按从右到左的顺序读取该值，如果值为1，则选择该字符串，否则不选该字符串。

例如：

mysql> select bin(5) 二进制数,make_set(5,’ShangHai’,’BeiJing’,’Tianjin’,’NanJing’,’HangZhou’),
-> bin(7) 二进制数,make_set(7,’ShangHai’,’BeiJing’,’Tianjin’,’NanJing’,’HangZhou’);

说明：由于数值5的二进制数为101，所以选择第一个和第三个字符串；数值7的二进制数为111，所以选择第一个、第二个和第三个字符串

18.1.10 从现有字符串中截取子字符串

（1）从左边或右边截取子字符串
语法：

LEFT(str,num) //返回字符串str中的包含前num个字母（从左边数）的字符串
RIGHT(str,num) //返回字符串str中的包含后num个字母（从右边数）的字符串

例如：

mysql> select “MySQL” 字符串,
-> left(“MySQL”,2) 前2个字母的字符串,
-> right(“MySQL”,3) 后3个字母的字符串;

（2）截取指定位置和长度的子字符串
语法：

SUBSTRING(str,num,len) 和MID(str,num,len) //返回字符串str中的第num个位置开始长度为len的子字符串

例如：

mysql> select “oraclemysql” 字符串,
-> substring(“oraclemysql”,7,5) 截取的子字符串,
-> mid(“oraclemysql”,7,5) 截取的子字符串;

18.1.11 去除字符串首尾的空格

（1）去除字符串开始处的空格

语法：

LTRIM(str) //返回去掉开始处空格的字符串

例如：

执行SQl语句LTRIM()函数，去除掉字符串“ MySQL ”左边的空格。在具体处理时，操作的字符串为“ MySQL ”，该字符串的左右两边各有一个空格。因为空格显示不太明显，所以在该字符串左右两边与字符“-”连接起来。

mysql> select concat(‘-‘,’ MySQL ‘,’-‘) 原来的字符串,
-> char_length(concat(‘-‘,’ MySQL ‘,’-‘)) 原来的字符串长度,
-> concat(‘-‘,ltrim(’ MySQL ‘),’-‘) 去除开始空格后的字符串,
-> char_length(concat(‘-‘,ltrim(’ MySQL ‘),’-‘)) 去除开始空格后的字符串长度

（2）去除字符串结尾处的空格

语法：

RTRIM(str) //返回去掉结尾处空格的字符串

例如：

执行SQl语句RTRIM()函数，去除掉字符串“ MySQL ”左边的空格。在具体处理时，操作的字符串为“ MySQL ”，该字符串的左右两边各有一个空格。因为空格显示不太明显，所以在该字符串左右两边与字符“-”连接起来。

mysql> select concat(‘-‘,’ MySQL ‘,’-‘) 原来的字符串,
-> char_length(concat(‘-‘,’ MySQL ‘,’-‘)) 原来的字符串长度,
-> concat(‘-‘,rtrim(’ MySQL ‘),’-‘) 去除结尾空格后的字符串,
-> char_length(concat(‘-‘,rtrim(’ MySQL ‘),’-‘)) 去除结尾空格后的字符串长度;

（3）去除字符串首尾处的空格

语法：

TRIM(str) //返回去掉结尾处空格的字符串

例如：

执行SQl语句TRIM()函数，去除掉字符串“ MySQL ”左边的空格。在具体处理时，操作的字符串为“ MySQL ”，该字符串的左右两边各有一个空格。因为空格显示不太明显，所以在该字符串左右两边与字符“-”连接起来。

mysql> select concat(‘-‘,’ MySQL ‘,’-‘) 原来的字符串,
-> char_length(concat(‘-‘,’ MySQL ‘,’-‘)) 原来的字符串长度,
-> concat(‘-‘,trim(’ MySQL ‘),’-‘) 去除首尾空格后的字符串,
-> char_length(concat(‘-‘,trim(’ MySQL ‘),’-‘)) 去除首尾空格后的字符串长度;

18.1.12 替换字符串

（1）使用INSERT()函数

语法：

INSERT(str,pos,len,newstr) //将字符串str中的pos位置开始的len长度的字符串用newstr字符串替换

注意：

• 如果参数pos的值超过str字符串长度，则返回值为原始字符串str；
• 如果len的长度大于原来字符串str中所剩字符串的长度，则从位置pos开始进行全部替换；
• 若任何一个参数为NULL，则返回值为NULL。

例如：

mysql> select “这里是MySQl数据库管理系统” 字符串,
-> insert(“这里是MySQl数据库管理系统”,4,5,”Redis”) 转换后的字符串;

mysql> select “这里是MySQl数据库管理系统” 字符串,
-> insert(“这里是MySQl数据库管理系统”,16,5,”Redis”) 转换后的字符串;

mysql> select “这里是MySQl数据库管理系统” 字符串,
-> insert(“这里是MySQl数据库管理系统”,17,5,”Redis”) 转换后的字符串;

注意：我们看到字符串“这里是MySQL数据库管理系统”一共有15个字符，为什么我们从第16和第17位置处（均大于15）替换的结果不一样？这是因为，MySQL中，字符串的末尾有一个‘\0’字符，所以从第16位置处替换还没有超出原来字符串的长度，而从第17位置处替换就是超出了原来字符串的长度了，所以后者返回原来的字符串。

mysql> select “这里是MySQl数据库管理系统” 字符串,
-> insert(“这里是MySQl数据库管理系统”,4,14,”Redis”) 转换后的字符串;

（2）使用REPLACE()函数

语法：

REPLACE(str,substr,newstr) //将str中的子字符串substr用新字符串newstr替换

例如：

mysql> select “这里是MySQl数据库管理系统” 字符串,
-> replace(“这里是MySQl数据库管理系统”,”MySQl”,”Redis”) 替换后的字符;