MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(4)

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】

MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(3)https://developer.aliyun.com/article/1534304

3.3.10、循环语句和循环控制

先介绍循环控制:

  • leave:类似于 break
  • iterate:类似于 continue
1. while 循环

语法

[标签]: while 循环条件 do
    循环体
end while [标签];

测试:

delimiter $$
create procedure func11()
begin
    declare i int default 0;
    declare num int default (select count(*) from emp);
    while i<num do
        select i;
        set i = i+1;
    end while;
end $$
delimiter ;
 
call func11();

测试结果就是会创建 num 张表,表内就是 i 的值。

2. repeat 循环

语法

[标签:] repeat
    循环体;
until 条件表达式
end repeat 标签;

测试:

delimiter $$
create procedure func12()
begin
    declare i int default 0;
    label: repeat
        set i = i+1;
        until i > 10
    end repeat label;
    select '循环结束';
end $$
delimiter ;
 
call func12();
3. loop 循环

语法

[标签:] loop
    循环体
    if 条件表达式 then
        leave 标签;
    endif;
end loop;

测试:

delimiter $$
create procedure func13()
begin
    declare i int default 0;
    label: loop
        if i!=5 then
            set i = i+1;
        else leave label;
        end if;
    end loop;
end $$
delimiter ;
 
call func13();

3.3.11、存储过程之游标(Cursor)

       游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE.

语法

-- 声明语法
declare cursor_name cursor for select_statement
-- 打开语法
open cursor_name
-- 取值语法
fetch cursor_name into var_name [, var_name] ...
-- 关闭语法
close cursor_name

测试:

-- 游标
delimiter $$
create procedure func14(in id int)
begin
    -- 声明局部变量
    declare eid int;
    declare ename char(20);
    declare esalary decimal(10,2);
 
    -- 声明游标
    declare my_cursor cursor for
        select emp_id,emp_name,salary
            from emp
            where emp_id=id;
 
    -- 打开游标
    open my_cursor;
 
    -- 通过游标获取每一行
    label: loop
        fetch my_cursor into eid,ename,esalary;
        select eid,ename,esalary;
        leave label;
    end loop;
 
    -- 关闭游标
    close my_cursor;
end $$
delimiter ;
 
drop procedure func14;
 
 
call func14(1);

注意循环体中必须有退出的条件,否则就是死循环!

3.3.12、句柄 handler

4、存储函数(自定义函数)

注意:自定义函数之前必须设置全局变量:

-- 信任函数的创建者
set global log_bin_trust_function_creators=TRUE;
-- 信任函数的创建者
set global log_bin_trust_function_creators=TRUE;
 
delimiter $$
create function oneToNum(n int) returns int
begin
    declare sum int default 0;
    while n!=0 do
        set sum = sum + n;
        set n = n-1;
    end while;
    return sum;
end $$
delimiter ;
 
select oneToNum(3); --6

注意:自定义函数不能包含递归,递归需要使用专门的语法。

5、触发器

5.1、介绍

  • 触发器,就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段,但是触发器无需调用,当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行,无需手动条用。
  • 在MySQL中,只有执行insert,delete,update操作时才能触发触发器的执行
  • 触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。
  • 使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。

5.2、触发器的特性

  • 什么条件会触发:I、D、U
  • 什么时候触发:在增删改前或者后
  • 触发频率:针对每一行执行
  • 触发器定义在表上,附着在表上

5.3、触发器语法

5.3.1、创建只有一个执行语句方触发器

注意:这里的触发事件只能是 insert、update、delete。

create trigger 触发器名 before|after 触发事件
on 表名 for each row 
执行语句;

5.3.2、创建有多个执行语句的触发器

create trigger 触发器名 before|after  触发事件 
on 表名 for each row
begin
     执行语句列表
end;

5.4、触发器的使用

5.4.1.创建触发器

-- 触发器
drop trigger if exists trigger_emp;
 
-- 创建受触发器影响的表格
create table emp_log(
    id int primary key auto_increment,
    time timestamp,
    log_text varchar(255)
);
 
-- 创建触发器
create trigger trigger_emp
after insert on emp
for each row
insert into emp_log values (NULL,now(),'新的员工注册');
 
insert into emp values (NULL,'刘海柱',8999,'技术部');

当向 emp 表进行 insert 操作时,就会触发触发器向 emp_log 插入一条日志。

5.4.2、NEW 和 OLD

MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中,触发了触发器的那一行数据,来引用触发器中发生变化的记录内容,具体地:

       这让我很自然的联想到了 MySQL 的 binlog 功能,我们数仓中使用 MaxWell 来监听 binlog 实现数据同步,但是 binlog 的底层并不是触发器。

语法

NEW.列名
OLD.列名

测试:

-- 不要影响到其它触发器对 emp_log 的操作,比如这里增加了两个字段,
-- 当对该表进行insert 操作时,因为给 emp_log 增加字段之后没有更新触发器的行为
-- 就会导致给 emp 和 emp_log 插入数据是全部失败
 
alter table emp_log add old varchar(50);
alter table emp_log add new varchar(50);
 
create trigger trigger_test
after update
on emp for each row
insert into emp_log values (
                            null,now(),
                            concat('更新数据'),
                            concat(OLD.emp_id,OLD.emp_name,OLD.salary,OLD.department),
                            concat(NEW.emp_id,NEW.emp_name,NEW.salary,NEW.department)
);
update emp set emp_name = '李元芳' where emp_id = 3;
 
select * from emp_log;

5.5、触发器使用的注意事项

  • MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作,以免递归循环触发
  • 尽量少使用触发器,假设触发器触发每次执行1s,insert table 500条数据,那么就需要触发500次触发器,光是触发器执行的时间就花费了500s,而insert 500条数据一共是1s,那么这个insert的效率就非常低了。
  • 触发器是针对每一行的;对增删改非常频繁的表上切记不要使用触发器,因为它会非常消耗资源。

MySQL 进阶使用【函数、索引、视图、存储过程、存储函数、触发器】(5)

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
28天前
|
关系型数据库 MySQL 数据库
MySQL索引优化:深入理解索引合并
MySQL索引优化:深入理解索引合并
|
29天前
|
关系型数据库 MySQL 分布式数据库
PolarDB产品使用问题之mysql迁移后查询不走索引了,该如何解决
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
29天前
|
关系型数据库 MySQL 测试技术
深入探索MySQL 8:隐藏索引与降序索引的新特性
深入探索MySQL 8:隐藏索引与降序索引的新特性
|
29天前
|
存储 关系型数据库 MySQL
架构面试题汇总:mysql索引汇总(2024版)
架构面试题汇总:mysql索引汇总(2024版)
|
29天前
|
存储 SQL 关系型数据库
|
1月前
|
存储 关系型数据库 MySQL
Mysql优化之索引相关介绍(笔记)
索引查找从顶层节点开始查找,通过key值,也就是主键的值进行比较,最终定位到存储数据的叶子节点上面,从叶子节点取出响应的数据。
44 0
Mysql优化之索引相关介绍(笔记)
|
13天前
|
存储 关系型数据库 MySQL
如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
|
23天前
|
关系型数据库 MySQL 数据库
MySQL索引的类型与优化方法
MySQL索引的类型与优化方法
|
23天前
|
存储 关系型数据库 MySQL
MySQL索引设计原则与优化策略
MySQL索引设计原则与优化策略
|
23天前
|
存储 关系型数据库 MySQL
MySQL删除索引的方法与注意事项
MySQL删除索引的方法与注意事项