如果不存在则不工作

IF NOT EXISTS(SELECT * FROM `user` WHERE `name`='Rutvij' AND `lang`='python')
    BEGIN
        INSERT INTO `user` VALUES ('Rutvij', 'python', 25)
    END
ELSE
    BEGIN
        UPDATE user SET `name`='Kanzaria' WHERE `name`='Rutvij'
    END

我正在 phpmyadmin sql 区域尝试上述查询。我正在使用xampp。它抛出错误指出

1064 - 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解要使用的正确语法 靠近 'IF EXISTS(SELECT * FROM user) SELECT name FROM use' 在第 1 行

我也尝试过下面的代码

IF NOT EXISTS(SELECT * FROM user WHERE name='Rutvij' AND lang='python')
    BEGIN
        INSERT INTO user VALUES ('Rutvij', 'python', 25)
    END
ELSE
    BEGIN
        INSERT INTO user VALUES ('Kanzaria', 'python', 25)
    END

挣扎了这么久。请帮忙。谢谢！！

MySQL 不允许if逻辑，除非您处于编程块（存储过程、触发器或函数）中。

幸运的是，您可以执行相同的操作WHERE logic:

INSERT INTO user 
    SELECT 'Rutvij', 'python', 25
    FROM DUAL
    WHERE NOT EXISTS (SELECT 1 FROM user WHERE name = 'Rutvij' AND lang = 'python')
    UNION ALL
    SELECT 'Kanzaria', 'python', 25
    FROM DUAL
    WHERE EXISTS (SELECT 1 FROM user WHERE name = 'Rutvij' AND lang = 'python');

MySQL 应该处理SELECT之前INSERT，因此只应插入一行。

或者，您可以将其分为两个INSERTs 但顺序相反：

INSERT INTO user 
    SELECT 'Kanzaria', 'python', 25
    FROM DUAL
    WHERE EXISTS (SELECT 1 FROM user WHERE name = 'Rutvij' AND lang = 'python');

INSERT INTO user 
    SELECT 'Rutvij', 'python', 25
    FROM DUAL
    WHERE NOT EXISTS (SELECT 1 FROM user WHERE name = 'Rutvij' AND lang = 'python');