第23章使用存储过程

一、存储过程基础

1.1 存储过程的定义：存储过程是为以后使用而保存的一条或多条MySQL语句的集合，可视为批处理文件，它不仅用于批处理，还能封装复杂的业务逻辑。在处理一些需要多条语句完成的操作时，如处理订单时涉及核对库存、预定物品、与供应商交互以及通知客户等一系列操作，存储过程可将这些操作集中起来，方便管理和使用。

1.2 为什么使用存储过程：使用存储过程有诸多优点：

简化复杂操作：将复杂的业务逻辑封装在存储过程中，使应用程序只需调用存储过程，而无需关心具体的实现细节，降低了代码复杂度。
保证数据完整性：所有开发人员和应用程序都使用同一经过测试的存储过程，确保了数据处理的一致性，减少错误发生的可能性。
简化变动管理：当表结构、业务逻辑等发生变化时，只需修改存储过程的代码，使用它的人员无需了解具体变化，降低了维护成本。
提高性能：存储过程在服务器端执行，比单独执行多条SQL语句速度更快，能有效提升应用程序的响应速度。
增强安全性：通过限制对基础数据的直接访问，使用存储过程可以减少数据讹误的风险，同时可以对用户的操作进行更细粒度的控制。

二、使用存储过程

2.1 执行存储过程：MySQL使用CALL语句来执行存储过程，语法为CALL procedure_name(parameters)，其中procedure_name是存储过程的名称，parameters是传递给存储过程的参数（如果有的话）。例如，执行名为productpricing的存储过程：

CALL productpricing();

2.2 创建存储过程：创建存储过程使用CREATE PROCEDURE语句。例如，创建一个返回产品平均价格的存储过程：

-- 创建存储过程
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END;

在mysql命令行实用程序中创建存储过程时，由于默认的语句分隔符;会与存储过程内部的SQL语句分隔符冲突，需要临时更改命令行实用程序的语句分隔符。例如：

DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage
    FROM products;
END //
DELIMITER ;

2.3 删除存储过程：使用DROP PROCEDURE语句删除存储过程，语法为DROP PROCEDURE procedure_name。例如，删除名为productpricing的存储过程：

DROP PROCEDURE productpricing;

若要在存储过程不存在时不产生错误，可使用DROP PROCEDURE IF EXISTS procedure_name。
2.4 使用参数：存储过程可以接受参数，参数分为IN（传递给存储过程）、OUT（从存储过程传出）和INOUT（对存储过程传入和传出）三种类型。例如，修改productpricing存储过程，使其返回产品的最低、最高和平均价格：

CREATE PROCEDURE productpricing(
    OUT p1 DECIMAL(8, 2),
    OUT ph DECIMAL(8, 2),
    OUT pa DECIMAL(8, 2)
)
BEGIN
    SELECT Min(prod_price) INTO p1
    FROM products;
    SELECT Max(prod_price) INTO ph
    FROM products;
    SELECT Avg(prod_price) INTO pa
    FROM products;
END;

调用该存储过程时，需要指定接收结果的变量：

CALL productpricing(@price_low, @pricehigh, @priceaverage);

可以通过SELECT语句查看变量的值：

SELECT @priceaverage;

2.5 建立智能存储过程：智能存储过程包含业务规则和智能处理。例如，创建一个计算订单合计并根据条件添加营业税的存储过程：

