jquery - MySQL查询仅当两个字段不存在时才插入数据

Question

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

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')

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

 #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

如何解决这个问题？

Answer 1

VALUES 不采用 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
                     );

但是，您可能打算避免在表格中使用相同电话和电子邮件的学生。如果是，则创建唯一约束或索引:

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

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

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

这在重复的情况下不执行任何操作。