阅读 118

MySQL必知必会笔记——MySQL其他操作

这是我参与更文挑战的第3天,活动详情查看:更文挑战

MySQL其他操作

第二十二章 使用视图

视图

例:

SELECT cust_name, cust_contact FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems,order_num = orders.order_num
AND prod_id = 'TNT2';
#此查询用来检索订购了某个特定产品的客户。假如把整个查询包装成一个名为productcusotmers的虚拟表,则
SELECT cust_name, cust_contact FROM productcustomers
WHERE prod_id = 'TNT2';
复制代码

productcustomers是一个视图。

为什么使用视图

  • 重用SQL语句。
  • 简化复杂的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节。
  • 使用表的组成部分而不是整个表。
  • 保护数据。可以给用户授予表的特定部分的访问权限而不是整个表的访问权限。
  • 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

视图的规则和限制

  • 与表一样,视图必须唯一命名(不能给视图取与别的视图或表相同的名字)。
  • 对于可以创建的视图数目没有限制。
  • 为了创建视图,必须具有足够的访问权限。这些限制通常由数据库管理人员授予。
  • 视图可以嵌套,即可以利用从其他视图中检索数据的查询来构造一个视图。
  • ORDER BY可以用在视图中,但如果从该视图检索数据SELECT中也含有ORDER BY,那么该视图中的ORDER BY将被覆盖。
  • 视图不能索引,也不能有关联的触发器或默认值。
  • 视图可以和表一起使用。例如,编写一条联结表和视图的SELECT语句。

使用视图

  • 视图用CREATE VIEW语句来创建。
  • 使用SHOW CREATE VIEW viewname;来查看创建视图的语句
  • 用DROP删除视图,其语法为DROP VIEW viewname;
  • 更新视图时,可以先用DROP再用CREATE,也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在,则第2条更新语句会创建一个视图;如果要更新的视图存在,则第2条更新语句会替换原有视图。

利用视图简化复杂的联结

CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderietms
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
复制代码

创建可重用的视图:创建不受特定数据限制的视图是一种好办法。例如,上面创建的视图返回生产所有产品的客户而不仅仅是生产TNT2的客户。扩展视图的范围不仅使得它能被重用,而且甚至更有用。这样做不需要创建和维护多个类似视图。

用视图重新格式化检索出的数据

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
复制代码

现在,假如经常需要这个格式的结果。不必在每次需要时执行联结,创建一个视图,每次需要时使用它即可。为把此语句转换为视图,可按如下进行:

CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;
复制代码

这条语句使用与以前的SELECT语句相同的查询创建视图。为了检索出以创建所有邮件标签的数据,可如下进行:

SELECT *
FROM vendorlocations;
复制代码

用视图过滤不想要的数据

例:排除没有电子邮件地址的用户

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

SELECT * FROM customeremaillist;
复制代码

使用视图与计算字段

SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;
#将其转换为视图
CREATE VIEW orderitemsexpanded AS
SELECT order_num, prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems;

SELECT *
FROM orderitemsexpanded
WHERE order_num = 20005;
复制代码

更新视图

视图的数据能否更新视情况而定。

通常,视图是可更新的(即,可以对它们使用INSERT、UPDATE和DELETE)。更新一个视图将更新其基表(可以回忆一下,视图本身没有数据)。如果你对视图增加或删除行,实际上是对其基表增加或删除行。 但是,并非所有视图都是可更新的。基本上可以说,如果MySQL不能正确地确定被更新的基数据,则不允许更新(包括插入和删除)。这实际上意味着,如果视图定义中有以下操作,则不能进行视图的更新:

  • 分组(使用GROUP BY和HAVING);
  • 联结;
  • 子查询;
  • 并;
  • 聚集函数(Min()、Count()、Sum()等);
  • DISTINCT;
  • 导出(计算)列。

第二十三章 使用存储过程

存储过程

存储过程简单来说,就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件,虽然它们的作用不仅限于批处理。

