程序员经验分享-hwhale

[mysql]游标和触发器

2023-11-13

目录

游标（或光标）

定义

使用过程

示例

总结

触发器

应用场景

定义

使用

创建

查看

删除

示例

一个注意点：

优缺点

拓展：MySQL 8.0的新特性—全局变量的持久化

游标（或光标）

定义

游标是一种 能够对结果集中的每一条记录进行定位（像指针一样，向前定位一条记录、向后定位一条记录，或者是 随意定位到某一条记录 ），并对指向的记录中的数据进行操作的数据结构。

游标让 SQL这种面向集合的语言有了面向过程开发的能力。

特点：

  • 在 SQL 中，游标是一种临时的数据库对象，可以指向存储在数据库表中的数据行指针。

这里游标充当指针的作用,通过操作游标来对数据行进行操作.

  • 游标必须在声明处理程序之前被声明

相对的，变量和条件则必须在声明游标和处理程序之前被声明。

使用过程

使用游标一般分4步，不同DBMS细节不同。

  1. 声明游标

    • mysql(还有SQL Server，DB2 和 MariaDB)

       DECLARE cursor_name CURSOR FOR select_statement;

    • Oracle 或者 PostgreSQL

       DECLARE cursor_name CURSOR IS select_statement;

    select_statement:要使用 SELECT 语句来获取数据结果集，而此时还没有开始遍历数据，

    所以这里要通过SELECT 语句，返回一个用于创建游标的结果集。

  2. 打开游标

    定义好游标之后，使用游标前必须先打开游标。

    打开游标的时候 SELECT 语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备。

     OPEN cursor_name

  3. 使用游标

    使用即通过它来获取数据

     FETCH cursor_name INTO var_name [, var_name] ...
 #这句的作用是使用 cursor_name 这个游标来读取当前行，并且将数据保存到 var_name 这个变量中，
 #游标指针指到下一行。

    注意：

    • 如果游标读取的数据行有多个列名，则在 INTO 关键字后面赋值给多个变量名即可。

    • var_name必须在声明游标之前就定义好。

    • 游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致

  4. 关闭游标

     CLOSE cursor_name

    使用完游标后需要关闭掉该游标 。下次还要使用就要重新打开游标。

    游标会占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。

示例

创建存储过程“get_count_by_limit_total_salary()”，

声明IN参数 limit_total_salary，DOUBLE类型；

声明 OUT参数total_count，INT类型。

函数的功能可以实现累加薪资最高的几个员工的薪资值，直到薪资总和达到limit_total_salary参数的值，返回累加的人数给total_count。

 DELIMITER // 
 ​
 CREATE PROCEDURE get_count_by_limit_total_salary(
     IN limit_total_salary DOUBLE,OUT total_count INT)
 BEGIN
 DECLARE sum_salary DOUBLE DEFAULT 0; #记录累加的总工资 
 DECLARE cursor_salary DOUBLE DEFAULT 0; #记录某一个工资值 
 DECLARE emp_count INT DEFAULT 0; #记录循环个数 
 #定义游标 
 DECLARE emp_cursor CURSOR FOR SELECT salary 
                               FROM employees 
                               ORDER BY salary DESC;
 #打开游标 
 OPEN emp_cursor; 
 ​
         REPEAT
             #使用游标（从游标中获取数据）
             FETCH emp_cursor INTO cursor_salary; 
             SET sum_salary = sum_salary + cursor_salary; 
             SET emp_count = emp_count + 1; 
 ​
             UNTIL sum_salary >= limit_total_salary 
         END REPEAT; 
         SET total_count = emp_count; 
 #关闭游标 
 CLOSE emp_cursor; 
 ​
 END // 
 DELIMITER ;

总结

  • 优点：为逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。

  • 缺点：比如在使用游标的过程中，会对数据行进行 加锁 ，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源 ，造成内存不足，这是因为游标是在内存中进行的处理。

建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率

触发器

应用场景

有 2 个或者多个相互关联的表，如商品信息和库存信息 分别存放在 2 个不同的数据表中，我们在添加一条新商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。

这样一来，我们就必须把这两个关联的操作步骤同步写到程序里面。可对数据进行手动维护时，很容易忘记其中的一步 ，导致数据缺失。

这时可以使用触发器：创建一个触发器，让商品信息数据的插入操作自动触发库存数据的插入操作。

定义

MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。

触发器是由 事件来触发 某个操作，这些事件包括 INSERT 、 UPDATE 、 DELETE 事件。

所谓事件就是指 用户的动作或者触发某项行为。

如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会自动激发触发器执行相应的操作。

当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

使用

触发器不能更新或覆盖；为了修改一个触发器，必须先删再建。

使用触发器

  • insert 触发器

  1. 在insert触发器代码内，可引用一个名为new的虚拟表，访问被插入的行；

  2. 在before insert触发器内，new中的值也可以被更新(允许更改被插入的值)；

  • delete 触发器

  1. 在delete触发器代码内，可以引用一个名为old的虚拟表，访问被删除的行；

  2. old中的值全都是只读的，不能更新；

  • update 触发器

  1. 在update触发器代码内，可以引用一个名为old的虚拟表访问以前(update语句前)的值，引用一个名为new的虚拟表访问新更新的值；

  2. 在before update触发器中，new中的值可能也被更新(允许更改将要用于update语句中的值)；

  3. old中的值全都是只读的，不能更新；

