主页 / dba / 问题 / 10657
Accepted
Mark D
Asked: 2012-01-14 10:49:20 +0800 CST

从触发器调用存储过程

  • 772

我使用以下语法在 mysql 中创建了一个存储过程。

DROP PROCEDURE IF EXISTS `sp-set_comment_count`;

DELIMITER $$

CREATE PROCEDURE `sp_set-comment_count` (IN _id INT)
BEGIN
   -- AC   - AllCount
   DECLARE AC INT DEFAULT 0;

   SELECT COUNT(*) AS ac
     INTO AC
     FROM usergroups AS ug
LEFT JOIN usergroup_comments AS ugm ON ugm.`gid` = ug.`id`
LEFT JOIN mediagallery AS dm ON ugm.mid = dm.`id`
    WHERE dm.`status` NOT IN (200, 201, 202, 203, 204, 205)
      AND ug.`id` = _id;

   UPDATE usergroups
      SET allCount = AC,
    WHERE usergroups.`id` = _id;

END $$
DELIMITER ;

仅供参考，我已经大大简化了存储过程，但我知道它可以正常工作，没有任何问题。

我想要做的是从 usergroup_comments 设置一个触发器，就像这样工作。

DROP TRIGGER IF EXISTS `usergroups_comments_insert` 

CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
    FOR EACH ROW
    BEGIN
       CALL sp-set-comment_count(NEW.`gid`);
    END;

但是由于某种原因，每次我做 mysql 都会向我抛出一个错误，说明第 4 行存在语法错误并没有帮助。

我梳理了 mysql 文档，发现了一些关于触发器限制的信息，但发现它相当复杂。

http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html

任何想法都会有所帮助。

mysql stored-procedures

4 个回答

  1. 永远不要从触发器中调用存储过程是有充分理由的。

    触发器本质上是存储过程。他们的行动几乎是难以回滚的。即使所有底层表都是 InnoDB，您也会遇到成比例的共享行锁和排他行锁带来的烦人的间歇性。如果触发器正在操作表，其中INSERT 和 UPDATE 被停滞以在每次调用触发器时执行重型MVCC ，就会出现这种情况。

    不要忘记触发器需要开销。事实上，根据MySQL Stored Procedure Programming，“Trigger Overhead”标题下的第 256 页说以下内容：

    重要的是要记住，触发器必然会增加它们所应用的 DML 语句的开销。实际开销将取决于触发器的性质，但是 --- 由于所有 MySQL 触发器都执行 FOR EACH ROW --- 对于处理大量行的语句，开销会迅速累积。因此，您应该避免在触发器中放置任何昂贵的 SQL 语句或过程代码。

    第 529-531 页给出了触发开销的扩展说明。该部分的结论如下：

    这里的教训是：由于触发器代码将对受 DML 语句影响的每一行执行一次，因此触发器很容易成为 DML 性能中最重要的因素。触发器主体内的代码需要尽可能轻量级，尤其是触发器中的任何 SQL 语句都应尽可能由索引支持。

    我在之前的文章中解释了触发器的其他令人讨厌的方面。

    概括

    我强烈建议不要从 Trigger 调用任何存储过程，即使 MySQL 允许。您应该查看 MySQL 5.5 的当前限制。

    • 25
  2. Best Answer

    所以事实证明这是困扰我几个小时的问题，信不信由你。

    我可以轻松定义一个名为 sp_set-comment_count 的过程。但是，当调用所述过程时，它的工作方式不同。

    CALL sp_set-comment_count（我只能假设这是因为服务器将 - 解释为减号）。

    从那以后，我将存储过程名称更改为仅使用下划线，它似乎已经解决了所有问题。

    • 8

  3. 如果它说语法错误，则很可能您忘记更改分隔符（就像您对存储过程所做的那样）。所以你需要

    DELIMITER $$
CREATE TRIGGER `usergroups_comments_insert` AFTER INSERT ON `usergroups_comment`
FOR EACH ROW
BEGIN
   CALL sp_set_count(NEW.`gid`);
END;
$$
    • 5

  4. 看起来后面的逗号AC是语法错误：

    UPDATE usergroups
   SET allCount = AC,
 WHERE ........
    • 1

相关问题