MySQL 更新 SET 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11763599/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Update SET value
提问by Brandon Wilson
I am trying to create a Triggerthat will run through some IF ELSEIFstatements and check the new value is NULLhowever it only goes to the first IFstatement.
我正在尝试创建一个Trigger将运行一些IF ELSEIF语句并检查新值的方法，NULL但是它只出现在第一个IF语句中。
This Triggeris AFTER UPDATE. My question is if I am only SETone column value what are the others SETto, are they NULLor what. How can I test this column if it is not SETon this UPDATEcommand.
这Trigger是AFTER UPDATE。我的问题是，如果我只是SET一列值，其他列值是什么SET，是它们NULL还是什么。如果不在SET此UPDATE命令中，如何测试此列。
Example Update:
示例更新：
UPDATE `stations_us`.`current_prices` SET `midPrice` = '3.59' WHERE `current_prices`.`_id` =1;
There are other columns that do not update at this time but can be updated based on the PHP Script.
还有其他列目前不更新，但可以根据 PHP Script 进行更新。
Trigger:
扳机：
BEGIN
-- Definition start
IF(NEW.regPrice IS NOT NULL) THEN
INSERT INTO prices (stationID, price, type,
prevPrice, prevDate, dateCreated, apiKey, uid)
VALUES(NEW.stationID, NEW.regPrice, 'reg', OLD.regPrice,
OLD.regDate, NEW.regDate, NEW.lastApiUsed, NEW.lastUpdatedBy);
ELSEIF(NEW.midPrice IS NOT NULL) THEN
INSERT INTO prices (stationID, price, type,
prevPrice, prevDate, dateCreated, apiKey, uid)
VALUES(NEW.stationID, NEW.midPrice, 'mid', OLD.midPrice,
OLD.midDate, NEW.midDate, NEW.lastApiUsed, NEW.lastUpdatedBy);
ELSEIF(NEW.prePrice IS NOT NULL) THEN
INSERT INTO prices (stationID, price, type,
prevPrice, prevDate, dateCreated, apiKey, uid)
VALUES(NEW.stationID, NEW.prePrice, 'pre', OLD.prePrice,
OLD.preDate, NEW.preDate, NEW.lastApiUsed, NEW.lastUpdatedBy);
END IF;
-- Definition end
END
回答by spencer7593
You can't tell inside the trigger which columns were referenced in the SET clause.
您无法在触发器内部判断 SET 子句中引用了哪些列。
You can only check whether the value of colwas changed by the statement, by comparing the values of NEW.coland OLD.col.
您只能检查是否值col由语句更改，通过比较的数值NEW.col和OLD.col。
IF NOT (NEW.regPrice <=> OLD.regPrice)
Testing (NEW.col IS NOT NULL)is not sufficient to tell whether the value has been changed. Consider the case when old value is e.g. 4.95 and the new value is NULL, it looks as if you probably want to detect that change.
测试(NEW.col IS NOT NULL)不足以判断值是否已更改。考虑旧值是例如 4.95 并且新值是 NULL 的情况，看起来好像您可能想要检测该更改。
Note that an UPDATE statement may set values for more than one column:
请注意，UPDATE 语句可能会为多个列设置值：
UPDATE foo SET col1 = 'a', col2 = 'b', col3 = 'c' WHERE ...
Also note that a column may be set to NULL, and a column can be set to its existing value
还要注意，一列可能被设置为NULL，而一列可以被设置为其现有值
UPDATE foo SET col1 = 'a', col2 = NULL, col3 = col3 WHERE ...
In this case, inside your trigger, NEW.col2would evaluate to NULL, and (NEW.col3 <=> OLD.col3)will evaluate to 1 (TRUE), because the OLD and NEW values are equal.
在这种情况下，在触发器内部，NEW.col2将评估为 NULL，(NEW.col3 <=> OLD.col3)并将评估为 1 (TRUE)，因为 OLD 和 NEW 值相等。
(What your AFTER UPDATE trigger is really seeing is the value that got stored, which is not necessarily the value from your UPDATE statement if a BEFORE INSERT trigger has mucked with the value, and supplied a different value for that column.)
（您的 AFTER UPDATE 触发器真正看到的是存储的值，如果 BEFORE INSERT 触发器与该值混淆并为该列提供了不同的值，则它不一定是您的 UPDATE 语句中的值。）
If your intent is to log changes in values, you probably do NOT want the ELSIF, but instead want to run through all the columns to check whether it's value has been changed or not.
如果您的意图是记录值的更改，您可能不想要 ELSIF，而是想要遍历所有列以检查它的值是否已更改。
