mysql内置函数

文章目录

字符串操作字符串连接 （CONCAT）使用分隔符拼接字符串 CONCAT_WS分组，字符串连接 group_concat 字符串截取（SUBSTRING ）、 MID(s,n,len)字符串转小写 LCASE(s) LOWER(s)字符串转小写字符串去空格字符串查找 FIND_IN_SET字符串定位 LOCATE数字格式化 FORMAT(x,n) 数字函数求和求平均最值取整 高级函数类型转化 CASTif 相关ifIFNULLIF ELSE

字符串操作

字符串连接 （CONCAT）

语法：

CONCAT(s1,s2...sn)

经典使用方式之一在mybatis 中进行模糊匹配。

select CONCAT('%','#{name}','%');

注意：返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

select concat(u.id,u.email) as info from user u;

使用分隔符拼接字符串 CONCAT_WS

语法：

CONCAT_WS(seperator, s1,s2...sn) -- WS的缩写大概是指 with seperator

第一个参数是 分隔符

select CONCAT_WS('%','1','2','3'); --输出结果 1%2%3 select CONCAT_WS('%',id,name,email) from user;

可以看到使用CONAT_WS 进行拼接 即便数据库字段为null，也不会想函数concat一样直接返回 null

分组，字符串连接 group_concat

分组后某个字段拼接，group_concat 还支持去重（distinct）。

SELECT GROUP_CONCAT(r.role_name) roleNames, GROUP_CONCAT(r.role_id) roleIds, r.user_id FROM user_role r GROUP BY user_id

默认使用 ,做分隔符，还可以添加前缀和后缀。

SELECT GROUP_CONCAT(r.role_name) roleNames, GROUP_CONCAT('_id',r.role_id) roleIds, r.user_id FROM user_role r GROUP BY user_id;

字符串截取（SUBSTRING ）、 MID(s,n,len)

mysql 提供了如下的字符截取函数。其中 substr 是 substring的缩写

SUBSTRING 函数需要三个参数，第一个要操作的字符串，第二个参数 开始截取的字符串索引（从 1开始），第三个参数是要截取的长度。

MID 函数和SUBSTRING 功能和参数一致。

select SUBSTRING('123456789',1,6);; -- 输出结果：123456 select SUBSTR('123456789',1,6);; -- 输出结果：123456 select SUBSTR('123456789' FROM 2 FOR 2);-- 输出结果：23 select SUBSTRING('123456789',0,6); -- 输出结果 空

所以需要注意的是 字符串截取索引要从 1 开始。

字符串转小写 LCASE(s) LOWER(s)

SELECT LCASE('Hello'); -- hello SELECT LOWER('World'); -- world

字符串转小写

SELECT UPPER('qwe'); -- QWE SELECT UCASE('qwe'); ---QWE

字符串去空格

去前后空格

SELECT TRIM(' 123 '); -- 输出结果 123

去前空格

SELECT LTRIM(' 123 "'); -- 输出结果 123 "

去后空格

SELECT RTRIM('" 123 '); -- 输出结果 " 123

字符串查找 FIND_IN_SET

语法：

FIND_IN_SET(str1,str2)

str2：是一个字符串集合，每个元素使用,分隔。 函数的作用是返回在集合str2中和str1匹配元素的位置。

SELECT FIND_IN_SET('3','1,2,3,4,5') -- 返回结果 3 SELECT FIND_IN_SET('7','1,2,3,4,5') -- 返回结果 0 SELECT FIND_IN_SET('3','12,3'); -- 返回结果 2 SELECT FIND_IN_SET(null,'12'); -- 返回 null SELECT FIND_IN_SET('12',null); -- 返回 null

可以看到该函数并不是单纯的字符串匹配，而是将第二个字符串按照,拆分并匹配值。

与下面程序大致相同。

public Integer findInSet(String str1,String str2){ if(str1==null || str2==null) return null; String[] str2Array = str2.split(","); for(int i=0;i<str2Array.length;i++){ if(str1.equals(str2Array[i])) return i+1; } return 0; }

看到split 方法，就想到了 分隔连续的问题。

SELECT FIND_IN_SET('1','0,,1,2'); -- 返回 3

字符串定位 LOCATE

语法：

LOCATE(s1,s)

获取 字符s1 在字符串s 中的起始位置。匹配不到则返回0

select LOCATE('3','1,2,3'); -- 返回结果 5 select LOCATE('0','1,2,3'); -- 返回结果 0

上面就可以明显看到， locate方法 返回的结果是在字符串中的位置

数字格式化 FORMAT(x,n)

FORMAT(x,n)

函数可以将数字 x 进行千分位格式化（ "#,###,###"）, 并保留 n 位小数，最后一位四舍五入。

select FORMAT(1234.123,2); --输出结果 1,234.12

数字函数

求和

AVG(expression) -- 返回一个表达式的平均值，expression 是一个字段

对某个学生成绩求和

select sum(grade),stu_id from stu_grade group by stu_id;

求平均

SUM(expression) -- 返回一个表达式的平均值，expression 是一个字段

求某个学生的平均成绩

select sum(grade)/count(DISTINCT(`subject`)),stu_id from stu_grade group by stu_id;

最值

最大值：

MAX(expression) -- 返回字段 expression 中的最大值

最小值：

MIN(expression) -- 返回字段 expression 中的最小值

取整

向上取整

CEIL(x) select ceil(1.24); -- 2 select ceil(2.64); -- 3

向下取整

FLOOR(x) select FLOOR(1.24); -- 1 select FLOOR(2.64); -- 2

高级函数

类型转化 CAST

oracle 也支持该函数

CAST(x AS type) SELECT CAST('12.5' AS decimal) -- 13 SELECT CAST('12.5' AS decimal(12,2)) -- 12.5

不知道为什么，字符串转数字时，只有指定类型为 才能正常执行。（mysql - 5.7.21）

if 相关

if

使用场景：

和count连用 分别统计 用户1 和用户2 的角色总数

SELECT count(*) as total, count(IF(user_id =1, 1,null)) role1, count(IF(user_id = 2,1, null)) role2 FROM user_role

sum连用

SELECT count(*) as total, SUM(IF(user_id =1, 1,null)) role1, SUM(IF(user_id = 2,1, null)) role2 FROM user_role

因为没有使用groupby 语句，上面两个的输出结果都是一样的。

total | role1 | role2 3 | 1 | 2

IFNULL

IFNULL(expr1,expr2)

如果expr1表达式结果不为null，则返回结果为 expr1.否则结果为 expr2，返回结果是数字或字符串。

可以用于数据统计时定义默认值，如果统计结果是null，则返回0。

select IFNULL(SUM(null),0) sum; -- 0

IF ELSE

if实现条件判断，满足不同条件执行不同的操作，这个我们只要学编程的都知道if的作用了，下面我们来看看mysql 存储过程中的if是如何使用的吧。

IF search_condition THEN statement_list [ELSEIF search_condition THEN] statement_list ... [ELSE statement_list] END IF