-- Name: ordertotal
-- Parameters: onumber = order number, ototal = order total variable, taxable = 0 if not taxable, 1 if taxable
CREATE PROCEDURE ordertotal(
    IN onumber INT,
    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 out variable
    SELECT total INTO ototal;
END;

调用该存储过程时，可以根据需求传入不同的参数，实现不同的计算逻辑：

CALL ordertotal(20005, 0, @total);
SELECT @total;

2.6 检查存储过程：使用SHOW CREATE PROCEDURE procedure_name语句可显示创建存储过程的CREATE语句；使用SHOW PROCEDURE STATUS可获得存储过程的详细信息，包括创建时间、创建者等。若要限制其输出，可使用LIKE指定过滤模式，如SHOW PROCEDURE STATUS LIKE 'ordertotal'; 。

三、小结

本章介绍了存储过程的概念、使用场景以及创建、执行、删除和检查存储过程的方法，还讲解了如何使用参数和建立包含业务逻辑的智能存储过程。存储过程在数据库开发中具有重要作用，能有效提高代码的可维护性、安全性和性能。

实战案例

实战数据准备

建表语句：创建orders表和orderitems表，并建立关联关系。

CREATE TABLE orders (
    order_num INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATETIME,
    cust_id INT
);

CREATE TABLE orderitems (
    order_num INT,
    order_item INT,
    prod_id CHAR(10),
    quantity INT,
    item_price DECIMAL(8, 2),
    PRIMARY KEY (order_num, order_item),
    FOREIGN KEY (order_num) REFERENCES orders(order_num)
);

插入数据语句：向表中插入测试数据。

INSERT INTO orders (order_date, cust_id) VALUES ('2024 - 01 - 01 10:00:00', 1);
INSERT INTO orderitems (order_num, order_item, prod_id, quantity, item_price) VALUES (1, 1, 'P001', 2, 10.00), (1, 2, 'P002', 3, 15.00);

题目与解析

题目1：创建并执行简单存储过程：创建一个存储过程，查询orders表中的所有订单信息，并执行该存储过程。

解析：使用CREATE PROCEDURE创建存储过程，在过程中使用SELECT语句查询orders表，然后使用CALL语句执行存储过程。
脚本答案：

-- 创建存储过程
CREATE PROCEDURE get_all_orders()
BEGIN
    SELECT *
    FROM orders;
END;
-- 执行存储过程
CALL get_all_orders();

题目2：创建带参数的存储过程：创建一个存储过程，根据传入的订单号，计算该订单的总金额，并返回结果。

解析：定义一个接受订单号作为参数的存储过程，在过程中通过联结orderitems表计算总金额，使用OUT参数返回结果。
脚本答案：

-- 创建存储过程
CREATE PROCEDURE calculate_order_total(
    IN order_num_param INT,
    OUT total_amount DECIMAL(8, 2)
)
BEGIN
    SELECT Sum(item_price * quantity)
    INTO total_amount
    FROM orderitems
    WHERE order_num = order_num_param;
END;
-- 调用存储过程并查看结果
CALL calculate_order_total(1, @total);
SELECT @total;

题目3：创建智能存储过程：创建一个存储过程，根据传入的订单号和是否含税的标志，计算订单总金额（含税或不含税），并返回结果。

解析：在存储过程中使用IF语句根据是否含税的标志进行不同的计算，实现智能处理。
脚本答案：

-- 创建存储过程
CREATE PROCEDURE calculate_order_amount(
    IN order_num_param INT,
    IN is_taxable BOOLEAN,
    OUT total_amount DECIMAL(8, 2)
)
BEGIN
    DECLARE subtotal DECIMAL(8, 2);
    DECLARE tax_rate DECIMAL(5, 2) DEFAULT 0.06;
    -- 计算订单小计
    SELECT Sum(item_price * quantity)
    INTO subtotal
    FROM orderitems
    WHERE order_num = order_num_param;
    -- 根据是否含税计算总金额
    IF is_taxable THEN
        SET total_amount = subtotal * (1 + tax_rate);
    ELSE
        SET total_amount = subtotal;
    END IF;
END;
-- 调用存储过程并查看结果
CALL calculate_order_amount(1, 1, @total);
SELECT @total;

第24章使用游标

一、游标基础

1.1 游标的定义：游标是一个存储在MySQL服务器上的数据库查询结果集。在MySQL中，使用简单的SELECT语句检索数据时，无法逐行处理结果集，而游标提供了一种在检索出来的行中前进或后退一行或多行的机制，方便对数据进行逐行处理。例如，在处理大量数据时，可能需要逐行读取并进行特定的计算或操作，游标就可满足这种需求。

1.2 游标的使用场景：游标主要用于交互式应用，在这些应用中，用户需要滚动屏幕上的数据，并对数据进行浏览或更改。不过，MySQL游标只能用于存储过程（和函数），这是与其他一些DBMS不同的地方。

二、使用游标

2.1 创建游标：在MySQL中，使用DECLARE语句创建游标。例如，定义一个名为ordernumbers的游标，用于检索所有订单的订单号：

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

在这个例子中，DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;定义了游标ordernumbers，并指定了相应的SELECT语句来获取数据。需要注意的是，此时游标只是定义，尚未执行查询检索数据。
2. 2.2 打开和关闭游标：游标定义后，需要使用OPEN CURSOR语句打开游标，此时才会执行查询并存储检索出的数据以供浏览和滚动。例如：

OPEN ordernumbers;

当游标使用完毕后，应使用CLOSE语句关闭游标，以释放游标使用的所有内部内存和资源。例如：

CLOSE ordernumbers;

如果不明确关闭游标，MySQL会在到达END语句时自动关闭它。但为了养成良好的编程习惯，建议在使用完游标后及时手动关闭。
2.3 使用游标数据：打开游标后，可以使用FETCH语句访问游标中的每一行数据。FETCH语句指定要检索的数据列，并将数据存储到指定的变量中，同时会将游标中的内部行指针向前移动，以便下一次FETCH语句检索下一行。例如，从游标中检索单个行（第一行）的订单号：

CREATE PROCEDURE processorders()
BEGIN
    DECLARE o INT;
    DECLARE ordernumbers CURSOR
        FOR
        SELECT order_num FROM orders;
    OPEN ordernumbers;
    FETCH ordernumbers INTO o;
    CLOSE ordernumbers;
END;

在上述代码中，FETCH ordernumbers INTO o;将游标ordernumbers当前行的order_num列数据存储到变量o中。若要循环检索游标中的所有数据，可以结合循环语句使用FETCH 。例如：

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;
        -- 在此处可以对o进行处理
        UNTIL done END REPEAT;
    CLOSE ordernumbers;
END;

在这个例子中，使用REPEAT - UNTIL循环结合FETCH语句逐行读取游标数据。DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;用于定义一个继续处理程序，当FETCH语句没有更多行可读取（即到达结果集末尾）时，将done变量设置为1，从而结束循环。此外，在使用游标时，DECLARE语句的发布存在特定次序，局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义，否则会产生错误消息。

三、小结

本章介绍了游标在MySQL中的概念、使用场景以及具体的使用方法，包括创建、打开、关闭游标和使用FETCH语句获取游标数据等操作。游标在需要逐行处理数据的场景中非常有用，尤其是在存储过程中处理复杂业务逻辑时，能够更灵活地操作数据。

实战案例

实战数据准备

建表语句：创建employees表。

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

插入数据语句：向表中插入测试数据。

INSERT INTO employees (emp_name, salary) VALUES ('Alice', 5000.00), ('Bob', 6000.00), ('Charlie', 5500.00);

题目与解析

题目1：使用游标计算员工平均工资：创建一个存储过程，使用游标遍历employees表，计算所有员工的平均工资，并返回结果。

解析：在存储过程中定义游标遍历employees表，累加员工工资，统计员工数量，最后计算平均工资并返回。
脚本答案：

-- 创建存储过程
CREATE PROCEDURE calculate_avg_salary(OUT avg_sal DECIMAL(10, 2))
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE current_salary DECIMAL(10, 2);
    DECLARE total_salary DECIMAL(10, 2) DEFAULT 0;
    DECLARE employee_count INT DEFAULT 0;
    DECLARE emp_cursor CURSOR
        FOR
        SELECT salary FROM employees;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN emp_cursor;
    REPEAT
        FETCH emp_cursor INTO current_salary;
        SET total_salary = total_salary + current_salary;
        SET employee_count = employee_count + 1;
    UNTIL done END REPEAT;
    CLOSE emp_cursor;
    -- 计算平均工资
    IF employee_count > 0 THEN
        SET avg_sal = total_salary / employee_count;
    ELSE
        SET avg_sal = 0;
    END IF;
END;
-- 调用存储过程并查看结果
CALL calculate_avg_salary(@average_salary);
SELECT @average_salary;

题目2：使用游标更新数据：创建一个存储过程，使用游标遍历employees表，将工资低于5500的员工工资提高10%。

解析：定义游标遍历employees表，使用IF语句判断员工工资是否低于5500，若低于则进行工资调整，通过UPDATE语句更新表数据。
脚本答案：

-- 创建存储过程
CREATE PROCEDURE update_salary()
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE current_emp_id INT;
    DECLARE current_salary DECIMAL(10, 2);
    DECLARE emp_cursor CURSOR
        FOR
        SELECT emp_id, salary FROM employees;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN emp_cursor;
    REPEAT
        FETCH emp_cursor INTO current_emp_id, current_salary;
        IF current_salary < 5500 THEN
            UPDATE employees
            SET salary = salary * 1.1
            WHERE emp_id = current_emp_id;
        END IF;
    UNTIL done END REPEAT;
    CLOSE emp_cursor;
END;
-- 调用存储过程
CALL update_salary();
-- 查看更新后的结果
SELECT * FROM employees;

第25章使用触发器

一、触发器基础

1.1 触发器的定义：触发器是MySQL中一种特殊的数据库对象，它能在特定的数据库操作（DELETE、INSERT、UPDATE）发生时自动执行一条或一组MySQL语句。例如，在增加顾客到数据库表时，自动检查电话号码格式；在订购产品时，自动从库存数量中减去订购数量等场景中，触发器都能发挥作用。

1.2 触发器的触发事件：MySQL支持的触发器触发事件包括DELETE（删除数据时触发）、INSERT（插入数据时触发）、UPDATE（更新数据时触发），其他MySQL语句不支持触发器。

二、创建触发器

2.1 创建触发器的语法：使用CREATE TRIGGER语句创建触发器，需要指定以下信息：

唯一的触发器名。
触发器关联的表。
触发器应该响应的活动（DELETE、INSERT或UPDATE）。
触发器何时执行（处理之前或之后）。
触发执行的具体语句（可以是一条或位于BEGIN和END语句之间的一组语句）。
例如，创建一个在products表插入数据后触发的简单触发器：

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

在上述代码中，newproduct是触发器名，AFTER INSERT表示在插入操作之后触发，ON products指定关联的表为products表，FOR EACH ROW表示对每个插入的行都执行后面的语句，即显示Product added消息。
2.2 注意事项：
- 触发器名必须在每个表中唯一，但不是在每个数据库中唯一。不过，为了避免后续版本可能的命名规则变化，建议在数据库范围内使用唯一的触发器名。
- 只有表支持触发器，视图和临时表不支持。
- 每个表每个事件每次只允许一个触发器，即每个表最多支持6个触发器（针对每条INSERT、UPDATE和DELETE的之前和之后）。
- 单一触发器不能与多个事件或多个表关联，如果需要对多个事件执行相同操作，应定义多个触发器。
- 触发器失败时，如果BEFORE触发器失败，MySQL将不执行请求的操作；如果BEFORE触发器或语句本身失败，MySQL将不执行AFTER触发器（如果有的话）。

三、删除触发器

使用DROP TRIGGER语句删除触发器，语法为DROP TRIGGER trigger_name 。例如，删除名为newproduct的触发器：

DROP TRIGGER newproduct;

需要注意的是，触发器不能更新或覆盖，若要修改一个触发器，必须先删除它，然后再重新创建。

四、使用触发器

4.1 INSERT触发器：INSERT触发器在INSERT语句执行之前或之后执行。在INSERT触发器代码内，可以引用一个名为NEW的虚拟表，访问被插入的行。在BEFORE INSERT触发器中，NEW中的值可以被更新；对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。例如，创建一个在orders表插入数据后返回新订单号的触发器：

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

在这个例子中，当向orders表插入新订单时，触发器会从NEW.order_num取得新生成的订单号并返回。通常，BEFORE触发器用于数据验证和净化，确保插入表中的数据符合要求。
4.2 DELETE触发器：DELETE触发器在DELETE语句执行之前或之后执行。在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，访问被删除的行。例如，创建一个在customers表删除数据前记录被删除客户信息的触发器（假设存在一个customer_backup表用于存储备份数据）：

CREATE TRIGGER backup_customer
    BEFORE DELETE
    ON customers
    FOR EACH ROW
INSERT INTO customer_backup (cust_id, cust_name, cust_address)
VALUES (OLD.cust_id, OLD.cust_name, OLD.cust_address);

在上述代码中，BEFORE DELETE表示在删除操作之前触发，通过OLD虚拟表获取即将被删除的客户信息，并插入到customer_backup表中，实现数据备份。
4.3 UPDATE触发器：UPDATE触发器在UPDATE语句执行之前或之后执行。同样可以在触发器代码内引用OLD和NEW虚拟表，分别访问更新前和更新后的行数据。例如，创建一个在products表更新数据后记录更新前价格的触发器（假设存在一个product_price_log表用于记录价格变化）：

CREATE TRIGGER log_product_price_update
    AFTER UPDATE
    ON products
    FOR EACH ROW
INSERT INTO product_price_log (prod_id, old_price, new_price)
VALUES (OLD.prod_id, OLD.prod_price, NEW.prod_price);

在这个例子中，AFTER UPDATE表示在更新操作之后触发，通过OLD和NEW虚拟表获取更新前后的产品价格，并插入到product_price_log表中，记录价格变化。

五、小结

本章详细介绍了MySQL中触发器的概念、创建和删除方法以及不同类型触发器（INSERT、DELETE、UPDATE）的使用。触发器在维护数据的完整性和一致性方面起着重要作用，通过自动执行相关操作，可以减少人为错误，提高数据库的可靠性。

实战案例

实战数据准备

建表语句：创建students表、student_backup表和student_score_log表。

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(50),
    score INT
);

