插入(如果存在)使用触发器更新 [英] insert, if exist update using trigger

问题描述

我想为我的网站实施一个查看系统".这是我的表的结构:

I want to implement a Viewed system for my website. Here is the structure of my tables:

// table1
id | user_or_ip | post_id | date_time   // inserting new row for each viewed

// table2
id | post_id | total_viewed             // getting the number of total viewed for each post

现在，在table1中插入后，我需要一个用于插入/更新table2的触发器.

Now I need to a trigger for insert/update table2 after insert in table1.

我 认为我必须使用on duplicate key.

推荐答案

您可以轻松地做到这一点.

You can do this fairly easily.

具有以下2个示例表:-

With the following 2 example table:-

CREATE TABLE table1
(
    id  INT NOT NULL AUTO_INCREMENT,
    user_or_ip  VARCHAR(255),
    post_id INT,
    date_time   DATETIME,
    PRIMARY KEY (id)
);

CREATE TABLE table2
(
    id  INT NOT NULL AUTO_INCREMENT,
    post_id INT,
    total_viewed    INT,
    PRIMARY KEY (id),
    UNIQUE KEY post_id (post_id)
);

您可以在表1上使用以下触发器来计算计数并将其插入表2:-

you can use the following trigger on table 1 to calculate the count and insert it to table 2:-

CREATE TRIGGER `trig_1` AFTER INSERT ON `table1`
 FOR EACH ROW BEGIN
INSERT INTO table2(post_id, total_viewed) 
SELECT post_id, COUNT(*)
FROM table1
WHERE post_id = NEW.post_id
GROUP BY post_id
ON DUPLICATE KEY UPDATE total_viewed = VALUES(total_viewed);
END

请注意，如果您确定永远不会出现错误，则可以在ON DUPLICATE KEY子句中插入1并将其设置为total_count + 1.但是，如果有任何失败阻止触发，则该post_id的计数将永远是错误的:-

Note that if you are certain that there will never be an error you could just insert a count of 1 and set it to total_count + 1 in the ON DUPLICATE KEY clause. But if anything fails that prevents the trigger the counts will be forever wrong for that post_id:-

CREATE TRIGGER `trig_1` AFTER INSERT ON `table1`
 FOR EACH ROW BEGIN
INSERT INTO table2(post_id, total_viewed) 
VALUES(NEW.post_id, 1)
ON DUPLICATE KEY UPDATE total_viewed = total_viewed + 1;
END

还请注意，使用表1中post_id上的索引，获取计数的子查询会更有效

Note also that the sub query to get the count will be more efficient with an index on post_id in table1

这篇关于插入(如果存在)使用触发器更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！