mysql的函数

---

一、数据类型函数

1、数学函数

1）向上取整

-- 13
ceil(12.12)
1
2

2）向下取整

-- 12
floor(12.12)
1
2

3）四舍五入

round(num)

m：保留几位小数
round(num,m)
1
2
3
4

4）非四舍五入

-- m：保留几位小数
truncate(num,m)

-- 12
truncate(12.8,0)
1
2
3
4
5

5）随机数

-- 0<= val <1.0
rand()

N：种子值
rand(N)
1
2
3
4
5

2、字符串函数

• 下标都是从1开始的

1）字符串拼接

-- 1、有一个为null则返回null
concat('aa','bb')   -- aabb

-- 2、指定分隔符拼接，存在null会忽略
concat_ws(separator,str1,str2 . . .)
concat_ws(',','aa','bb')    -- aa,bb
1
2
3
4
5
6

2）获取字符串的长度

-- 1、计算字符长度
char_length('abc')  -- 3
char_length('轻松工作') -- 4

-- 2、计算字节长度
-- 会把一个中文字符的长度按照设置的对应的字符集计算为2或3
length('abc')   -- 3
length('轻松工作')  -- 12
1
2
3
4
5
6
7
8

3）获取子串在字符串出现的位置

• 可用来判断是否包含，不包含返回0

-- 1、instr(str,子串)
instr('abcdef','c')		-- 3

-- 2、locate(子串,str)
locate('c','abcde')		-- 3

-- 3、find_in_set(子串,str)
-- str必须以逗号分隔，返回该字串在集合中的位置，类似一个集合中是否包含某个元素
find_in_set('3','3,13,33,36,39')	-- 1
find_in_set('3','13,33,36,39')		-- 0
1
2
3
4
5
6
7
8
9
10

4）插入、替换字符串

/*
start：插入位置
length：替换掉的字符串的子串的长度
newstr：新子串
*/
insert(str,start,length,newstr)

insert('abcdef',2,2,'m')  -- amdef
1
2
3
4
5
6
7
8

5）替换

-- 将指定的字符串全部替换
replace('abcdefg','c','x')
1
2

6）转大小写

upper(str)
lower(str)
1
2

7）去除字符串两端内容

-- 1、去两端空白
trim(' ab ')    -- ab

-- 2、去除两端的特定子串
trim(子串 from str)
trim('e' from 'essse')  -- sss
1
2
3
4
5
6

8）截取字符串

/*
从左边开始截取
n：截取的位数
*/
left(str,n) 

/*
从右边开始截取
n：截取的位数
*/
right(str,n)

/*
start：起始位置
length：截取长度，不写截取到末尾
*/
substring(str,start,length)

/*
delim：分隔符
count：截取从左边(正数)/右边(负数)开始，第count个分隔符以左 / 右边的内容
*/
substring_index(str,delim,count)

substring_index('www.wikibt.com','.',1) -- www
substring_index('www.wikibt.com','.',-2)    -- wikibt.com
substring_index(substring_index('www.wikibt.com','.',2),'.',-1)    -- wikibt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

9）重复

-- n重复的次数
repeat(str,n)
repeat('ab',2)  -- abab
1
2
3

10）反转

reverse(str)
reverse('abc')  -- cba
1
2

11）比较

• 字符串在进行比较时和java的字符串比较规则一样，‘121’>‘12000’，所以一般使用类型转换函数

3、日期函数

1）获取当前日期、时间

/*
1、年-月-日 时:分:秒
取的是sql执行时的时间，不会改变
*/
now()

/*
2、年-月-日 时:分:秒
取的是系统时间
*/
sysdate()

-- 3、年-月-日
curdate()

-- 4、时:分:秒
curtime()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2）提取

-- 1、年-月-日
date(now())

-- 2、时:分:秒
time(now())

-- 3、时间分量
extract(year from now())
extract(month from now())
extract(day from now())
extract(hour from now())
extract(minute from now())
extract(second from now())
1
2
3
4
5
6
7
8
9
10
11
12
13

3）运算

• 会自动计算跨月、跨年情况

/*
date：日期
expr：时间间隔
unit：单位
        SECOND
        MINUTE
        HOUR
        DAY
        WEEK
        MONTH
        YEAR
*/

1、加
DATE_ADD(date,INTERVAL expr unit)