CREATE TABLE student_backup (
    student_id INT,
    student_name VARCHAR(50),
    PRIMARY KEY (student_id)
);

CREATE TABLE student_score_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    old_score INT,
    new_score INT
);

插入数据语句：向students表插入测试数据。

INSERT INTO students (student_name, score) VALUES ('Alice', 85), ('Bob', 78);

题目与解析

题目1：创建INSERT触发器：创建一个在students表插入数据后，向student_backup表插入相同数据的触发器。

解析：使用CREATE TRIGGER创建INSERT触发器，在触发器内通过NEW虚拟表获取插入到students表的数据，并插入到student_backup表。
脚本答案：

-- 创建INSERT触发器
CREATE TRIGGER backup_student_insert
    AFTER INSERT
    ON students
    FOR EACH ROW
INSERT INTO student_backup (student_id, student_name)
VALUES (NEW.student_id, NEW.student_name);
-- 测试插入操作
INSERT INTO students (student_name, score) VALUES ('Charlie', 90);
-- 查看student_backup表
SELECT * FROM student_backup;

题目2：创建DELETE触发器：创建一个在students表删除数据前，将被删除数据插入到student_backup表的触发器。

解析：使用CREATE TRIGGER创建DELETE触发器，在触发器内通过OLD虚拟表获取即将被删除的数据，并插入到student_backup表。
脚本答案：

