三、MySQL 函数

已于 2023-12-21 15:51:20 修改
阅读量179 收藏 1
点赞数
分类专栏： MySQL 文章标签： 数据库 sql mysql
于 2021-11-02 14:27:11 首次发布
骑士梦
本文链接：https://blog.youkuaiyun.com/qq_38667427/article/details/119547056
版权

一、日期函数

  • 获取当前时间
  1. now() 获取 当前日期+时间 (date + time)。
  2. sysdate() 获取 当前日期+时间。
  3. current_timestamp() 获取 当前时间戳。
SELECT now()
	, sysdate()
	, current_timestamp();
-- 2022-04-07 07:59:24	
-- 2022-04-07 07:59:24	
-- 2022-04-07 07:59:24
  • sysdate() 跟 now() 不同之处
  1. now() 在执行开始时，值就得到了。
  2. sysdate() 在函数执行时，动态得到值。
SELECT now()
	, sysdate()
	, current_timestamp()
	, sleep(3)
	, now()
	, sysdate()
	, current_timestamp();
-- 2022-04-07 08:01:42	
-- 2022-04-07 08:01:42	
-- 2022-04-07 08:01:42	
-- 0	
-- 2022-04-07 08:01:42	
-- 2022-04-07 08:01:45	
-- 2022-04-07 08:01:42

1.创建日期

1. makdedate
-- makdedate(year,dayofyear);
SELECT makedate(2001,31);
-- '2001-01-31'

SELECT makedate(2001,32);
-- '2001-02-01'
2. maketime
-- maketime(hour,minute,second);
SELECT maketime(12,15,30);
-- '12:15:30'

2. 日期格式化

1. date_format
SELECT date_format(now(), '%Y-%m-%d %T');
-- 2022-04-07 08:05:41

-- 时间戳格式化
SELECT date_format(20130111191640, '%Y-%m-%d %H:%i:%s');
-- 2013-01-11 19:16:40

-- 字符串格式化必须 `%Y-%m-%d %H:%i:%s` 指定格式
SELECT date_format('2008-08-08 22:23:01', '%Y/%m/%d %H:%i:%s');
-- 2008/08/08 22:23:01
2. time_format
-- 时间格式化，只到时分秒
SELECT time_format(now(), '%Y-%m-%d %T');
-- 0000-00-00 08:06:48
表达式描述
%Y代表 4 位的年份
%y代表 2 位的年份
%m代表月，格式为（01…12）
%c代表月，格式为（1…12）
%d代表月份中的天数，格式为（00…31）
%e代表月份中的天数，格式为（0…31）
%H代表小时，格式为（00…23）
%k代表小时，格式为（0…23）
%h代表小时，格式为（01…12）
%I代表小时，格式为（01…12）
%l代表小时，格式为（1…12）
%i代表分钟，格式为（00…59）
%r代表时间，格式为 12 小时制（hh:mm:ss [AP]M）
%T代表时间，格式为 24 小时制（hh:mm:ss）
%S代表秒，格式为（00…59）
%s代表秒，格式为（00…59）

3. 字符串转日期

1. str_to_date
SELECT str_to_date('2012-05-01 23:59:59', '%Y-%m-%d %T');

SELECT str_to_date('08/09/2008', '%m/%d/%Y');
-- 2008-08-09

SELECT str_to_date('08/09/08', '%m/%d/%y');
-- 2008-08-09

SELECT str_to_date('08.09.2008', '%m.%d.%Y');
-- 2008-08-09

SELECT str_to_date('08:09:30', '%h:%i:%s');
-- 08:09:30

SELECT str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s');
-- 2008-08-09 08:09:30

4. 日期天数互转

1. to_days
SELECT to_days('0000-00-00');
-- null

SELECT to_days('2008-08-08');
-- 733627
2. from_days
SELECT from_days(733627);
-- 2008-08-08

5. 日期秒数互转

1. time_to_sec
select time_to_sec('01:00:05');
-- 3605
2. sec_to_time
select sec_to_time(3605);
-- 01:00:05

