欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL基础篇(03):系统和自定义函数总结,触发器使用详解

程序员文章站 2022-06-05 08:35:50
本文源码: "GitHub·点这里" || "GitEE·点这里" 一、系统封装函数 MySQL 有很多内置的函数,可以快速解决开发中的一些业务需求,大概包括流程控制函数,数值型函数、字符串型函数、日期时间函数、聚合函数等。以下列出了这些分类中常用的函数。 1、控制流程函数 case...when ......

本文源码:github·点这里 || gitee·点这里

一、系统封装函数

mysql 有很多内置的函数,可以快速解决开发中的一些业务需求,大概包括流程控制函数,数值型函数、字符串型函数、日期时间函数、聚合函数等。以下列出了这些分类中常用的函数。

1、控制流程函数

  • case...when

根据值判断返回值,类比编程中的if-else判断。

-- demo 01
select case date_format(now(),'%y-%m-%d') 
    when '2019-12-29' then 'today' 
    when '2019-12-28' then 'yesterday' 
    when '2019-12-30' then 'tommor' 
    else 'unknow' end;
-- demo 02
select (case when 1>0 then 'true' else 'false' end) as result;
  • if(expr1,expr2,expr3)

如果表达式 expr1 是true,则 if()的返回值为expr2; 否则返回值则为 expr3。

select if(1>2,'1>2','1<2') as result ; 
select if(1<2,'yes ','no') as result ;
select if(strcmp('test','test'),'no','yes');
  • ifnull(expr1,expr2)

如果表达式 expr1不为null,则返回值为expr1;否则返回值为 expr2。

select ifnull(null,'cicada');
select ifnull(1/1,'no');

2、常用字符串函数

  • char_length()

返回值为字符串的长度 。

select char_length(' c i c ') ;-- 包含空格
select length(' s q l ') ;
  • concat(str1...)

拼接串联字符串。

select concat('my', 's', 'ql');
select concat('my', null, 'ql'); -- 包含null 则返回null
select concat("%", "java", "%"); -- mybatis中拼接模糊查询
  • elt(n,str1,str2,...)

若n = 1,则返回值为 str1 ,若n = 2,则返回值为 str2 ,以此类推,可以用来转换返回页面的状态。

select elt(1,'提交','审核中','规则通过') ;
select elt(2,'提交','审核中','规则通过') ;
  • format(x,d)

格式化数字类型。

select format(3.1455,2) ; -- 四舍五入保留两位
select truncate(3.1455,2) ; -- 直接截取两位
  • trim(str)

清空字符串空格。

select ltrim('  hel l o ') ;-- 清空左边
select rtrim('  hel l o ') ;-- 清空右边
select trim('  hel l o ') ; -- 清空两边
select replace('m y s q l',' ','') ; -- 替换掉全部空格

3、数值函数

  • floor(x)

返回不大于x的最大整数值 。

select floor(1.23); -- 1
select floor(-1.23); -- -2
  • mod(n,m)

模操作。返回n 被 m除后的余数。

select mod(29,9); -- 2
select 29 mod 9; -- 2
  • rand() rand(n)

返回一个随机浮点值,范围在0到1之间。若已指定一个整数参数 n ,则它被用作种子值,用来产生重复序列。

select rand(); -- 0.923
select rand(20) = rand(20) ; -- true

4、时间日期函数

  • adddate(date,interval expr type)

给指定日期,以指定类型进行运算。

select date_add('2019-12-29', interval 3 day); -- 2020-01-01
  • curdate()

将当前日期按照'yyyy-mm-dd' 或yyyymmdd 格式的值返回,具体格式根据函数用在字符串或是数字语境中而定。

select curdate(); -- '2019-12-29' 字符串
select curdate() + 0; -- 20180725 数字
  • date(expr)

提取日期或时间日期表达式expr中的日期部分。

select date('2019-12-31 01:02:03'); -- '2019-12-31'
select date('2019-12-31 01:02:03')+0; -- 20191231
  • date_format(date,format)

