游标
_游标(cursor)_是一个存储在MySQL服务器上的数据库查询,他不是一条SELECT
语句,而是被该语句检索出来的结果集。在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据
游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或做出更改
MySQL游标只能用于存储过程(和函数)
使用游标
使用游标设计几个明确的步骤:
- 在能够使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,他只是定义要使用的
SELECT
语句 - 一旦声明后,必须打开游标以供使用。这个过程用前面定义的
SELECT
语句实际检索出来 - 对于填有数据的游标,根据需要取出(检索)各行
- 在结束游标使用时,必须关闭游标
在声明游标后,可根据需要频繁地打开和关闭游标。在游标打开后,可根据需要频繁地执行取操作
创建游标
游标用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语句(或位于BEGIN
和END
语句之间的一组语句)
- DELETE
- INSERT
- UPDATE
其他MySQL语句不支持触发器
在创建触发器时,需要给出4条信息:
- 唯一的触发器名
- 触发器关联的表
- 触发器应该响应的活动(
EDELETE
、INSERT
或UPDATE
) - 触发器何时执行
应保持每个数据库的触发器名唯一
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个触发器(每条INSERT
、UPDATE
和DELETE
的之前或之后)。单一触发器不能与多个事件或多个表关联,所以如果你需要一个对INSERT
和UPDATE
操作执行的触发器,则应该定义两个触发器
触发器失败:如果
BEFORE触发器
失败,则MySQL将不执行请求的操作。此外,如果BEFORE触发器
或语句本身失败,MySQL将不执行AFTER触发器
# 删除触发器
drop TRIGGER newproduct;
使用触发器
INSERT触发器
INSERT触发器
在INSERT
语句执行之前或之后执行,需要知道以下几点:
- 在
INSERT触发器
代码内,可应用一个名为NEW
的虚拟表,访问被插入的行 - 在
BEFORE INSERT触发器
中,NEW
中的值也可以被更新(允许更改被插入的值) - 对于
AUTO_INCREMENT
列,NEW
在INSERT
执行之前包含0,在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
语句执行之前或之后执行
- 在
DELETE触发器
代码内,你可以应用一个名为OLD
的虚拟表,访问被删除的行 OLD
中的值全都是只读的,不能更新
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
语句执行之前或之后执行
- 在
UPDATE触发器
代码中,你可以应用一个名为OLD
的虚拟表访问以前(UPDATE
语句前)的值,应用一个名为NEW
的虚拟表访问新更新的值 - 在BEFORE UPDATE触发器中,
NEW
中的值可能也被更新(允许更改将要用于UPDATE
语句中的值) - OLD中的值全都是只读的,不能更新
# 保证州名缩写总是大写
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW NEW.vwnd_state = Upper(NEW.vend_state);
管理事务处理
并非所有引擎都支持事务处理。MySQL支持几种基本的数据库引擎,
MyISAM
和InnoDB
是两种最常使用的引擎,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;
因为涉及两张表orders
和orderItems
,所以使用事务处理快来保证订单不被部分删除。最后的COMMIT
语句仅在不出错时写出更改。如果第一条DELETE
起作用,单第二条失败,则DELETE
不会提交(实际上他是被自动撤销的)
当
COMMIT
或ROLLBACK
语句执行后,事务会自动关闭(将来的更改会隐含提交)
使用保留点
简单的ROLLBACK
和COMMIT
语句就可以写入或撤销整个事务处理。但是只是对简单的事务处理才能这样做,更复杂的事务处理可能需要部分提交或回退。为了支持回退部分事务处理,必须能在事务处理块中合适的位置放置占位符。这样如果需要回退,可以回退到某个占位符
# 创建保留点
$ SAVEPOINT delete1;
# 回退到保留点
$ ROLLBACK TO delete1;
保留点越多越好,因为保留点越多,你越能按自己的意愿灵活地进行回退
保留点在事务处理完成后自动释放,也可以用
RELEASE SAVEPOINT
明确地释放保留点
更改默认的提交行为
# 设置不自动提交
# autocommit标志是针对每个连接而不是服务器的
SET autocommit=0;