MySQL的错误信息解读：在存储过程/触发器中无法更新表格，因为该表格已被调用该存储过程/触发器的语句所使用

MySQL数据库是目前应用最广的关系型数据库之一。在使用MySQL数据库时，可能会遇到该错误提示信息：MySQL Error: Can’t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger，它告诉我们，在存储过程或触发器中我们不能更新表格，因为该表格已被调用此存储过程/触发器的语句所使用。

阅读更多：MySQL 教程

示例

为了更好地理解这个错误提示信息，我们可以通过一个简单的示例来说明。假设我们要向customers表格中添加一条数据，并在添加后更新orders表格。我们可以使用以下存储过程：

DELIMITER //
CREATE PROCEDURE add_customer_to_order(IN customerID INT, IN orderID INT)
BEGIN
INSERT INTO customers (customer_id, customer_name) VALUES (customerID, 'Test');
UPDATE orders SET customer_id = customerID WHERE order_id = orderID;
END //
DELIMITER ;

然而，如果我们试图将add_customer_to_order存储过程与update_order_avail_qty触发器结合使用，我们会得到上述的错误信息。对于以下触发器：

DELIMITER //
CREATE TRIGGER update_order_avail_qty AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE products SET avail_qty = avail_qty - NEW.qty WHERE product_id = NEW.product_id;
END //
DELIMITER ;

因为在add_customer_to_order存储过程中，我们试图更新由update_order_avail_qty触发器使用的orders表格，所以MySQL会拒绝并返回错误信息。

解决方法

为了解决这个问题，我们可以使用以下两种方法：

将UPDATE语句移动到存储过程之外，例如：

DELIMITER //
CREATE PROCEDURE add_customer_to_order(IN customerID INT, IN orderID INT)
BEGIN
INSERT INTO customers (customer_id, customer_name) VALUES (customerID, 'Test');
END //
DELIMITER ;

UPDATE orders SET customer_id = customerID WHERE order_id = orderID;

在存储过程和触发器中使用临时表格，使它们不影响彼此：

DELIMITER //
CREATE PROCEDURE add_customer_to_order(IN customerID INT, IN orderID INT)
BEGIN
DECLARE temp_customer_id INT;
INSERT INTO customers (customer_id, customer_name) VALUES (customerID, 'Test');
SELECT customer_id INTO temp_customer_id FROM customers WHERE customer_id = customerID;
UPDATE orders SET customer_id = temp_customer_id WHERE order_id = orderID;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER update_order_avail_qty AFTER INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE temp_avail_qty INT;
SELECT avail_qty INTO temp_avail_qty FROM products WHERE product_id = NEW.product_id;
UPDATE products SET avail_qty = temp_avail_qty - NEW.qty WHERE product_id = NEW.product_id;
END //
DELIMITER ;

通过使用临时表格，我们可以避免存储过程和触发器之间的互相影响，从而成功地更新表格。

总结

在MySQL数据库中，我们应该注意存储过程和触发器之间的相互影响。如果存储过程或触发器试图更新已被另一个存储过程或触发器引用的表格，就会出现Can’t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger的错误信息，这时候需要我们通过移动UPDATE语句或使用临时表格的方法来解决问题。