根据format 字符串进行 date 值的格式化。

select date_format(now(), '%y-%m-%d'); -- 2019-12-29
select date_format(now(), '%y年%m月%d日'); -- 2019年12月29日

5、聚合函数

avg([distinct] expr)  求平均值
count({*|[distinct] } expr)  统计行的数量
max([distinct] expr)  求最大值
min([distinct] expr)  求最小值
sum([distinct] expr)  求累加和

二、自定义函数

1、概念简介

函数存储着一系列sql语句,调用函数就是一次性执行这些语句。所以函数可以降低语句重复。函数注重返回值,而触发器注重执行过程,所以一些语句无法执行。所以函数并不是单纯的sql语句集合。

2、使用方式

create function 函数名([参数列表]) returns 数据类型
begin
 sql语句;
 return 值;
end;

参数列表的格式是: 变量名 数据类型。

  • 无参案例
create function mysum1 () returns int return (2+3)*2;
select mysum1 () ;
  • 有参函数

表结构

create table t01_user (
    id int(11) not null auto_increment primary key  comment '主键id',
  user_name varchar(20) default null comment '用户名称'
) engine=innodb default charset=utf8 comment '用户表';

函数用法

create function get_name(p_id int) returns varchar(20)
begin 
    declare username varchar(20);
    select user_name from t01_user where id=p_id into username;
    return username;
end;

select get_name(1) ;

3、函数查看

show create function get_name ;

4、删除函数

drop function get_name ;

5、函数注意事项

函数是事先经过编译,才能在服务器环境调用,所以mysql集群环境需要同步编译;mysql是多线程环境,所以要保证函数也是线程安全 。

三、触发器

1、触发器简介

触发器是特殊的存储过程,不同的是存储过程要用call来调用,而触发器不需要使用call。也不需要手工启动,只要当一个预定义的事件发生的时候,就会被mysql自动触发调用。

2、创建触发器

触发器语法

create trigger trigger_name trigger_time trigger_event 
on tbl_name for each row trigger_stmt
  • trigger_name:触发器命名 ;
  • trigger_time: 触发动作的时间 ;
  • trigger_event: 激活触发器的语句类型 ;
  • tbl_name: 触发器作用的表明,非临时表 ;
  • trigger_stmt:触发程序执行的语句 ;

表数据同步

当向用户表 t01_user 写入数据时,同时向 t02_back 表写入一份备份数据。

-- 用户备份表
create table t02_back (
    id int(11) not null primary key comment '主键id',
  user_name varchar(20) default null comment '用户名称'
) engine=innodb default charset=utf8 comment '用户备份';

-- 触发器程序
drop trigger if exists user_back_trigger ;
create trigger user_back_trigger after insert on t01_user for each row
begin
    insert into t02_back (id,user_name)
values (new.id,new.user_name);
end ;

-- 测试案例
insert into t01_user (user_name) values ('smile'),('mysql') ;
select * from t02_back ;

3、查看触发器

查看触发器是指数据库中已存在的触发器的定义、状态、语法信息等。可以在triggers表中查看触发器信息。

select * from `information_schema`.`triggers` 
where `trigger_name`='user_back_trigger';

4、删除触发器

drop trigger语句可以删除mysql中已经定义的触发器,删除触发器的基本语法。

drop trigger [schema_name.]trigger_name

5、触发器注意事项

  • 触发事件

对于相同的表,相同的事件只能创建一个触发器,比如对表t01_user创建两次after insert触发器,就会报错。

  • 执行效率

触发器可以减少应用端和数据库的通信次数和业务逻辑,但是基于行触发的逻辑,如果数据集非常大,效率会降低。

  • 事务问题

触发器执行和原表的执行语句是否在同一个事务中,取决于触发表的存储引擎是否支持事务。

四、源代码地址

github·地址
https://github.com/cicadasmile/mysql-data-base
gitee·地址
https://gitee.com/cicadasmile/mysql-data-base

MySQL基础篇(03):系统和自定义函数总结,触发器使用详解