MySQL:插入不存在的地方

Question

如果用户表中不存在，我正在尝试插入新用户，我尝试了此查询但收到错误:

INSERT INTO USER (name,email)
VALUES ('John','john@mmm.com')
WHERE NOT EXISTS
    (SELECT id FROM USER WHERE email = 'john@mmm.com')

如果不存在，如何插入用户？

错误:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where not exists (select id from user where email = 'john@mmm.com')' at line 5

谢谢，

Answer 1

使用 insert . . . select :

INSERT INTO USER (name, email)
    SELECT 'John', 'john@mmm.com'
    WHERE NOT EXISTS
        (SELECT id FROM USER WHERE email = 'john@mmm.com');

我会这样写:

INSERT INTO USER (name, email)
    SELECT name, email
    FROM (SELECT 'John' as name, 'john@mmm.com' as email) t
    WHERE NOT EXISTS (SELECT 1 FROM USER u WHERE u.email = t.email);

但更好的方法可能是放置一个唯一索引，以便数据库保护数据:

create unique index idx_users_email on user(email);