另外的 tips ：

  1. 创建触发器可能需要特殊的安全访问权限，但触发器的执行是自动的。如果insert、update或delete语句可以执行，则相应触发器也能执行；

  2. 应该用触发器来保证数据的一致性(大小写、格式等)；优点在于它总是进行这种处理，而且是透明的进行，与客户机应用无关；

  3. 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改记录到另一个表非常容易；

  4. MySQL触发器不支持call语句，即不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

创建

 CREATE TRIGGER 触发器名称
 {BEFORE|AFTER}
 {INSERT|UPDATE|DELETE} 
 ON 表名 
 FOR EACH ROW 
 触发器执行的语句块;

  • 表名 ：表示触发器监控的对象。

  • BEFORE|AFTER ：表示触发的时间。BEFORE 表示在事件之前触发；AFTER 表示在事件之后触发。

  • INSERT|UPDATE|DELETE ：表示触发的前提事件。

查看

查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。

  1. 查看当前数据库的所有触发器的定义

     SHOW TRIGGERS\G

  2. 查看当前数据库中某个触发器的定义

     SHOW CREATE TRIGGER 触发器名

  3. 从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。

     SELECT * FROM information_schema.TRIGGERS;

删除

 DROP TRIGGER IF EXISTS 触发器名称;

触发器作为数据库对象之一，也适用于DROP删除。

示例

 DELIMITER // 
 ​
 CREATE TRIGGER salary_check_trigger 
 BEFORE INSERT ON employees FOR EACH ROW 
 ​
 BEGIN
     DECLARE mgrsalary DOUBLE; 
                 SELECT salary INTO mgrsalary 
                 FROM employees 
                 WHERE employee_id = NEW.manager_id; 
     IF NEW.salary > mgrsalary THEN 
         SIGNAL SQLSTATE 'HY000'  SET MESSAGE_TEXT = '薪资高于领导薪资错误'; 
     END IF; 
 END // 
 DELIMITER ;

一个注意点：

如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此

时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子

表的UPDATE和DELETE语句定义的触发器并不会被激活。

优缺点

  • 优点：

    1. 触发器可以确保数据的完整性。

    2. 触发器可以帮助我们记录操作日志

    3. 触发器还可以用在操作数据前，对数据进行合法性检查。

  • 缺点：

    1. 触发器最大的一个问题就是可读性差

      因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能 不受应用层的控制 。这对系统维护是非常有挑战的。

       #创建触发器用于修改会员储值操作。如果触发器中的操作出了问题，会导致会员储值金额更新失败
 update demo.membermaster set memberdeposit=20 where memberid = 2; 
 ​
 ERROR 1054 (42S22): Unknown column 'aa' in 'field list'

      这是因为，触发器中的数据插入操作多了一个字段，系统提示错误。可是，如果你不了解这个触发器，很可能会认为是更新语句本身的问题，或者是会员信息表的结构出了问题。说不定你还会给会员信息表添加一个叫“aa”的字段，试图解决这个问题，结果只能是白费力

    2. 相关数据的变更，可能会导致触发器出错。

      特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。

拓展：MySQL 8.0的新特性—全局变量的持久化

在MySQL数据库中，全局变量可以通过SET GLOBAL语句来设置。

例如，设置服务器语句超时的限制，可以通过设置系统变量max_execution_time来实现：

 SET GLOBAL MAX_EXECUTION_TIME=2000;

但使用SET GLOBAL语句设置的变量值只会临时生效 : 即数据库重启 后，服务器又会从MySQL配置文件中读取变量的默认值。

MySQL 8.0版本新增了 SET PERSIST 命令。

例如：设置服务器的最大连接数为1000：

 SET PERSIST global max_connections = 1000;

MySQL会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

mysql

数据库

