php - mysql 使用触发器更新另一个表

Question

在将一行插入“video_ ratings”表后，我需要更新“video_upload”表。在此之前，我需要获取 RATINGS 列的计数，然后应该使用该值进行更新。所需的逻辑如下所述。

1. 从 video_ ratings 中计算全 0 的数量并更新 video_upload 的 RATE_BAD 列。
2. 计算 video_ ratings 中全 1 的计数并更新 video_upload 的 RATE_AVERAGE 列。
3. 从 video_ ratings 中计算所有 2 的数量并更新 video_upload 的 RATE_GOOD 列。
4. 从 video_ ratings 中计算所有 3 的数量并更新 video_upload 的 RATE_BEST 列。

video_upload 表

  CREATE TABLE `video_upload` (
  `ID` int(10) NOT NULL,
  `USER_ID` int(10) NOT NULL,
  `VIDEO_NAME` varchar(75) COLLATE utf32_sinhala_ci NOT NULL,
  `VIDEO_URL` varchar(100) COLLATE utf32_sinhala_ci DEFAULT NULL,
  `PIC1_URL` varchar(100) COLLATE utf32_sinhala_ci DEFAULT NULL,
  `PIC2_URL` varchar(100) COLLATE utf32_sinhala_ci DEFAULT NULL,
  `PIC3_URL` varchar(100) COLLATE utf32_sinhala_ci DEFAULT NULL,
  `ATT_URL` varchar(100) COLLATE utf32_sinhala_ci DEFAULT NULL,
  `TYPE` varchar(7) COLLATE utf32_sinhala_ci NOT NULL,
  `DESCRIPTION` varchar(2000) COLLATE utf32_sinhala_ci NOT NULL,
  `IsAPPROVED` int(1) NOT NULL DEFAULT '0',
  `RATE_BAD` int(10) NOT NULL,
  `RATE_AVERAGE` int(10) NOT NULL,
  `RATE_GOOD` int(10) NOT NULL,
  `RATE_BEST` int(10) NOT NULL,
  `UPLOADED_ON` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_ON` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf32 COLLATE=utf32_sinhala_ci;

video_ ratings 表

    CREATE TABLE `video_ratings` (
  `VID_ID` int(10) NOT NULL,
  `STU_ID` int(10) NOT NULL,
  `RATINGS` int(1) NOT NULL,
  `RATED_ON` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `UPDATED_ON` timestamp NOT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf32 COLLATE=utf32_sinhala_ci;

我计划使用触发器来执行此操作。我还从网上找到了一些mysql触发代码。

//Rating count trigger
create trigger rate_count_update
after insert on video_ratings for each row begin
DECLARE updatecount INT;
  set updatecount = ( select count(RATINGS) AS TOTAL from video_ratings where RATINGS='0' AND RATINGS='1' AND RATINGS='2' AND RATINGS='3' );
  if updatecount=4
    then
      update video_upload set RATE=TOTAL;
  end if;
end//

但是我无法根据我的要求修改它。请帮忙解决这个问题。

Answer 1

一些注释...

我可以肯定地保证不会有满足条件的行

  FROM video_ratings
 WHERE RATINGS='0' 
   AND RATINGS='1'
   AND RATINGS='2'

想一想。如果这些条件(比较)之一的计算结果为 TRUE，则其他比较的计算结果将为 FALSE，并且

  TRUE  AND  FALSE  AND  FALSE 

将评估为 FALSE。

因此该语句上的 COUNT() 聚合的计算结果将为 0。

<小时/>

在此更新声明中，

update video_upload set RATE=TOTAL;

如果成功，将更新 video_upload 表中的每一行。看起来我们只想更新 video_upload 表中的一行行，该行的 ID 值与该行的 VID_ID 匹配我们刚刚插入到 videos_ ratings 表中。

我们可以通过引用来获取刚刚插入的行的VID_ID列的值

 NEW.VID_ID 

在触发器主体中。我们可能想要一个如下所示的更新语句:

UPDATE video_upload v
   SET ...
 WHERE v.ID = NEW.VID_ID ; 

如果我们想要为 RATE_BAD、RATE_AVERAGE、RATE_GOOD 和 RATE_BEST 列赋值，我们需要SET 子句引用这些列...

UPDATE video_upload v
   SET v.RATE_BAD      = some_expr 
     , v.RATE_AVERAGE  = another_expr
     , v.RATE_GOOD     = expr_for_good
     , v.RATE_BEST     = expr_for_best
 WHERE v.ID = NEW.VID_ID ; 

<小时/>

也许我们想做这样的事情，获取评级计数并将这些计数存储在局部变量中，以便我们稍后在触发器中引用这些计数。

SELECT IFNULL(SUM(r.RATINGS='0'),0) AS cnt_r0
     , IFNULL(SUM(r.RATINGS='1'),0) AS cnt_r1 
     , IFNULL(SUM(r.RATINGS='2'),0) AS cnt_r2 
     , IFNULL(SUM(r.RATINGS='3'),0) AS cnt_r3 
  FROM video_ratings r 
 WHERE r.VID_ID = NEW.VID_ID
   INTO li_cnt_r0
      , li_cnt_r1
      , li_cnt_r2
      , li_cnt_r3
;

<小时/>

跟进

我建议触发器名称采用这种模式:table_name + _suffix

其中 _suffix 是“_ad”、“_ai”、“_au”、“_bd”、“_bi”、“_bu”之一(用于删除/插入/更新之后/之前)

遵循这个命名约定可以避免命名冲突，并且当我们在表上查找触发器时，我们会知道在哪里可以找到它们。通过按字母顺序列出触发器，给定表的所有触发器将按 table_name(大部分)分组在一起。 (边缘情况，我们有可能进行某种混合，即表名称以另一个表的名称开头，后跟 _a.. 或 _b..)

(在早期开发中，当您有两个表和六个触发器时，这种命名约定的优点并不明显。但是当数据库包含大量表和触发器时，它就变得很明显。)

另请注意，MySQL 仅支持给定表上的每个 BEFORE/AFTER INSERT/UPDATE/DELETE 的单个触发器。

使用局部变量有利于用户定义变量，除非有特定原因需要使用用户定义变量。

DELIMITER $$

DROP TRIGGER IF EXISTS video_ratings_ad$$

CREATE TRIGGER video_ratings_ad
AFTER DELETE ON video_ratings
FOR EACH ROW
BEGIN
   -- declare local variables
   DECLARE li_cnt_r0  BIGINT;
   DECLARE li_cnt_r1  BIGINT;
   DECLARE li_cnt_r2  BIGINT;
   DECLARE li_cnt_r3  BIGINT;
   -- get counts of ratings for specific VID_ID
   -- and store counts in local variables 
   SELECT IFNULL(SUM(r.RATINGS='0'),0) AS cnt_r0
        , IFNULL(SUM(r.RATINGS='1'),0) AS cnt_r1
        , IFNULL(SUM(r.RATINGS='2'),0) AS cnt_r2
        , IFNULL(SUM(r.RATINGS='3'),0) AS cnt_r3
     FROM video_ratings r
    WHERE r.VID_ID = OLD.VID_ID
     INTO li_cnt_r0
        , li_cnt_r1
        , li_cnt_r2
        , li_cnt_r3
   ;
   -- update target table with rating counts from local variables
   UPDATE video_upload t
      SET t.RATE_BAD      = li_cnt_r0
        , v.RATE_AVERAGE  = li_cnt_r1
        , v.RATE_GOOD     = li_cnt_r2
        , v.RATE_BEST     = li_cnt_r3
    WHERE t.ID = OLD.VID_ID
   ;
END$$

DROP TRIGGER IF EXISTS video_ratings_ai$$

CREATE TRIGGER video_ratings_ai
AFTER UPDATE ON video_ratings
FOR EACH ROW
BEGIN
   -- declare local variables
   DECLARE li_cnt_r0  BIGINT;
   DECLARE li_cnt_r1  BIGINT;
   DECLARE li_cnt_r2  BIGINT;
   DECLARE li_cnt_r3  BIGINT;
   -- get counts of ratings for specific VID_ID
   -- and store counts in local variables 
   SELECT IFNULL(SUM(r.RATINGS='0'),0) AS cnt_r0
        , IFNULL(SUM(r.RATINGS='1'),0) AS cnt_r1
        , IFNULL(SUM(r.RATINGS='2'),0) AS cnt_r2
        , IFNULL(SUM(r.RATINGS='3'),0) AS cnt_r3
     FROM video_ratings r
    WHERE r.VID_ID = NEW.VID_ID
     INTO li_cnt_r0
        , li_cnt_r1
        , li_cnt_r2
        , li_cnt_r3
   ;
   -- update target table with rating counts from local variables
   UPDATE video_upload t
      SET t.RATE_BAD      = li_cnt_r0
        , v.RATE_AVERAGE  = li_cnt_r1
        , v.RATE_GOOD     = li_cnt_r2
        , v.RATE_BEST     = li_cnt_r3
    WHERE t.ID = NEW.VID_ID
   ;
END$$

DROP TRIGGER IF EXISTS video_ratings_au$$

CREATE TRIGGER video_ratings_au
AFTER UPDATE ON video_ratings
FOR EACH ROW
BEGIN
   -- declare local variables
   DECLARE li_cnt_r0  BIGINT;
   DECLARE li_cnt_r1  BIGINT;
   DECLARE li_cnt_r2  BIGINT;
   DECLARE li_cnt_r3  BIGINT;
   IF( OLD.RATINGS <=> NEW.RATINGS 
   -- if VID_ID and RATINGS is not changed, we can skip getting counts
   IF( NEW.VID_ID <=> OLD.VID_ID AND NEW.RATINGS <=> OLD.RATINGS )
   THEN BEGIN END
   ELSE
      -- get counts of ratings for OLD.VID_ID
      -- and store counts in local variables
      SELECT IFNULL(SUM(r.RATINGS='0'),0) AS cnt_r0
           , IFNULL(SUM(r.RATINGS='1'),0) AS cnt_r1
           , IFNULL(SUM(r.RATINGS='2'),0) AS cnt_r2
           , IFNULL(SUM(r.RATINGS='3'),0) AS cnt_r3
        FROM video_ratings r
       WHERE r.VID_ID = OLD.VID_ID
        INTO li_cnt_r0
           , li_cnt_r1
           , li_cnt_r2
           , li_cnt_r3
      ;
      -- update target table with rating counts from local variables
      UPDATE video_upload t
         SET t.RATE_BAD      = li_cnt_r0
           , v.RATE_AVERAGE  = li_cnt_r1
           , v.RATE_GOOD     = li_cnt_r2
           , v.RATE_BEST     = li_cnt_r3
       WHERE t.ID = OLD.VID_ID
      ;
      IF( NEW.VID_ID <=> OLD.VID_ID )
      THEN BEGIN END
      ELSE
         -- get counts of ratings for specific VID_ID
         -- and store counts in local variables 
         SELECT IFNULL(SUM(r.RATINGS='0'),0) AS cnt_r0
              , IFNULL(SUM(r.RATINGS='1'),0) AS cnt_r1
              , IFNULL(SUM(r.RATINGS='2'),0) AS cnt_r2
              , IFNULL(SUM(r.RATINGS='3'),0) AS cnt_r3
           FROM video_ratings r
          WHERE r.VID_ID = NEW.VID_ID
           INTO li_cnt_r0
              , li_cnt_r1
              , li_cnt_r2
              , li_cnt_r3
         ;
         -- update target table with rating counts from local variables
         UPDATE video_upload t
            SET t.RATE_BAD      = li_cnt_r0
              , v.RATE_AVERAGE  = li_cnt_r1
              , v.RATE_GOOD     = li_cnt_r2
              , v.RATE_BEST     = li_cnt_r3
          WHERE t.ID = NEW.VID_ID
         ;
      END IF;
   END IF;
END$$

DELIMITER ;