MySQL如果存在则插入id否则插入到两个表中

Question

我已经编写了以下 SQL 语句，但它不起作用，并且无法弄清楚如何创建一个正常运行的 SQL 语句。这就是我正在尝试做的事情。

1. 检查玩家表中是否存在某个值
2. 如果该值存在，我想将忽略插入名册表
3. 如果该值不存在，我想将其插入到球员表中，并将其插入到名册表中

这是我所拥有的

IF EXISTS (SELECT id AS plyrId FROM players WHERE email = @(:e) LIMIT 1)
BEGIN
    INSERT IGNORE INTO roster
        (date, teamId, playerId)
    VALUES
        ( (:d), (:t), plyrId )
END
ELSE
BEGIN
    INSERT INTO players 
        (status, date, first_name, last_name, email) 
    VALUES 
        ( (:s), (:d), (:f), (:l), (:e) )

    INSERT IGNORE INTO roster
        (date, teamId, playerId)
    VALUES
        ( (:d), (:t), LAST_INSERT_ID() )  //LAST_INSERT_ID() -> I want it to be pulled from the last id inserted from the players table - not sure how to accomplish this
END

感谢任何帮助!

我又尝试了一次，但还是没有成功

IF (SELECT COUNT(*) FROM players WHERE email = (:e) > 0)
            INSERT IGNORE INTO roster 
                (date, teamId, playerId)
            (:d), (:t), SELECT id FROM players WHERE email =(:e)
        ELSE
        BEGIN
         .. // havent got to this part yet. it follows the same logic as the one before
        END

Answer 1

您必须在事件插入之前使用触发器

CREATE TRIGGER test_trigger BEFORE INSERT ON `players table` FOR EACH ROW SET
-- checking value exist or not, for each case write corresponding insert or inserts