6. 日期增加

1. date_add
set @dt = now();

-- 年
select date_add(@dt, interval 1 year);
-- 月
select date_add(@dt, interval 1 month);
-- 日
select date_add(@dt, interval 1 day);
-- 时
select date_add(@dt, interval 1 hour);
-- 分
select date_add(@dt, interval 1 minute);
-- 秒
select date_add(@dt, interval 1 second);
-- 微秒
select date_add(@dt, interval 1 microsecond);
-- 2022-04-07 08:32:05.000001

-- 季度
select date_add(@dt, interval 1 quarter);
-- 周
select date_add(@dt, interval 1 week);

-- 减一天
select date_add(@dt, interval -1 day);

set @dt = '2008-08-09 12:12:33';

select date_add(@dt, interval '01:15:30' hour_second);
-- 2008-08-09 13:28:03 加指定时间

select date_add(@dt, interval '1 01:15:30' day_second);
-- 2008-08-10 13:28:03
2. adddate 加日期
3. addtime 加时间
select now()
	, adddate(now(), 1)
	, addtime(now(), 1);
-- 2022-04-07 08:43:25	
-- 2022-04-08 08:43:25 -- 加了一天	
-- 2022-04-07 08:43:26 -- 加了一秒

7. 日期减少

1. date_sub 减去指定日期
select date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second);
-- 1997-12-30 22:58:59
2. SUBDATE
SELECT NOW()
	, SUBDATE(NOW(), INTERVAL 60 SECOND)
-- 2023-06-09 17:30:01
-- 2023-06-09 17:29:01

SELECT '2023-01-01'
	, SUBDATE('2023-01-01', INTERVAL 60 SECOND)
-- 2023-01-01
-- 2022-12-31 23:59:00

SELECT NOW()
	, SUBDATE(NOW(), INTERVAL 1000 MICROSECOND) AS a
	, SUBDATE(NOW(), INTERVAL 1000 SECOND_MICROSECOND) AS b
	, SUBDATE(NOW(), INTERVAL 1000 MINUTE_MICROSECOND) AS c
	, SUBDATE(NOW(), INTERVAL 1000 HOUR_MICROSECOND) AS d
-- 2023-11-14 11:31:02
-- 2023-11-14 11:31:01.999000
-- 2023-11-14 11:31:01.900000
-- 2023-11-14 11:31:01.900000
-- 2023-11-14 11:31:01.900000

# MICROSECOND 微秒
# SECOND 秒
# MINUTE 分
# HOUR 时
# DAY 天
# WEEK 星期
# MONTH 月
# QUARTER 季
# YEAR 年

# SECOND_MICROSECOND 秒_微秒
# MINUTE_MICROSECOND 分_微秒
# MINUTE_SECOND 分_秒
# HOUR_MICROSECOND 时_微秒
# HOUR_SECOND 时_秒
# HOUR_MINUTE 时_分
# DAY_MICROSECOND 天_微秒
# DAY_SECOND 天_秒
# DAY_MINUTE 天_分
# DAY_HOUR 天_时
# YEAR_MONTH 年_月

8. 日期间隔

1. datediff(date1,date2)
  • 两个日期相减 date1 - date2 返回天数
select datediff('2008-08-08', '2008-08-01');
-- 7

select datediff('2008-08-01', '2008-08-08');
-- -7
2. timediff(time1,time2)
  • 两个日期相减 time1 - time2 返回 time 差值
  • 注意：timediff(time1,time2) 函数的 两个参数类型 必须相同
select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00');
-- 08:08:08

select timediff('08:08:08', '00:00:00');
-- 08:08:08

9. 日期 Unix 时间戳互转

1. unix_timestamp
select unix_timestamp();
-- 1218290027

select unix_timestamp('2008-08-08');
-- 1218124800

select unix_timestamp('2008-08-08 12:30:00');
-- 1218169800
2. from_unixtime
select from_unixtime(1218290027);
-- '2008-08-09 21:53:47'

