MySQL IF NOT EXISTS 在触发器中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5943650/
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
IF NOT EXISTS in trigger
提问by Jull
I have tow tables concept_access and concept_access_log. I want to create a trigger that works every time something is deleted from concept_access, check if there is similar record in log table and if not, inserts new one before it is deleted from concept_access.
我有两个表concept_access 和concept_access_log。我想创建一个触发器，每次从concept_access 中删除某些内容时都会起作用，检查日志表中是否有类似的记录，如果没有，则在从concept_access 中删除之前插入新的记录。
I modified trigger and now it looks like this:
我修改了触发器，现在看起来像这样：
DROP TRIGGER IF EXISTS before_delete_concept_access;
DELIMITER //
CREATE TRIGGER before_delete_concept_access
BEFORE DELETE ON `concept_access` FOR EACH ROW
BEGIN
IF (SELECT 1 FROM concept_access_log WHERE map=OLD.map
AND accesstype=OLD.accesstype AND startdate=OLD.startdate AND stopdate=OLD.stopdate) IS NULL THEN
INSERT INTO concept_access_log (map, accesstype, startdate, stopdate)
VALUES (OLD.map, OLD.accesstype, OLD.startdate, OLD.stopdate);
END IF;
END//
DELIMITER ;
Sample data in concept_access before delete:
删除前concept_access中的示例数据：
map accesstype startdate stopdate
1 public NULL NULL
1 loggedin 2011-05-11 NULL
1 friends NULL NULL
Log table already has first 2 rows. And they are exactly the same as in concept_access. When I delete first row from concept_access table, I get this in log table:
日志表已经有前 2 行。它们与concept_access中的完全相同。当我从concept_access表中删除第一行时，我在日志表中得到了这个：
map accesstype startdate stopdate
1 public NULL NULL
1 loggedin 2011-05-11 NULL
1 friends NULL NULL
1 public NULL NULL
While it is not supposed to insert anything because (1,public,null,null) already exists there.
虽然它不应该插入任何东西，因为 (1,public,null,null) 已经存在于那里。
This table has no primary key. I was not creating structure, so don't ask me why. Changing it will ruin a lot of already existing functionality. I just need to keep log of what was removed from table concept_accessand store it in log without duplicates.
该表没有主键。我不是在创建结构，所以不要问我为什么。改变它会破坏很多已经存在的功能。我只需要记录从表中删除的内容concept_access并将其存储在日志中而没有重复。
I would really appreciate, if anyone can figure out what is going wrong.
我真的很感激，如果有人能弄清楚出了什么问题。
回答by neocanable
DROP TRIGGER IF EXISTS before_delete_concept_access;
DELIMITER //
CREATE TRIGGER before_delete_concept_access
BEFORE DELETE ON `concept_access` FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) FROM concept_access_log WHERE map=OLD.map
AND accesstype=OLD.accesstype AND startdate=OLD.startdate AND stopdate=OLD.stopdate) = 0 THEN
INSERT INTO concept_access_log (map, accesstype, startdate, stopdate)
VALUES (OLD.map, OLD.accesstype, OLD.startdate, OLD.stopdate);
END IF;
END//
DELIMITER ;
I am not using not exists, just test if the match count greater than 0
我没有使用not exists，只是测试匹配计数是否大于 0
your code runs well on my machine, what's your MySQL version?
你的代码在我的机器上运行良好，你的 MySQL 版本是什么？
mysql> select version();
+------------+
| version() |
+------------+
| 5.1.56-log |
+------------+
1 row in set (0.00 sec)
回答by pilcrow
Instead of this
而不是这个
... WHERE map = OLD.map ...
-- map OLD.map bool
-- --- ------- ----
-- 'a' 'a' TRUE
-- 'a' 'b' FALSE
-- 'a' NULL FALSE
-- NULL 'b' FALSE
-- NULL NULL FALSE <-- Undesired! We want this to be TRUE
try <=>, which is MySQL's answerto the SQL standard IS (NOT) DISTINCT FROMpredicate:
try <=>，这是MySQL对 SQL 标准IS (NOT) DISTINCT FROM谓词的回答：
... WHERE map <=> OLD.map ...
-- TRUE when both have the same non-NULL value or when both are NULL
-- Equivalents:
-- WHERE (map = OLD.map OR (map IS NULL AND OLD.map IS NULL))
-- WHERE (map = OLD.map OR COALESCE(map, OLD.map) IS NULL)
