Mysql - 在重复键上插入不同的值

问题描述：

I have a table called history that tracks changes made to another table. The basic idea is to be able to track who last updated a specific entry.

When I insert the first time it will create an entry that shows the action field as 'UPLOAD'. The next time that same key is entered I need it to create a new history where the action is 'UPDATE', that way I can see when it was first made and who updated that key after that.

Unfortunately ON DUPLICATE KEY INSERT... is not a mysql construct. What do I do?

So far I have:

INSERT INTO history (key,userID,action,note,date)
VALUES (?,?,?,?,?)

*Where the value of action is always 'UPLOAD', even if it's an update ** The question marks are from the prepared statement bind

If the key exists in history I need the action to change to 'UPDATE' and create a new entry - it should never change entries that already exist.

我有一个名为history的表，用于跟踪对另一个表所做的更改。基本思路是能够跟踪最后更新特定条目的人。 p>

当我第一次插入时，它将创建一个将操作字段显示为“上传”的条目。下次输入相同的密钥时，我需要它来创建一个新的历史记录，其中操作是“更新”，这样我可以看到它何时首次制作，以及之后谁更新了该密钥。 p>

不幸的是，在DUPLICATE KEY INSERT ...不是一个mysql构造。我该怎么做？ p>

到目前为止，我有： p>

  INSERT INTO历史记录（密钥，用户ID，操作，注释，日期）  
VALUES（？，？，？，？，？）
  code>  pre> 
 
  *其中action的值始终为'UPLOAD'，即使它是更新
 *  *问号来自准备好的语句bind  p> 
 
 
如果密钥存在于历史记录中，我需要将操作更改为“更新”并创建新条目 - 它应该永远不会更改已完成的条目 存在。 p> 
  div>

答

You can use triggers

CREATE TRIGGER catchHim BEFORE INSERT ON myTable
FOR EACH ROW
  BEGIN
    IF EXISTS(SELECT * FROM myTable WHERE userID=NEW.userID) THEN
        INSERT INTO history (key,userID,action,note,date) VALUES (NEW.myColumn1, NEW.myColumnForUserId, .....);
    END IF;
  END;

But they are hard to keep track of, especially that nobody uses them on MySQL.

Actually verify if that thing exists, in PHP, before making the update, then deciding whether to insert history or not.

Mysql - 在重复键上插入不同的值

相关推荐