-- 创建DELETE触发器
CREATE TRIGGER backup_student_delete
    BEFORE DELETE
    ON students
    FOR EACH ROW
INSERT INTO student_backup (student_id, student_name)
VALUES (OLD.student_id, OLD.student_name);
-- 测试删除操作
DELETE FROM students WHERE student_name = 'Bob';
-- 查看student_backup表
SELECT * FROM student_backup;

题目3：创建UPDATE触发器：创建一个在students表更新score列数据后，将更新前后的分数记录到student_score_log表的触发器。

解析：使用CREATE TRIGGER创建UPDATE触发器，在触发器内通过OLD和NEW虚拟表获取更新前后的分数，并插入到student_score_log表。
脚本答案：

-- 创建UPDATE触发器
CREATE TRIGGER log_student_score_update
    AFTER UPDATE
    ON students
    FOR EACH ROW
    IF (OLD.score != NEW.score) THEN
        INSERT INTO student_score_log (student_id, old_score, new_score)
        VALUES (OLD.student_id, OLD.score, NEW.score);
    END IF;
-- 测试更新操作
UPDATE students SET score = 88 WHERE student_name = 'Alice';
-- 查看student_score_log表
SELECT * FROM student_score_log;

第26章管理事务处理

一、事务处理基础

1.1 事务处理的定义与作用：事务处理是一种机制，用于维护数据库的完整性，确保成批的MySQL操作要么完全执行，要么完全不执行。在关系数据库中，数据通常存储在多个相互关联的表中，如订单系统中，订单数据存储在orders和orderitems表中。在进行涉及多个表的操作时，如添加订单，可能需要多个步骤，若其中某个步骤出现故障，可能导致数据不一致。事务处理可以将这些操作作为一个整体进行管理，保证数据的一致性和完整性。

