MySQL中常用的字符串函数

1. 字符串拼接

concat(str1,str2,...)：将str1，str2...等多个字符串拼接成一个长字符串，如果有任何一个参数为NULL，则返回值为NULL

concat_ws(separator, str1, str2, ...)：指定分隔符，将多个字符串拼接成一个长字符串，如果有任何一个参数（包括分隔符）为NULL，则返回值为NULL

group_concat(distinct 要连接的字段 order by 排序字段 separator '分隔符')：搭配group by使用，将分组后产生的，属于同一个分组中的值连接起来，返回一个长字符串结果（f分隔符默认为逗号）

# 准备测试数据 --student表
CREATE TABLE `student` (
   `id` int(11) NOT NULL AUTO_INCREMENT,   #--id
   `stuName` varchar(22) DEFAULT NULL,     #--学生姓名
   `course` varchar(22) DEFAULT NULL,      #--学习科目
   `score` int(11) DEFAULT NULL,           #--学分
   PRIMARY KEY (`id`)                      #--设置主键
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
# 插入数据
INSERT INTO `student`(stuName,course,score) VALUES ('张三', '语文', '91');
INSERT INTO `student`(stuName,course,score) VALUES ('张三', '数学', '90');
INSERT INTO `student`(stuName,course,score) VALUES ('张三', '英语', '87');
INSERT INTO `student`(stuName,course,score) VALUES ('李四', '语文', '79');
INSERT INTO `student`(stuName,course,score) VALUES ('李四', '数学', '95');
INSERT INTO `student`(stuName,course,score) VALUES ('李四', '英语', '80');
INSERT INTO `student`(stuName,course,score) VALUES ('王五', '语文', '77');
INSERT INTO `student`(stuName,course,score) VALUES ('王五', '数学', '81');
INSERT INTO `student`(stuName,course,score) VALUES ('王五', '英语', '89');
# 浏览数据
mysql> select * from student;
+----+---------+--------+-------+
| id | stuName | course | score |
+----+---------+--------+-------+
| 10 | 张三    | 语文    |    91 |
| 11 | 张三    | 数学    |    90 |
| 12 | 张三    | 英语    |    87 |
| 13 | 李四    | 语文    |    79 |
| 14 | 李四    | 数学    |    95 |
| 15 | 李四    | 英语    |    80 |
| 16 | 王五    | 语文    |    77 |
| 17 | 王五    | 数学    |    81 |
| 18 | 王五    | 英语    |    89 |
+----+---------+------- +-------+
# concat_ws()函数和group_concat()搭配使用
select 
stuName as ‘姓名’,group_concat(concat_ws(':',course,score) order by score separator ',') as '成绩'
from student
group by stuName;
# 查询返回结果
+--------------+-------------------------------+
| ‘姓名’        | 成绩                          |
+--------------+-------------------------------+
| 张三          | 英语:87,数学:90,语文:91        |
| 李四          | 语文:79,英语:80,数学:95        |
| 王五          | 语文:77,数学:81,英语:89        |
+--------------+-------------------------------+　

2. 字符串替换

insert(str,x,y,instr)：将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串instr

replace(str,x,y): 将字符串str中的x替换成y

mysql> select insert('aaaaab',2,5,1),replace('aaaaab','a',1);
+------------------------+-------------------------+
| insert('aaaaab',2,5,1) | replace('aaaaab','a',1) |
+------------------------+-------------------------+
| a1                     | 11111b                  |
+------------------------+-------------------------+

3. 字符串填充

lpad(str,x,instr)：向字符串str的最左侧填充字符串instr，直到字符串长度达到x

rpad(str,x,instr)：向字符串str的最右端填充字符串instr，直到字符串长度达到x

如果当原字符串的长度>给定的填充后的字符串长度，返回结果则是从左往右取x位字符

mysql> select lpad('aa',6,'b'),rpad('aa',6,'b'),lpad('aaaaaa',2,'b'),rpad('aaaaaa',2,'b');
+------------------+------------------+----------------------+----------------------+
| lpad('aa',6,'b') | rpad('aa',6,'b') | lpad('aaaaaa',2,'b') | rpad('aaaaaa',2,'b') |
+------------------+------------------+----------------------+----------------------+
| bbbbaa           | aabbbb           | aa                   | aa                   |
+------------------+------------------+----------------------+----------------------+

4. 字符串去空格/字符

trim(leading/trailing/both instr from str)：去除字符串str最左端/最右端/两端的字符串instr，或者空格

ltrim(str)：去除字符串左端空格

rtrim(str)：去除字符串右端空格

mysql> select trim(leading 'a' from 'abca'),trim(leading 'ab' from 'abca');
+-------------------------------+--------------------------------+
| trim(leading 'a' from 'abca') | trim(leading 'ab' from 'abca') |
+-------------------------------+--------------------------------+
| bca                           | ca                             |
+-------------------------------+--------------------------------+

5. 返回字符串的最左/右端字符串

left(str,x)：返回字符串str最左端x个字符

right(str,x)：返回字符串str最右端x个字符

mysql> select left('abc',1),right('abc',1);
+---------------+----------------+
| left('abc',1) | right('abc',1) |
+---------------+----------------+
| a             | c              |
+---------------+----------------+

6. 截取字符串

substring(str,x,y)：截取字符串str，从x开始，截取y位

mysql> select substring('abcdef',2,3)：
+-------------------------+
| substring('abcdef',2,3) |
+-------------------------+
| bcd                     |
+-------------------------+

7. 查找字符串位置

instr(str,instr)：查找字符串instr第一次出现的位置

locate(instr,str,pos)：从pos开始查找字符串instr第一次出现的位置

position(instr in str)：查找字符串str第一次出现的位置

mysql> select instr('abc','b'),locate('b','abc',1),position('b' in 'abc');
+------------------+---------------------+------------------------+
| instr('abc','b') | locate('b','abc',1) | position('b' in 'abc') |
+------------------+---------------------+------------------------+
|                2 |                   2 |                      2 |
+------------------+---------------------+------------------------+

8. 返回字符串的字符长度/字节长度

length(str)：返回字符串str的字节长度。不同的字符集编码下，字符和字节的对应关系不同，在utf8规则下，一个汉字字符 =3 个字节，gbk：一个汉字字符 = 2个字节。

char_length(str)：返回字符串str的字符长度。一个汉字，数字，字母或者符号就是一个字符　　

# 字符集编码为utf8
mysql> select length('数据库'),char_length('数据库');
+---------------------+--------------------------+
| length('数据库')    | char_length('数据库')    |
+---------------------+--------------------------+
|                   9 |                        3 |
+---------------------+--------------------------+

9. 比较两个字符的大小

strcmp(str1,str2)：比较两个字符的ascii值大小，如果str1>str2,则返回1；str1<str2,返回-1；str1=str2，返回0；

mysql> select strcmp('a','9'),strcmp('z','您');
+-----------------+-------------------+
| strcmp('a','9') | strcmp('z','您')  |
+-----------------+-------------------+
|               1 |                -1 |
+-----------------+-------------------+
1 row in set (0.00 sec)