MySQL在DUPLICATE KEY UPDATE语法错误

嘿,我想知道如果有人可以发现我的SQL语句中的任何错误。 我希望它插入一个新的logging到我的表中,如果一个不存在的话。 如果确实存在,那就更新它。 我在date字段中的主键。

这是我的声明,因为它出现在PHP中,也是我得到的错误:

INSERT INTO ExtraStats (date, supportStaff, startEmails, endEmails, emailsAnswered) VALUES ('$startDate', '$supportStaff', '$startEmail', '$endEmail', '$emailAnswered') ON DUPLICATE KEY UPDATE (supportStaff, startEmails, endEmails, emailsAnswered) VALUES ('$supportStaff', '$startEmail', '$endEmail', '$emailAnswered') 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 '(supportStaff, startEmails, endEmails, emailsAnswered) VALUES ('2', '3', '1', '3' at line 1 

 INSERT INTO ExtraStats ( DATE , supportStaff , startEmails , endEmails , emailsAnswered ) VALUES ( '$startDate' , '$supportStaff' , '$startEmail' , '$endEmail' , '$emailAnswered' ) ON DUPLICATE KEY UPDATE supportStaff = '$supportStaff' , startEmails = '$startEmail' , endEmails = '$endEmail' , emailsAnswered = '$emailAnswered' 

你也可以使用VALUES()函数,所以你不要两次传递值:

  ... ON DUPLICATE KEY UPDATE supportStaff = VALUES(supportStaff) , startEmails = VALUES(startEmails) , endEmails = VALUES(endEmails) , emailsAnswered = VALUES(emailsAnswered) 

date是一个保留字,用反引号括起来:

 INSERT INTO ExtraStats (`date`, supportStaff, startEmails, etc... 

另外, ON DUPLICATE KEY UPDATE应该是这样的:

 ON DUPLICATE KEY UPDATE supportStaff = '$supportStaff', startEmails = '$startEmail', etc.. 

您可以查看其他示例的手册 。 你还应该考虑对变量进行saniziting,如果你还没有这样做,以避免SQL注入。