2、减
SUBDATE(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

4）格式化

1. 日期时间转字符串

date_format(date,format)
date_format(now(),'%Y年%m月%d日 %H时%i分%s秒')
date_format(date(now()),'%Y-%m-%d 00:00:00')    -- 今天的00:00:00
1
2
3

2. 字符串转日期时间

str_to_date(str,format)

str_to_date('16.11.2018 15.00.00','%d.%m.%Y %H.%i.%s')
1
2
3

5）时间戳转换

1. 日期转换为时间戳

-- 秒
UNIX_TIMESTAMP(now())
1
2

2. 时间戳转换为指定格式的日期

/*
格式：年-月-日 时:分:秒
unix_timestamp：时间戳，秒
*/
FROM_UNIXTIME(unix_timestamp)

/*
unix_timestamp：时间戳，秒
format：指定格式
*/
FROM_UNIXTIME(unix_timestamp,format)
1
2
3
4
5
6
7
8
9
10
11

6）比较

• 使用关系运算和最大最小值

4、json函数

1）构建jsonObject对象

-- 单个
json_objectagg(key,value)

-- 多个，参数不能为奇数、key不能为null
json_object(key,val. . .)

json_object('id', 87, 'name', 'carrot') -- {"id": 87, "name": "carrot"}
1
2
3
4
5
6
7

2）构建jsonArray对象

-- 单个
json_arrayagg(val)

-- 多个
json_array(val. . .)

json_array(1,"abc",null)    -- [1,"abc",null]
1
2
3
4
5
6
7

3）是否包含某个值

/*
包含-1；不包含-0
candidate：json值
*/
json_contains(target, candidate[, path])

json_contains(json_array('1','2'),json_arrayagg('3'))  -- 0
json_contains(字段, ' "www" ', '$.url')    -- 字段的key-url="www" ?
json_contains(字段, '{"url": "www.cctv"}')   -- 字段={"url": "www.cctv"} ?
1
2
3
4
5
6
7
8
9

4）替换

/*
json_doc：json字段/值
path：指定属性
*/
json_replace(json_doc, path, val[, path, val] ...)

-- content字段的name属性替换成'tom''
json_replace(content, '$.name', "tom")
1
2
3
4
5
6
7
8

5）获取字段类型

-- OBJECT、ARRAY
json_type(TARGET / TARGET->'$.params')
1
2

6）加引号

json_quote
1

7）去除json字符串的引号，转成字符串

json_unquote(json_val)

json_unquote(json_extract(tt, '$.name'))
1
2
3

8）提取json值

-- 包含双引号
json_extract(json_doc, path[, path] ...)

JSON_EXTRACT(json_value, '$.name')  -- "kimi"
1
2
3
4

9）获取所有key

-- 返回jsonArray
json_keys(json_doc[, path])

JSON_KEYS(json_object('name','kimi','age',18))  -- ["name","age"]
1
2
3
4

5、其它

1）类型转换

-- 1、
cast(expr as type)

cast(123.4 as int)          -- 123
cast('2021-11-11' as date)
cast('{"name":"kimi"}' as JSON)

-- 2、
convert(expr,type)

convert('123.3',SIGNED) -- 123
1
2
3
4
5
6
7
8
9
10
11

二、聚合函数

1、平均值

avg(column)

select
    avg(age)
from t_user
1
2
3
4
5

2、最大值

max(column)
1

3、最小值

min(column)
1

4、求和

-- 1、单字段
sum(colnum)

-- 2、多字段的值全部相加
sum(colnum+column+...)

-- 3、有一行的一列=null时，该行=null
/*
语文    数学
1       1
null    1
*/
-- 第2行=null
sum(语文+数学) -- 2

-- 配合ifnull函数，解决该问题
sum(ifnull(语文,0)+ifnull(数学,0))  -- 3

-- 4、sum的结果有可能是null，但我们一般想得到0
ifnul(sum(colnum),0)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

5、统计数量

-- 该字段不为null的数量
count(column)

-- 统计表中所有字段的数据的最大个数
count(*)
-- 统计表中数量时，统计id最合适，因为主键不会为null
count(id)
1
2
3
4
5
6
7

三、其它

1、if

-- 动态删除表
drop table if exists t_user;

-- 动态显示值
select if(3>2,2,3);
select if(score is null,'',if(score=0,0,5));

-- 动态排序
order by if(status!='3' or status is null,end_time,update_time) desc;

-- 动态更新
update table set status=if(#{currentItem}=total_item,1,0);
1
2
3
4
5
6
7
8
9
10
11
12

2、ifnull

-- 如果x的值为null则age=y，如果x值不为null则age=x
age = ifnull(x,y)

update t set sal=ifnull(sal,25.000);
1
2
3
4

3、分组汇总统计with rollup

/*
统计sal字段就查sum(sal)
分组才能统计、最后添加该函数
ifnull是针对用了with rollup函数总计这个位置出现空字段时候修改它为总计字样
该函数与order by 互斥，需使用自定义排序
*/
select
    ifnull(name,'合计') nn,
    sum(sal) sal
from emp
group by name with rollup
1
2
3
4
5
6
7
8
9
10
11

4、自定义排序函数order by field

5、UUID

• 可以用作主键值

-- 36位
select UUID();  -- 8a1cb459-e5c1-11ea-8df7-00163e303ffa

-- 18位
select UUID_SHORT();   -- 100486252163235846
1
2
3
4
5