MySQL必知必会-游标、触发器、事务管理

oneNeko 于 2022-02-22 发布

游标

_游标(cursor)_是一个存储在MySQL服务器上的数据库查询,他不是一条SELECT语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据

游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或做出更改

MySQL游标只能用于存储过程(和函数)

使用游标

使用游标设计几个明确的步骤:

在声明游标后,可根据需要频繁地打开和关闭游标。在游标打开后,可根据需要频繁地执行取操作

创建游标

游标用DECLARE语句创建,DECLARE命名游标,并定义相应地SELECT语句,格局需要带WHERE和其他子句

CREATE PROCEDURE processorders()
BEGIN
    -- 声明变量
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT

    -- 声明游标 
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

    -- 声明循环标记
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1

    -- 打开游标
    OPEN ordernumbers;

    -- 循环
    REPEAT

        -- 获取数据
        FETCH ordernumbers INTO o;

    -- 结束循环
    UNTIL done END REPEAT;

    -- 关闭游标
    CLOSE ordernumbers;
END;

触发器

触发器 是MySQL响应以下任意语句而自动执行地一条MySQL语句(或位于BEGINEND语句之间的一组语句)

其他MySQL语句不支持触发器

在创建触发器时,需要给出4条信息:

应保持每个数据库的触发器名唯一

CRETAE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

CREATE TRIGGER用来创建名为newproduct的新触发器。触发器可在一个操作发生之前货之后执行,这里给出了AFTER INSERT,所以触发器将在INSERT语句成功执行后执行。这个触发器还制定FOR EACH ROW,因此代码对每个插入行执行。在这个例子中,文本Product added将对每个插入的行显示一次

只有表才支持触发器,视图不支持(临时表也不支持)

触发器按每个表每个事件每次地定义,每个表每个事件每次只允许一个触发器。因此每个表最多支持6个触发器(每条INSERTUPDATEDELETE的之前或之后)。单一触发器不能与多个事件或多个表关联,所以如果你需要一个对INSERTUPDATE操作执行的触发器,则应该定义两个触发器

触发器失败:如果BEFORE触发器失败,则MySQL将不执行请求的操作。此外,如果BEFORE触发器或语句本身失败,MySQL将不执行AFTER触发器

# 删除触发器
drop TRIGGER newproduct;

使用触发器

INSERT触发器

INSERT触发器INSERT语句执行之前或之后执行,需要知道以下几点:

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

此代码创建一个名为neworder的触发器,它按照AFETER INSERT ON orders执行。在插入一个新订单到orders表时,MySQL生成一个新订单号并保存到order_num中。触发器从NEW.order_num取得这个值并返回它。此触发器必须按照AFTER INSERT执行,因为在BEFORE INSERT语句执行之前,新order_num还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号

# 测试
INSERT INTO orders(order_date,cust_id)
VALUES(Now(),10001);

# 返回新的order_num值
+-----------+
| order_num |
+-----------+
|     20010 |
+-----------+

DELETE触发器

DELETE触发器DELETE语句执行之前或之后执行

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num,order_date,cust_id)
    VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值(要被删除的订单)保存到一个名为archive_orders的存档表中

使用BEFORE DELETE触发器的优点(相对于AFTER DELETE触发器来说)为,如果由于某些原因,订单不能存档,DELETE本身将被放弃

UPDATE触发器

UPDATE触发器UPDATE语句执行之前或之后执行

# 保证州名缩写总是大写
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW NEW.vwnd_state = Upper(NEW.vend_state);

管理事务处理

并非所有引擎都支持事务处理。MySQL支持几种基本的数据库引擎,MyISAMInnoDB是两种最常使用的引擎,MyISAM不支持明确的事务处理管理,而后者支持

事务处理 可以用来维护数据库的完整性,它保证成批的MySQL操作要么完全执行,要么完全不执行

事务(transaction) 指一组SQL语句 回退(rollback) 指撤销指定SQL语句的过程 提交(commit) 指将为存储的SQL语句结果写入数据库表 保留点(savepoint) 指事务处理中设置的临时占位符(placeholder),你可以对它发布回退

控制事务处理

# 事务开始
START TRANSACTION

MySQL的ROLLBACK命令用来回退(撤销)MySQL语句。ROLLBACK只能在一个事务处理内使用(在执行一条START TRANSACTION命令之后)

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotal;

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的 隐含提交 即提交是自动进行的。但是,在事务处理块中,提交不会隐含地进行。未进行明确地提交,使用COMMIT语句:

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

因为涉及两张表ordersorderItems,所以使用事务处理快来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用,单第二条失败,则DELETE不会提交(实际上他是被自动撤销的)

COMMITROLLBACK语句执行后,事务会自动关闭(将来的更改会隐含提交)

使用保留点

简单的ROLLBACKCOMMIT语句就可以写入或撤销整个事务处理。但是只是对简单的事务处理才能这样做,更复杂的事务处理可能需要部分提交或回退。为了支持回退部分事务处理,必须能在事务处理块中合适的位置放置占位符。这样如果需要回退,可以回退到某个占位符

# 创建保留点
$ SAVEPOINT delete1;

# 回退到保留点
$ ROLLBACK TO delete1;

保留点越多越好,因为保留点越多,你越能按自己的意愿灵活地进行回退

保留点在事务处理完成后自动释放,也可以用RELEASE SAVEPOINT明确地释放保留点

更改默认的提交行为

# 设置不自动提交
# autocommit标志是针对每个连接而不是服务器的
SET autocommit=0;

相关

MySQL必知必会-查询

MySQL必知必会-插入、增加、修改、删除

MySQL必知必会-视图、存储过程

MySQL必知必会-游标、触发器、事务管理

MySQL必知必会-字符集、校对