select from_unixtime(1218124800);
-- '2008-08-08 00:00:00'

select from_unixtime(1218169800);
-- '2008-08-08 12:30:00'

select from_unixtime(1218169800, '%Y %D %M %h:%i:%s %x');
-- '2008 8th August 12:30:00 2008'

10. 时间戳函数

1. timestamp 日期转时间戳
SELECT timestamp(now());
-- 2022-04-07 09:05:03

select timestamp('2008-08-08'); 
-- 2008-08-08 00:00:00

-- timestamp(dt,time) dt + time
select timestamp('2008-08-08 08:00:00', '01:01:01'); 
-- 2008-08-08 09:01:01

select timestamp('2008-08-08 08:00:00', '10 01:01:01'); 
-- 2008-08-18 09:01:01

11. 时间戳增加

1. timestampadd(unit, interval, datetime_expr) 时间戳增加
select timestampadd(day, 1, '2008-08-08 08:00:00'); 
-- 2008-08-09 08:00:00

-- `timestampadd()`函数类似于`date_add()`
select date_add('2008-08-08 08:00:00', interval 1 day); 
-- 2008-08-09 08:00:00

12. 时间戳减少

1. timestampdiff(unit, datetime_expr1, datetime_expr2) 时间戳减少
select timestampdiff(year,'2002-05-01','2001-01-01'); 
-- -1

select timestampdiff(day ,'2002-05-01','2001-01-01'); 
-- -485

select timestampdiff(hour,'2008-08-08 12:00:00','2008-08-08 00:00:00'); 
-- -12

-- timestampdiff() 函数类似于 datediff()。
select datediff('2008-08-08 12:00:00', '2008-08-01 00:00:00'); 
-- 7

13. 时区转换

1. convert_tz(dt, from_tz, to_tz)
select convert_tz('2008-08-08 12:00:00', '+08:00', '+00:00');
-- 2008-08-08 04:00:00
2. date_add、date_sub、timestampadd
  • 时区转换，也可以通过 date_add、date_sub、timestampadd 来实现
select date_add('2008-08-08 12:00:00', interval -8 hour); 
-- 2008-08-08 04:00:00

select date_sub('2008-08-08 12:00:00', interval 8 hour); 
-- 2008-08-08 04:00:00

select timestampadd(hour, -8, '2008-08-08 12:00:00'); 
-- 2008-08-08 04:00:00

14. 根据生日计算年龄

  • 当生日为 未来日期 时，年龄为 0。
set @birthday = STR_TO_DATE('2022-04-09 23:59:59', '%Y-%m-%d %T');
-- 当前日期为`2022-04-08`时
-- 生日为`1994-04-08`，年龄为`28`岁。
-- 生日为`1994-04-09`，年龄为`27`岁。
-- 生日为`2021-04-07`，年龄为`1`岁。
-- 生日为`2021-04-08`，年龄为`0`岁。
-- 生日为`2022-04-08`，年龄为`0`岁。
-- 生日为`2022-04-09`，年龄为`0`岁。
SELECT YEAR(FROM_DAYS(DATEDIFF(NOW(), @birthday))) AS age;

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(@birthday)), '%Y') + 0 AS age;

set @birthday = STR_TO_DATE('2022-04-09 23:59:59', '%Y-%m-%d %T');
-- 当前日期为`2022-04-08`时
-- 生日为`1994-04-07`，年龄为`28`岁。
-- 生日为`1994-04-08`，年龄为`27`岁。
-- 生日为`2021-04-07`，年龄为`1`岁。
-- 生日为`2021-04-08`，年龄为`0`岁。
-- 生日为`2022-04-08`，年龄为`0`岁。
-- 生日为`2022-04-09`，年龄为`0`岁。
SELECT  TIMESTAMPDIFF(YEAR, @birthday, CURDATE()) AS age;
-- 假如当前日期为`2017-1-13`，当生日为`2013-1-14`时，还差一天就要过生日了，离`4`岁只差一天了，结果还是`3`岁。
  • 当生日为 未来日期 时，年龄为 负数。