1.2 事务处理相关术语：

事务：指一组SQL语句，这些语句被视为一个整体进行处理。
回退：撤销指定SQL语句的过程，用于在事务执行过程中出现错误时，将数据库恢复到某个已知且安全的状态。
提交：将未存储的SQL语句结果写入数据库表，使事务中的更改永久生效。
保留点：事务处理中设置的临时占位符，可以对其发布回退，与回退整个事务处理不同，方便在复杂事务中进行部分回退操作。

1.3 引擎支持情况：并非所有MySQL的数据库引擎都支持明确的事务处理管理。常见的MyISAM引擎不支持，而InnoDB引擎支持。因此，在需要事务处理功能时，应确保使用支持事务的引擎，本书中的样例表大多使用InnoDB引擎就是这个原因。

二、控制事务处理

2.1 标识事务开始：使用START TRANSACTION语句标识事务的开始，告知MySQL后续的SQL语句将作为一个事务进行处理。例如：

START TRANSACTION;

2.2 使用ROLLBACK：ROLLBACK命令用于回退（撤销）事务处理中已执行的SQL语句，将数据库恢复到事务开始前的状态。例如：

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

在上述代码中，开始事务后执行了删除操作，然后使用ROLLBACK回退，最后查询ordertotals表，会发现数据恢复到了事务开始前的状态。需要注意的是，ROLLBACK只能在一个事务处理内使用（在执行START TRANSACTION命令之后），且事务处理主要用于管理INSERT、UPDATE和DELETE语句，不能回退SELECT、CREATE或DROP操作。
2.3 使用COMMIT：在事务处理块中，提交操作不会隐含地进行，需要使用COMMIT语句明确提交事务，将事务中的更改写入数据库表。例如：

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