为什么使用存储过程

  • 通过把处理封装在容易使用的单元中,简化复杂的操作(正如前面例子所述)。
  • 由于不要求反复建立一系列处理步骤,这保证了数据的完整性。如果所有开发人员和应用程序都使用同一(试验和测试)存储过程,则所使用的代码都是相同的。 这一点的延伸就是防止错误。需要执行的步骤越多,出错的可能 性就越大。防止错误保证了数据的一致性。
  • 简化对变动的管理。如果表名、列名或业务逻辑(或别的内容)有变化,只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。 这一点的延伸就是安全性。通过存储过程限制对基础数据的访问减少了数据讹误(无意识的或别的原因所导致的数据讹误)的机会。
  • 提高性能。因为使用存储过程比使用单独的SQL语句要快。
  • 存在一些只能用在单个请求中的MySQL元素和特性,存储过程可以使用它们来编写功能更强更灵活的代码(在下一章的例子中可以看到。) 换句话说,使用存储过程有3个主要的好处,即简单、安全、高性能。显然,它们都很重要。不过,在将SQL代码转换为存储过程前,也必须知道它的一些缺陷。

执行存储过程

MySQL称存储过程的执行为调用,因此MySQL执行存储过程的语句 为CALL。CALL接受存储过程的名字以及需要传递给它的任意参数。

-- 执行名为productpricing的存储过程,它计算并返回产品的最低、最高和平均价格。
CALL productpricing(@pricelow,
				   @pricehigh,
                    @priceaverage);
复制代码

创建存储过程

-- 返回产品平均价格的存储过程
CREATE PROCEDURE priductpricing()
BEGIN
	SELECT Avg(prod_price) AS priceaverage
    FROM priducts;
END;
复制代码

使用存储过程:

-- 执行刚创建的存储过程并显示返回的结果。
CALL priductpricing();
复制代码

因为存储过程实际上是一种函数,所以存储过程名后 需要有()符号(即使不传递参数也需要)。

删除存储过程

DROP PROCEDURE productpricing;
复制代码

仅当存在时删除使用DROP PROCEDURE IF EXISTS

使用参数

CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
	SELECT Min(prid_price)
    INTO pl
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT AVG(prod_price)
    INTO pa
    FROM prodcts;
END;
复制代码

此存储过程接受3个参数:pl存储产品最低价格,ph存储产品最高价格,pa存储产品平均价格。每个参数必须具有指定的类型,这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)。MySQL支持IN(传递给存储过程)、OUT(从存储过程传出,如这里所用)和INOUT(对存储过程传入和传出)类型的参数。存储过程的代码位于BEGIN和END语句内,如前所见,它们是一系列SELECT语句,用来检索值,然后保存到相应的变量(通过指定INTO关键字)。

为调用此修改过的存储过程,必须指定3个变量名,如下所示:

CALL productpricing(@pricelow,
					@pricehigh,
                    @priceaverage);
#为了显示检索出的产品价格,可如下进行
SELECT @priceaverage;
#为了获得3个值,可以使用如下语句
SELECT @pricehigh, @pricelow, @priceaverage;
复制代码
-- 使用IN和OUT参数,ordertotal接收订单号并返回该订单的合计。
-- onumber定义为IN,因为订单号被传入存储过程。
-- ototal定义为OUT,因为要从存储过程返回合计。
CREATE PROCEDURE ordertotal(
	IN onumber INT,
    OUT ototal DECIMAL(8,2)
)
BEGIN
	SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO ototal;
END;

#调用:必须传递两个参数,1为订单号,2为包含计算出来的合计的变量名
CALL ordertotal(20005, @total);

#显示合计
SELECT @total;
复制代码

建立智能存储过程

显而易见上述的sql没必要封装起来,只要执行被封装的sql就能得到结果。只有在存储过程内包含业务规则和智能处理时,它们的威力才真正显现出来。

例:获得与以前一样的订单合计,但需要对合计增加营业税,不过只针对某些顾客

  • 获得合计
  • 把营业税有条件地添加到合计
  • 返回合计
-- Name: ordertotal
-- Parameters: onumber = order number
--             taxable = 0 if not taxable, 1 if taxable
--             ototal  = order total variable