set @birthday = STR_TO_DATE('2022-04-09 23:59:59', '%Y-%m-%d %T');
-- 当前日期为`2022-04-08`时
-- 生日为`1994-04-08`，年龄为`28`岁。
-- 生日为`1994-04-09`，年龄为`27`岁。
-- 生日为`2021-04-08`，年龄为`1`岁。
-- 生日为`2021-04-09`，年龄为`0`岁。
-- 生日为`2022-04-08`，年龄为`0`岁。
-- 生日为`2022-04-09`，年龄为`-1`岁。
SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(@birthday, '%Y') -
       (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(@birthday, '00-%m-%d')) AS age;

set @birthday = STR_TO_DATE('2022-04-08 23:59:59', '%Y-%m-%d %T');
-- 当前日期为`2022-04-08`时
-- 生日为`1994-04-08`，年龄为`28`岁。
-- 生日为`1994-04-09`，年龄为`27`岁。
-- 生日为`2021-04-07`，年龄为`1`岁。
-- 生日为`2021-04-08`，年龄为`0`岁。
-- 生日为`2022-04-08`，年龄为`0`岁。
-- 生日为`2022-04-09`，年龄为`-1`岁。
SELECT FLOOR(DATEDIFF(CURDATE(), @birthday)/365.2422) AS age;
-- 取生日和当前日期之间的天数，除以一年的实际天数（365天5小时48分46秒），然后取整。
  • 只计算 年份。
set @birthday = STR_TO_DATE('2022-04-07 23:59:59', '%Y-%m-%d %T');
-- 当前日期为`2022-04-08`时
-- 生日为`1994-04-07`，年龄为`27`岁。
-- 生日为`1994-04-08`，年龄为`27`岁。
-- 生日为`1994-04-09`，年龄为`27`岁。
-- 生日为`2021-04-07`，年龄为`0`岁。
-- 生日为`2021-04-08`，年龄为`0`岁。
-- 生日为`2021-04-09`，年龄为`0`岁。
-- 生日为`2022-04-07`，年龄为`-1`岁。
-- 生日为`2022-04-08`，年龄为`-1`岁。
-- 生日为`2022-04-09`，年龄为`-1`岁。
SELECT YEAR(CURDATE()) - YEAR(@birthday) - (RIGHT(CURDATE(), 5) < RIGHT(@birthday, 5)) AS age;
-- 取日期的右边五位
-- 当日期格式为`2013-01-01`时取到的是`01-01`，没有问题。
-- 当日期格式为`2013-1-1`缩写格式时，取右边的五位取出的是`3-1-1`，会导致出错。

set @birthday = STR_TO_DATE('2022-04-07 23:59:59', '%Y-%m-%d %T');
-- 当前日期为`2022-04-08`时
-- 生日为`1994-04-07`，年龄为`28`岁。
-- 生日为`1994-04-08`，年龄为`28`岁。
-- 生日为`1994-04-09`，年龄为`28`岁。
-- 生日为`2021-04-07`，年龄为`1`岁。
-- 生日为`2021-04-08`，年龄为`1`岁。
-- 生日为`2021-04-09`，年龄为`1`岁。
-- 生日为`2022-04-07`，年龄为`0`岁。
-- 生日为`2022-04-08`，年龄为`0`岁。
-- 生日为`2022-04-09`，年龄为`0`岁。
SELECT ROUND(DATEDIFF(CURDATE(), @birthday)/365.2422) AS age;

二、数值函数

1. sign

  • SIGN(X) 符号函数 ，返回参数的符号。
  1. X 的值为 负数、零、正数 时。
  2. 返回结果 依次为 -1、0、1。
SELECT SIGN(100) sign
	, SIGN(0) sign2
	, SIGN(-100) sign3
	, SIGN(null) sign4;
-- sign1: 大于0为1
-- sign2: 等于0为0
-- sign3: 小于0为-1
-- sign4: 为null

2. ROUND 四舍五入

