1、时间日期函数
1.1 字符串转时间戳
unix_timestamp
用来将字符串转为时间戳,其格式为unix_timestamp(时间字符串)
,其中时间字符串最细粒度是yyyy-MM-dd HH:mm:ss
格式,也可以只取到分钟、小时、天、日、月、年等格式。当无参数时返回当前时间。
SELECT unix_timestamp('2024-01-02 03:04:05') FROM table_name
-- 结果:1704135845
1.2 时间戳转字符串
from_unixtime
用来将时间戳转换为字符串,其格式为from_unixtime(时间戳, 时间格式)
,其中时间格式最细粒度是yyyy-MM-dd HH:mm:ss
格式,也可以只取到分钟、小时、天、日、月、年等格式。
SELECT from_unixtime(unix_timestamp('2024-01-02 03:04:05') , 'yyyy-MM-dd HH:mm:ss') FROM table_name
-- 结果:2024-01-02 03:04:05
1.3 计算时间差天数
datediff
用来计算时间差天数,其格式为datediff(日期1,日期2)
,其中日期1和日期2是以yyyy-MM-dd HH:mm:ss
或yyyy MM-dd
格式表示的字符串。如果日期1早于日期2,结果将为负数。
SELECT datediff('2024-01-02', '2024-01-01') FROM table_name;
-- 结果:1
1.4 取年月日时分秒
year`、`month`、`day`、`hour`、`minute`、`second`分别用于取时间的年月日时分秒,以`year`为例,其其格式为`year(日期)
SELECT year('2024-01-02 03:04:05'), year(to_date('2024-01-02 03:04:05')) FROM table_name
-- 结果:2024、2024
1.5 增加指定天数
date_add
用来为日期加上指定的天数,其格式为date_add(日期, 天数)
,其中天数可以为负数,此时会减去对应的天数。返回结果是yyyy-MM-dd
格式
SELECT date_add('2024-01-05 03:04:05', 3), date_add('2024-01-05 03:04:05', -3) FROM table_name
-- 结果:2024-01-08、2024-01-02
1.6 增加指定月数
add_months
用来为日期加上指定的月数,其格式为add_months(日期, 月数)
,其中月数可以为负数,此时会减去对应的月数。返回结果是yyyy-MM-dd
格式
SELECT add_months('2024-02-02 03:04:05', 1), add_months('2024-02-02 03:04:05', -1) FROM table_name
-- 结果:2024-03-02、2024-01-02
1.7 当月最后一日
last_day
用来取当前日期所在月份的最后一日,其格式为last_day(日期)
。返回结果是yyyy-MM-dd
格式
SELECT last_day('2024-02-02 03:04:05') FROM table_name
-- 结果:2024-02-29
1.8 获取天级日期
to_date
用来获取天级日期,参数必须是yyyy-MM-dd HH:mm:ss
格式字符串(可只取到分、时、日)、date
或timestamp
类型(timestamp类型自测不行),返回值是date类型。
SELECT to_date('2024-01-02 03:04:05'), to_date('2024-01-02 03'), to_date(to_date('2024-01-02 03')) FROM table_name
-- 结果:2024-01-02、2024-01-02、2024-01-02
2、字符串函数
2.1 获取字符串长度
length`用来获取字符串长度,其格式为`length(字符串)
SELECT length('abcdefgh') FROM table_name
-- 结果:8
2.2 字符串取子串
substring
用来取字符串的子串,其格式为substring(起始下标,长度)
,其中字符串下标从1开始。
SELECT substring('abcdefg', 1, 4) FROM table_name
-- 结果:abcd
2.3 查找子串
locate
用来在字符串里查找子串,如果存在要查找的子串,则返回第一次出现的下标,否则返回0。其格式为locate(字符串, 子串, [起始下标])
,其中起始下标是可选参数,用来控制从第几个字符开始查找子串。下标从1开始。
SELECT locate('bcd', 'abcdefg'), locate('bcd', 'abcdabcd'), locate('aaa', 'abcdefg'), locate('bcd', 'abcdefg', 2), locate('bcd', 'abcdefg', 3) FROM table_name
-- 结果:2、2、0、2、0
2.4 字符串中子串替换
replace
用来替换字符串中的子串,其格式为replace(字符串, 旧子串, 新子串)
,当字符串中存在多个旧子串时,会全部被新子串替换
SELECT replace('abcdeabcde', 'bc', '333'), replace('aaaaa', 'aa', 'bb') FROM table_name
-- 结果:a333dea333de、bbbba
2.5 字符串拼接
concat
用来拼接多个字符串,其格式为concat(字符串1, 字符串2, 字符串3, ...)
,其中只要有个一个字符串为NULL,则结果为NULL
SELECT concat('a', 'bb', 'ccc'), concat('a', null, 'ccc') FROM table_name
-- 结果:abbccc、NULL
concat_ws
用来以指定分隔符拼接多个字符串,其格式为concat_ws(分隔符, 字符串1, 字符串2, 字符串3, ...)
,其中有字符串为NULL是会被忽略
SELECT concat_ws(',', 'a', 'bb', 'ccc'), concat_ws(',,', 'a', null, 'ccc') FROM table_name
-- 结果:a,bb,ccc、a,,ccc
3.1 数据类型转换
cast
用来做数据类型转换,其格式为cast(字段名 AS 数据类型)
,数据类型可以是tinyint、smallint、int、bigint、boolean、float、double、string、binary、timestamp、decimal中的一种。
SELECT cast(1.23 AS string) FROM table_name
-- 结果:1.23
3.2 取最大最小值函数
least
和greatest
分别用来计算多个值的最小值和最大值,格式为least(值1, 值2, 值3, ...)
和greatestt(值1, 值2, 值3, ...)
。参数可以是多个值,数据类型支持整数、浮点数、字符串、时间戳等。字符串会按照字典序比较。
SELECT least(1, 3, 5),greatest(1, 3, 5),least(1.11, 3.33, 5.55),greatest(1.11, 3.33, 5.55),least('aaa', 'b', 'abc'),greatest('aaa', 'b', 'abc'),least(unix_timestamp(),unix_timestamp()+1,unix_timestamp()+2),greatest(unix_timestamp(),unix_timestamp()+1,unix_timestamp()+2)
FROM table_name
-- 结果:1、5、1.11、5.55、aaa、b、1726649632、1726649634
3.3 if条件判断
if
用来做条件判断,其格式为if(表达式, 值1, 值2)
,如果表达式的值为true
则取值1,否则取值2。
SELECT if(1=1, 1, 0), if(1>1, 1, 0) FROM table_name
-- 结果:1、0
3.4 case-when条件判断
直接看例子吧。
SELECT casewhen 55>90 then '优'when 55>60 then '良'else '不及格'end
FROM table_name
3.5 获取json字符串key对应的value
get_json_object
用来获取json字符串指定key对应的value,其格式为get_json_object(json字符串, $.key)
,注意$.
是必须的,不可省略。
SELECT get_json_object('{"abcdef":"111"}', '$.abc'),get_json_object('{"abcdef":"111"}', '$.abcdef')
FROM table_name
-- 结果:空、111