在这个例子中，使用事务处理块删除两个相关表中的订单数据，只有当两条DELETE语句都执行成功后，通过COMMIT语句将更改写入数据库。如果其中一条DELETE语句失败，整个事务不会提交，数据将保持不变。
2.4 使用保留点：保留点用于在事务处理中设置临时占位符，以便在需要时回退到特定的位置，而不是回退整个事务。使用SAVEPOINT语句创建保留点，使用ROLLBACK TO语句回退到指定的保留点。例如：

START TRANSACTION;
DELETE FROM orderitems WHERE order_num = 20010;
SAVEPOINT delete1;
DELETE FROM orders WHERE order_num = 20010;
-- 如果第二条DELETE语句出现问题，可回退到delete1保留点
ROLLBACK TO delete1;

在上述代码中，创建了一个名为delete1的保留点，在后续操作中如果出现问题，可以回退到该保留点，而不必回退整个事务。保留点在复杂事务处理中非常有用，可以提高事务处理的灵活性。自MySQL 5以来，保留点在事务处理完成（执行ROLLBACK或COMMIT）后会自动释放，也可以使用RELEASE SAVEPOINT明确地释放保留点。
2.5 更改默认的提交行为：MySQL的默认行为是自动提交所有更改，即每条SQL语句执行后，更改立即生效。通过使用SET autocommit = 0;语句可以指示MySQL不自动提交更改，直到将autocommit设置为真为止。例如：

SET autocommit = 0;
-- 执行一系列SQL语句，这些更改不会立即提交
-- 直到执行COMMIT或ROLLBACK
SET autocommit = 1;

需要注意的是，autocommit标志是针对每个连接而不是服务器的。

三、小结

本章介绍了事务处理的重要概念和相关操作，包括事务的定义、回退、提交、保留点以及更改默认提交行为等。事务处理在保证数据库数据的完整性和一致性方面起着关键作用，特别是在涉及多个相关表的复杂操作中。合理运用事务处理机制，可以有效避免数据不一致的问题，确保数据库的可靠性。

实战案例

实战数据准备

建表语句：创建accounts表和transactions表，并建立关联关系。

CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY,
    balance DECIMAL(10, 2)
);

CREATE TABLE transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (account_id) REFERENCES accounts(account_id)
);

插入数据语句：向accounts表插入一条初始数据。

INSERT INTO accounts (balance) VALUES (1000.00);

题目与解析

题目1：简单事务处理与回退：从accounts表中取出100元（模拟支出操作），如果操作过程中出现错误（假设余额不足时视为错误），则回退事务。

解析：使用START TRANSACTION开始事务，进行余额更新操作，若更新后余额不足则使用ROLLBACK回退事务。
脚本答案：

START TRANSACTION;
-- 假设要取出100元
SET @withdrawal_amount = 100;
-- 查询当前余额
SELECT balance INTO @current_balance FROM accounts WHERE account_id = 1;
-- 判断余额是否足够
IF @current_balance >= @withdrawal_amount THEN
    UPDATE accounts SET balance = balance - @withdrawal_amount WHERE account_id = 1;
    -- 模拟插入交易记录（这里省略具体插入语句，仅示意事务关联操作）
    -- INSERT INTO transactions (account_id, amount) VALUES (1, -@withdrawal_amount);
    COMMIT;
ELSE
    ROLLBACK;
    SELECT 'Insufficient balance';
END IF;

题目2：使用保留点：在一个事务中进行两次余额操作（先取出200元，再存入50元），如果第二次操作出现问题，则回退到第一次操作后的状态。

解析：开始事务后，进行第一次余额更新操作并设置保留点，接着进行第二次操作，若第二次操作出现问题，回退到保留点。
脚本答案：

START TRANSACTION;
-- 第一次操作：取出200元
SET @first_withdrawal = 200;
UPDATE accounts SET balance = balance - @first_withdrawal WHERE account_id = 1;
-- 设置保留点
SAVEPOINT first_withdrawal_done;
-- 第二次操作：存入50元
SET @deposit = 50;
-- 模拟可能出现的错误情况，如余额不足（这里简化处理，实际可能更复杂）
SET @current_balance = (SELECT balance FROM accounts WHERE account_id = 1);
IF @current_balance < 0 THEN
    ROLLBACK TO first_withdrawal_done;
    SELECT 'Second operation failed, rolled back to first operation state';
ELSE
    UPDATE accounts SET balance = balance + @deposit WHERE account_id = 1;
    COMMIT;
END IF;

题目3：更改默认提交行为：关闭自动提交，执行一系列操作（先插入一条交易记录，再更新账户余额），然后手动提交事务。

解析：使用SET autocommit = 0;关闭自动提交，依次执行插入和更新操作，最后使用COMMIT手动提交事务。
脚本答案：

SET autocommit = 0;
-- 插入交易记录
INSERT INTO transactions (account_id, amount) VALUES (1, -150);
-- 更新账户余额
UPDATE accounts SET balance = balance - 150 WHERE account_id = 1;
-- 手动提交事务
COMMIT;
SET autocommit = 1;

网站首页 > 文章精选正文

《MySQL必知必会》_笔记09（mysql必知必会 pdf完整版）

第23章使用存储过程

一、存储过程基础

二、使用存储过程

三、小结

实战案例

第24章使用游标

一、游标基础

二、使用游标

三、小结

实战案例

第25章使用触发器

一、触发器基础

二、创建触发器

三、删除触发器

四、使用触发器

五、小结

实战案例

第26章管理事务处理

一、事务处理基础

二、控制事务处理

三、小结

实战案例

猜你喜欢

网站首页 > 文章精选 正文

《MySQL必知必会》_笔记09（mysql必知必会 pdf完整版）

第23章 使用存储过程

一、存储过程基础

二、使用存储过程

三、小结

实战案例

第24章 使用游标

一、游标基础

二、使用游标

三、小结

实战案例

第25章 使用触发器

一、触发器基础

二、创建触发器

三、删除触发器

四、使用触发器

五、小结

实战案例

第26章 管理事务处理

一、事务处理基础

二、控制事务处理

三、小结

实战案例

猜你喜欢

网站首页 > 文章精选正文

第23章使用存储过程

第24章使用游标

第25章使用触发器

第26章管理事务处理