SELECT ROUND(1.234, 2) AS round
	, ROUND(1.235, 2) AS round2
-- 1.23
-- 1.24

3. CONVERT 转换

SELECT SUBSTR('20220206', 5, 2) AS month
	, CONVERT(SUBSTR('20220206', 5, 2), UNSIGNED) AS month2;
-- 02
-- 2

三、字符函数

1. find_in_set

  • FIND_IN_SET(str,strlist);：
    查询 strlist 字段中包含 str 的结果。
    返回的结果为 null、0 或 所在的位置（在 1 到 N 之间）。
  1. str：要查询的字符串（str 要包含一个逗号时，将无法正常运行）。
  2. strlist：字段名（参数以 , 分隔，如：1,2,6,8,10,22）。
SELECT t.ids
	, find_in_set(1, t.ids) AS position
FROM (
    SELECT ',0,' ids
    UNION ALL SELECT '1,' ids
    UNION ALL SELECT '0,1,' ids
    UNION ALL SELECT '-1,0,1' ids
    UNION ALL SELECT '1,0,1' ids
    UNION ALL SELECT 'null' ids
    UNION ALL SELECT null ids
    UNION ALL SELECT '' ids
) t

在这里插入图片描述

1. find_in_set 和 in 区别
  • 如果 ids 是常量，使用 in。
  • 如果 ids 是变量，使用 find_in_set。
2. find_in_set 和 like 区别
  • like 是广泛的模糊查询。
  • find_in_set 是精确匹配，并且 字段值 之间用 , 分开。

2. REPLACE 字符替换

SELECT REPLACE('123\n', '\n', '');
-- 123

4. concat_ws 多个字符串拼接

SELECT concat_ws('_', '1', '2', 3, now())
FROM DUAL;
-- 1_2_3_2022-04-26 02:23:34

5. GROUP_CONCAT 结果集拼接

GROUP_CONCAT([DISTINCT] 'column1' [ORDER BY 'column2' [ASC|DESC]] ['SEPARATOR seq']);

SELECT dept_name 
FROM sys_dept 
WHERE dept_id IN ('8777', '8909', '8910');

SELECT GROUP_CONCAT(dept_name ORDER BY dept_id ASC SEPARATOR ',') 
FROM sys_dept 
WHERE dept_id IN ('8777', '8909', '8910');

在这里插入图片描述

在这里插入图片描述

6. 字符串截取

  • 字符串截取
SELECT REPLACE(LTRIM(REPLACE(SUBSTRING_INDEX('2022-09', '-', -1), 0, ' ')), ' ', 0);
-- 9

SELECT SUBSTRING_INDEX('2022-09', '-', -1);
-- 09

-- 替换字符
SELECT REPLACE('09', 0, ' ');
--  9
SELECT REPLACE(' 9', ' ', 0);
-- 09

-- 去除空格
SELECT LTRIM(' 9');
-- 9

SELECT SUBSTR('2022-09', 6, 2);
-- 09
SELECT CONVERT(SUBSTR('2022-09', 6, 2), UNSIGNED);
-- 9

-- 左截取
SELECT LEFT('1234', 2);
-- 12

-- 右截取
SELECT RIGHT('1234', 1);
-- 4

-- 索引截取
SELECT SUBSTR('1234', 1, 2);
-- 12
SELECT SUBSTR('1234', 2, 2);
-- 23

-- 字节长度
SELECT LENGTH('1234');
-- 4
SELECT LENGTH('张山');
-- 6

-- 字符长度
SELECT CHAR_LENGTH('1234');
-- 4
SELECT CHAR_LENGTH('张山');
-- 2

7. 数据脱敏

-- 数据脱敏
SELECT user_name,
       IF(
     		CHAR_LENGTH(user_name) > 2,
          	CONCAT(LEFT(user_name, 1), '*', RIGHT(user_name, 1)),
          	CONCAT('*', RIGHT(user_name, 1))
		) AS userName2
FROM sys_user;
-- admin,a*n

四、其他函数