CREATE PROCEDURE ordertotal(
	IN onumber TNT,
    IN taxable BOOLEAN,
    OUT ototal DECIMAL(8,2)
)COMMENT 'Obtain order total, optionally adding tax'
BEGIN
    -- DECLARE variable for total
    DECLARE total DECIMAL (8,2);
    -- DECLARE tax percentage
    DECLARE taxrate INT DEFAULT 6;

    -- Get the order total
    SELECT Sum(item_price*quantity)
    FROM orderitems
    WHERE order_num = onumber
    INTO total;

    -- Is this taxable?
    IF taxable THEN
    	-- Yes, so add taxrate to the total
        SELECT total +(total/100*taxrate) INTO total;
    END IF;

    --And finally, save to our variable
    SELECT total INTO ototal;
END;

--试验
CALL ordertotal(20005, 0, @total);
SELECT @total;

CALL ordertotal(20005, 1, @total);
SELECT @total;
复制代码

此存储过程有很大的变动。首先,增加了注释(前面放置--)。在存储过程复杂性增加时,这样做特别重要。添加了另外一个参数taxable,它是一个布尔值(如果要增加税则为真,否则为假)。在存储过程体中,用DECLARE语句定义了两个局部变量。DECLARE要求指定变量名和数据类型,它也支持可选的默认值(这个例子中的taxrate的默 认被设置为6%)。SELECT语句已经改变,因此其结果存储到total(局部变量)而不是ototal。IF语句检查taxable是否为真,如果为真,则用另一SELECT语句增加营业税到局部变量total。最后,用另一SELECT语句将total(它增加或许不增加营业税)保存到ototal。 本例子中的存储过程在CREATE PROCEDURE语句中包含了一个COMMENT值。它不是必需的,但如果给出,将在SHOW PROCEDURE STATUS的结果中显示

检查存储过程

显示用来创建一个存储过程的CREATE语句,使用

SHOW CREATE PROCEDURE ordertotal;
复制代码

为获得包括何时、由谁创建等详细信息的存储过程列表,使用SHOW PRODUCEDURE STATUS SHOW PROCEDURE STATUS列出所有存储过程。为限制其输出,可使用LIKE指定一个过滤模式:

SHOW PROCEDURE STATUS LIKE 'ordertotal';
复制代码

第二十四章 使用游标

有时,需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。

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

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

使用游标

使用游标涉及几个明确的步骤。

  • 在能够使用游标前,必须声明(定义)它。这个过程实际上没有检索数据,它只是定义要使用的SELECT语句。
  • 一旦声明后,必须打开游标以供使用。这个过程用前面定义的SELECT语句把数据实际检索出来。
  • 对于填有数据的游标,根据需要取出(检索)各行。
  • 在结束游标使用时,必须关闭游标。

创建游标

CREATE PROCEDURE processorders()
BEGIN
	DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END;
复制代码

这个存储过程并没有做很多事情,DECLARE语句用来定义和命名游标,这里为ordernumbers。存储过程处理完成后,游标就消失(因为它局限于存储过程)。

打开和关闭游标

--打开游标
OPEN ordernumbers;
--处理完成后,应当使用下句关闭游标
CLOSE ordernumbers;
复制代码

CLOSE释放游标使用的所有内部内存和资源,因此在每个游标不再需要时都应该关闭

隐含关闭:如果你不明确关闭游标,MySQL会在达到END语句时自动关闭它。

下面是前面例子的修改版本:

CREATE PROCEDURE processorders()
BEGIN
	-- Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELCET order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers;

    --Close the cursor
    CLOSE ordernumbers;

END;
复制代码

这个存储过程声明、打开和关闭一个游标,但对检索出的数据什么也没做。

使用游标数据

-- 从游标中检索单个行
CREATE PROCEDURE processorders()
BEGIN
	-- Declare local variables
    DECLARE o INT;

    -- Declare the cursor
    DECLARE orderumbers CURSOR
    FRO
    SELECT order_num FROM orders;

    -- Open the cursor
    OPEN ordernumbers;

	-- Get order number
    FETCH ordernumbers INTO o;

	-- Close the cursor
    CLOSE ordernumbers;

END;
复制代码