[mysql]游标和触发器 的相关文章

  • 使用 Python 2.7 和 MySQLdb 将二进制数据插入 MySQL 中的 blob 列时如何避免编码警告

    我在将二进制数据插入到longblob使用 Python 2 7 中的 MySQLdb 在 MySQL 中的列 但我收到一个编码警告 我不知道如何解决 test py 11 Warning Invalid utf8 character st

  • MySQL 创建和更新时的 CURRENT_TIMESTAMP

    我想定义一个有 2 个 TIMESTAMP 字段的表 如下所示 CREATE TABLE msgs id INT PRIMARY KEY AUTO INCREMENT msg VARCHAR 256 ts create TIMESTAMP

  • 根据 MySQL 列内容的长度创建索引？

    如何根据值的长度在 MySQL v 5 0 myisam 数据库引擎 中的列上创建索引 它是文本数据类型 最多 7000 个字符 我是否必须添加具有第一列长度的另一列 是的 因为 MySQL 不支持基于函数的索引 例如ADD INDEX m

  • 在 ASP.NET MVC 中使用 MySQL 的 AccountController

    在 Visual Studio 中创建默认的 ASP NET MVC 项目会设置一个可以在其中注册用户的基本项目 我将如何继续更改它以使用 MySQL 服务器而不是 SQLServer 现在可以使用了 安装最新的 Connector NET

  • 解码Json数据数组并插入到mysql

    这个问题可能已经在这里问过 但我尝试搜索找不到它 我有如下 Json 数据 CityInfo CityCode 5599 Name DRUSKININKAI CityCode 2003 Name KAUNAS CityCode 2573 N

  • MySQL获取最后10条记录中的第一条记录

    在Mysql中 我试图获取最后10条记录中最旧的记录 为了得到最后 10 个我会简单地做SELECT FROM table ORDER BY id DESC LIMIT 10 为了获得最旧的 我只需使用 ASC 顺序 我需要首先按 DESC

  • PHP：使用输入和输出参数（不是“INOUT”）调用 MySQL 存储过程

    我想从 PHP 调用 MySQL 中的存储过程 该过程需要输入and输出参数 not INOUT 参数 举一个简单的例子 假设我在 MySQL 中有以下存储过程 DELIMITER DROP PROCEDURE IF EXISTS test

  • ASP.NET API：尚未为此 DbContext 配置数据库提供程序

    我正在尝试从我的 Net Core API 项目连接到 MySql 数据库 这是我的上下文类 public class MyContext DbContext public MyContext public MyContext DbCont

  • 基于列顺序的查询速度

    数据库中列类型的顺序对查询时间有影响吗 例如 具有混合排序 INT TEXT VARCHAR INT TEXT 的表的查询速度是否会比具有连续类型 INT INT VARCHAR TEXT TEXT 的表慢 答案是肯定的 这确实很重要 而且

  • 需要有关使用 PHP 在 mysql 数据库中插入逗号分隔数据的帮助

    数据库表中已有的演示数据 INSERT INTO csvtbl ID SKU Product Name Model Make Year From Year To VALUES 1 C2AZ 3B584 AR Power Steering P

  • 显示过去 7 天 PHP 的结果

    我想做的是显示过去 30 天的文章 但我现有的代码不断给我一个 mysql fetch assoc 错误 然后追溯到我的查询 这是代码 sql mysql query SELECT FROM table WHERE DATE datetim

  • 自动将所有mysql表转储到单独的文件中？

    我想将每个 mysql 表转储到单独的文件中 手册指出其语法是 mysqldump options db name tbl name 这表明您事先知道表名称 我现在可以设置知道每个表名称的脚本 但是假设我在路上添加了一个新表并且忘记更新转储

  • MySQL 触发器和 SUM()

    我有两张桌子 学生桌和家庭桌 在学生中 我有列 st venue 和total venue 家里我有收入 Total Revenue 是学生 st 收入与家庭收入之和 其中 family id student student id stud

  • MySQL“选择更新”行为

    根据 MySql 文档 MySql 支持多粒度锁定 MGL case 1 开放航站楼 1 连接到mysql mysql gt start transaction Query OK 0 rows affected 0 00 sec mysql

  • PDO::commit() 成功或失败

    The PHP PDO 提交 http www php net manual en pdo commit php文档指出该方法成功时返回 TRUE 失败时返回 FALSE 这是指beginTransaction 和commit 之间的语句执

  • 基本表创建 fpdf

    我找不到使用 fpdf 制作表格并从 mysql 数据库获取数据的合适教程 我只是想知道如何创建一个 我在网上尝试示例时遇到了很多错误 例如 我有 名字 中间名 姓氏 年龄 和 电子邮件 列 如何使用 fpdf 创建表格并回显数据库中的条目

  • 如何使用 PHP 从 MySQL 检索特定值？

    好吧 我已经厌倦了 过去一周我花了大部分空闲时间试图解决这个问题 我知道 SQL 中的查询已更改 但我无法弄清楚 我能找到的所有其他帖子似乎都已经过时了 如果有人能帮助我 我将非常感激 我想做的就是使用手动输入数据库的唯一 密码 来检索行的

  • 如何使用 PHP 获取列中的所有值？

    我一直在到处寻找这个问题 但仍然找不到解决方案 如何从 mySQL 列中获取所有值并将它们存储在数组中 例如 表名称 客户 列名称 ID 名称 行数 5 我想获取此表中所有 5 个名称的数组 我该如何去做呢 我正在使用 PHP 我试图 SE

  • 如何解决 MySQL innodb 在 TRUNCATE TABLE 上“等待表元数据锁”？

    在 GitLab CI 服务器中运行包含数百个应用程序单元测试的测试套件 运行 10 次测试后 不知怎的 它总是卡在等待 TRUNCATE TABLE 上的表元数据锁上 这是一个拆卸步骤 我知道SHOW ENGINE INNODB STAT

  • 哪个是识别关系或非识别关系中的子表？

    在表之间的识别和非识别关系的上下文中 MySQL 文档大量将表称为父表和子表 如何判断哪个表是父表 哪个表是子表 子表 A K A 弱实体 http en wikipedia org wiki Weak entity 是一个表 其主键属性d

随机推荐

热门标签