1. ifnull(.,.)

  • IFNULL(expr1,expr2);
  1. 如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。
  2. IFNULL() 返回一个数字或字符串值，取决于它被使用的上下文环境。
SELECT IFNULL(1,0);
-- 1
SELECT IFNULL(0,10);
-- 0
SELECT IFNULL(1/0,10);
-- 10.0000
SELECT IFNULL(1/0,'yes');
-- yes

2. if(.,.,.)

  • IF(expr1,expr2,expr3);
  1. 如果expr1 是 TRUE（expr1<>0 且 expr1<>NULL），那么 IF() 返回 expr2，否则它返回 expr3。
  2. IF() 返回一个数字或字符串值，取决于它被使用的上下文。
SELECT IF(1>2,2,3);
-- 3

SELECT IF(1<2,'yes','no');
-- 'yes'

SELECT IF(strcmp('test','test1'),'yes','no');
-- 'yes'

SELECT IF(0.1,1,0);
-- 1

SELECT IF(0.1<>0,1,0);
-- 1

SELECT IF(0,1,2);
-- 2

SELECT IF('qs' IS NOT NULL, 1, 0);
-- 1

SELECT IF(2 = 2, 1, 0);
-- 1

3. ON DUPLICATE KEY UPDATE—不存在新增，存在更新

  • 当 INSERT 已经存在的记录时，执行 UPDATE。
INSERT INTO demo (id, name) VALUES (1, '张三');
  1. 第一次插入 id=1 成功
    在这里插入图片描述
INSERT INTO demo (id, name) VALUES (1, '李四');
  1. 第二次插入 id=1 失败
    1062 - Duplicate entry ‘1’ for key ‘PRIMARY’
INSERT INTO demo (id, name) VALUES (1, '李四')
ON DUPLICATE KEY UPDATE 
id = VALUES(id),
name = VALUES(name);
  1. 第三次 ON DUPLICATE KEY UPDATE 不存在新增，存在更新
    在这里插入图片描述
INSERT INTO demo (id, name) VALUES 
(1, '张山'), (2, '李四'), (3, '王五')
ON DUPLICATE KEY UPDATE 
id = VALUES(id),
name = VALUES(name);
  1. 批量插入成功
    在这里插入图片描述
INSERT INTO demo (id, name) VALUES 
(1, '张山'), (2, '李四'), (3, '王五'), (4, '赵六')
ON DUPLICATE KEY UPDATE 
id = VALUES(id),
name = 'wy';
  1. 批量修改指定值
    在这里插入图片描述
INSERT INTO test (
	province_code, city_code, area_code
) VALUES
<foreach collection="list" item="item" separator=",">
	(#{item.provinceCode}, #{item.cityCode}, #{item.areaCode})
</foreach>
ON DUPLICATE KEY UPDATE
province_code= VALUES(province_code),
city_code= VALUES(city_code),
area_code= VALUES(area_code)

4. LAST_INSERT_ID()—上次插入的 ID

CREATE TABLE `qs_code` (
  `code1` int(10) NOT NULL AUTO_INCREMENT COMMENT 'code1',
  PRIMARY KEY (`code1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 设置自增字段的初始值
ALTER TABLE dam_staff_code AUTO_INCREMENT = 1000000;
-- 获取上次插入 ID
SELECT last_insert_id();

SELECT LAST_INSERT_ID();

SELECT @parentId := LAST_INSERT_ID();

SELECT @date := 20220714;
SELECT @rowNum := @rowNum + 1 AS serialNumber

5. nulls first 和 nulls last

-- sort 从小到大排序
-- null 值默认最小，需要 null 值排最后
SELECT sort
FROM user
ORDER BY sort nulls last

-- null 值排最后
SELECT sort
FROM user
WHERE deleted = 0
ORDER BY sort IS NULL, sort

-- null 值排最前
SELECT sort
FROM user
WHERE deleted = 0
ORDER BY sort IS NULL DESC, sort

SELECT 1 IS NULL, null IS NULL
`
0	1
`
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

骑士梦

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值