其中FETCH用来检索当前行的order_num列(将自动从第一行开始)到一个名为o的局部声明的变量中。对检索出的数据不做任何处理。

-- 循环检索数据,从第一行到最后一行
CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;

    --Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;

	--Declare continue handler
    DECLARE CONTINUE HANDLER FRO SQLSTATE '02000' SET done=1;

	--Open the cursor
    OPEN ordernumbers;

	--Loop through all rows
    REPEAT
    	--Get order numbers
        FETCH ordernumbers INTO o;
    --End of loop
    UNTIL done END REPEAT;
    --CLose the cursor
    CLOSE ordernumbers;
END;
复制代码

这个例子使用FETCH检索当前order_num到声明的名为o的变量中。但与前一个例子不一样的是,这个例子中的FETCH是在REPEAT内,因此它反复执行直到done为真(由UNTIL done END REPEAT;规定)。为使它起作用,用一个DEFAULT 0(假,不结束)定义变量done。

使用以下语句将done在结束时设置为真: DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1

这条语句定义了一个CONTINUE HANDLER,它是在条件出现时被执行的代码。这里,它指出当SQLSTATE '02000'出现时,SET done=1。SQLSTATE '02000'是一个未找到条件,当REPEAT由于没有更多的行供循环而不能继续时,出现这个条件。

CREATE PROCEDURE processorders()
BEGIN
	--Declare local variables
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
    --Declare the cursor
    DECLARE ordernumbers CURSOR
    FOR SELECT order_num FROM orders;
    --Declare continue handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    --Create a table to store the results
    CREATE TABLE IF NOT EXISTS ordertotals
    	(order_num INT, total DECIMAL(8,2));
    --Open the cursor
    OPEN ordernumbers;
    --Loop through all rows
    REPEAT
    	--Get order number
        FETCH ordernumbers INTO o;
        --Get the total for this order
        CALL ordertoal(o, 1, t);
        --Insert order and total into ordertotals
        INSERT INTO ordertotals(order_num, total)
        VALUES(o,t);
    --End of loop
    UNTIL done END REPEAT;
    --Close the cursor
    CLOSE ordernumbers;
END;
复制代码

在这个例子中,增加了另一个名为t的变量(存储每个订单的合计)。此存储过程还在运行中创建了一个新表(如果它不存在的话),名为ordertotals。这个表将保存存储过程生成的结果。FETCH像以前一样取每个order_num,然后用CALL执行另一个存储过程来计算每个订单的带税的合计(结果存储到t)。最后,用INSERT保存每个订单的订单号和合计。 次存储过程不返回数据 可以用SELECT * FROM ordertotals;查看该表

第二十五章 使用触发器

想要某条语句(或某些语句)在事件发生时自动执行。

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

  • DELETE
  • INSERT
  • UPDATE 其他MySQL语句不支持触发器

创建触发器

需要

  • 唯一的触发器名
  • 触发器关联的表
  • 触发器应该响应的活动
  • 触发器合适执行

使用CREATE TRIGGER语句创建。例:

CREATE TRIGGER newproduct ALTER INSERT ON products
FOR EACH ROW SELECT 'Product added';
复制代码

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

意思就是使用INSERT语句添加一行或多行到products 中,你将看到对每个成功的插入,显示Product added消息。

只有表支持触发器,视图不支持。

每个表最多支持6个触发器(每条INSERT、UPDATE和DELETE前后)单一触发器不能与多个事件或多个表关联。

删除触发器

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

DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点:

  • 在DELETE触发器代码内,你可以引用一个名为OLD的虚拟表,访问被删除的行;
  • 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的存档表中(为实际使用这个例子,你需要用与orders相同的列创建一个名为archive_orders的表)。

UPDATE触发器

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

  • 在UPDATE触发器代码中,你可以引用一个名为OLD的虚拟表访问以前(UPDATE语句前)的值,引用一个名为NEW的虚拟表访问新 更新的值;
  • 在BEFORE UPDATE触发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值);
  • OLD中的值全都是只读的,不能更新。 下面的例子保证州名缩写总是大写(不管UPDATE语句中给出的是大写还是小写):
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(New.vend_state);
复制代码

