MySQL查询只在不存在两个字段时插入数据。

I have a students table, I would like to insert data if the email and phone number is not exist in the table. I tried this query,

我有一个学生表，我想插入数据，如果电子邮件和电话号码不存在于表格中。我试着这个查询,

INSERT INTO studesnts (name, phone, email, address) 
  VALUES ('vinod','9999999999','xyz@example.com','my address') 
  WHERE NOT EXISTS (SELECT * FROM studesnts WHERE phone ='9999999999' AND email='xyz@example.com')

But this query returns an error,

但是这个查询返回一个错误，

 #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 * FROM studesnts ( WHERE phone ='9999999999' ' at line 2

How do fix this?

如何解决这个问题呢?

1 个解决方案

#1

VALUES does not take a WHERE clause. You can use SELECT instead:

值不接受WHERE子句。您可以使用SELECT代替:

INSERT INTO students (name, phone, email, address) 
    SELECT s.*
    FROM (SELECT 'vinod' as name, '9999999999' as phone, 'xyz@example.com' as email, 'my address' as address) s
    WHERE NOT EXISTS (SELECT 1
                      FROM students s2
                      WHERE s2.phone = s.phone AND s2.email = s.email
                     );

However, you probably intend avoiding students that have the same phone and email in the table. If so, create a unique constraint or index:

然而，你可能有意避开那些在桌子上有相同电话和电子邮件的学生。如果是这样，创建一个唯一的约束或索引:

create unique index unq_students_phone_email on students(phone, email);

That way, the database guarantees the integrity of the data. Under normal circumstances that will result in an error. If you just want the duplicate insert to be ignored, you can do:

这样，数据库就保证了数据的完整性。在正常情况下会导致错误。如果您只是想要忽略重复插入，您可以这样做:

INSERT INTO students (name, phone, email, address) 
    VALUES ('vinod', '9999999999', 'xyz@example.com', 'my address') 
    ON DUPLICATE KEY UPDATE phone = VALUES(phone);

This does a no-op in the case of duplicates.

这在重复的情况下是不允许的。

#1