每次更新一个行时,NEW.vend_state中的值(将用来更新表行的值)都用Upper(NEW.vend_state)替换。

关于触发器的进一步介绍

  • 与其他DBMS相比,MySQL 5中支持的触发器相当初级。未来的MySQL版本中有一些改进和增强触发器支持的计划。
  • 创建触发器可能需要特殊的安全访问权限,但是,触发器的执行是自动的。如果INSERT、UPDATE或DELETE语句能够执行,则相关的触发器也能执行。
  • 应该用触发器来保证数据的一致性(大小写、格式等)。在触发器中执行这种类型的处理的优点是它总是进行这种处理,而且是透明地进行,与客户机应用无关。
  • 触发器的一种非常有意义的使用是创建审计跟踪。使用触发器,把更改(如果需要,甚至还有之前和之后的状态)记录到另一个表非常容易。
  • 遗憾的是,MySQL触发器中不支持CALL语句。这表示不能从触发器内调用存储过程。所需的存储过程代码需要复制到触发器内。

管理事务处理

事务处理

InnoDB支持事务处理

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

事务处理是一种机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完整的操作结果。利用事务处理,可以保证一组操作不会中途停止,它们或者作为整体执行,或者完全不执行(除非明确指示)。如果没有错误发生,整组语句提交给(写到)数据库表。如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态。

事务处理过程:

  1. 检查数据库中是否存在相应的客户,如果不存在,添加他/她。
  2. 提交客户信息。
  3. 检索客户的ID。
  4. 添加一行到orders表。
  5. 如果在添加行到orders表时出现故障,回退。
  6. 检索orders表中赋予的新订单ID。
  7. 对于订购的每项物品,添加新行到orderitems表。
  8. 如果在添加新行到orderitems时出现故障,回退所有添加的orderitems行和orders行。
  9. 提交订单信息。

在使用事务和事务处理时,有几个关键词汇反复出现。下面是关于事务处理需要知道的几个术语:

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

第二十六章 控制事务处理

在使用事务和事务处理时,有几个关键词汇反复出现。下面是关于 事务处理需要知道的几个术语:

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

使用ROLLBACK

ROLLBACK撤回MySQL语句

SELECT * FROM ordertitals;
START TRANSCITION;
DELETE FROM ordertotals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;
复制代码

这个例子从显示ordertotals表的内容开始。首先执行一条SELECT以显示该表不为空。然后开始一 个事务处理,用一条DELETE语句删除ordertotals中的所有行。另一条SELECT语句验证ordertotals确实为空。这时用一条ROLLBACK语句回退START TRANSACTION之后的所有语句,最后一条SELECT语句显示该表不为空。

显然,ROLLBACK只能在一个事务处理内使用(在执行一条START TRANSACTION命令之后)。

哪些语句可以回退?

事务处理用来管理INSERT、UPDATE和 DELETE语句。你不能回退SELECT语句。(这样做也没有什么意义。)你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句,但如果你执行回退,它们不会被撤销。

使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交(implicit commit),即提交(写或保存)操作是自动 进行的。 但是,在事务处理块中,提交不会隐含地进行。为进行明确的提交,使用COMMIT语句,如下所示:

START TRAMSCATION;
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
复制代码

在这个例子中,从系统中完全删除订单20010。因为涉及更新两个数据库表orders和orderItems,所以使用事务处理块来保证订单不被部分删除。最后的COMMIT语句仅在不出错时写出更改。如果第一条DELETE起作用,但第二条失败,则DELETE不会提交(实际上,它是被自动撤销的)。

使用保留点

为了支持回退部分事务处理,必须能在事务处理块中合适的位置放置占位符 SAVEPOINT

SAVEPOINT delete1;
ROLLBACK TO delete1;
复制代码

每个保留点都取标识它的唯一名字,以便在回退时,MySQL直到要回退到何处。

更改默认的提交行为

默认MySQL行为是自动提交所有更改,为指示MySQL不自动提交更改,需要使用

SET autocommit=0;
复制代码
文章分类
后端
文章标签