php IF EXISTS UPDATE ELSE INSERT 的语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12436602/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Syntax error with IF EXISTS UPDATE ELSE INSERT
提问by Openstar63
I'm using MySQL 5.1 hosted at my ISP. This is my query
我正在使用托管在我的 ISP 上的 MySQL 5.1。这是我的查询
mysql_query("
IF EXISTS(SELECT * FROM licensing_active WHERE title_1='$title_1') THEN
BEGIN
UPDATE licensing_active SET time='$time' WHERE title_1='$title_1')
END ELSE BEGIN
INSERT INTO licensing_active(title_1) VALUES('$title_1')
END
") or die(mysql_error());
The error is
错误是
... check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF EXISTS(SELECT * FROM licensing_active WHERE title_1='Title1') THEN ' at line 1
My actual task involves
我的实际任务涉及
WHERE title_1='$title_1' AND title_2='$title_2' AND version='$version' ...ETC...
but I have reduced it down to make things simpler for my problem solving
但我已经减少了它以使我的问题解决变得更简单
In my searches on this, I keep seeing references to 'ON DUPLICATE KEY UPDATE', but don't know what to do with that.
在我对此的搜索中，我一直看到对“ON DUPLICATE KEY UPDATE”的引用，但不知道该怎么做。
回答by Sohail Ahmed
Here is a simple and easy solution, try it.
这是一个简单易行的解决方案，试试吧。
$result = mysql_query("SELECT * FROM licensing_active WHERE title_1 ='$title_1' ");
if( mysql_num_rows($result) > 0) {
mysql_query("UPDATE licensing_active SET time = '$time' WHERE title_1 = '$title_1' ");
}
else
{
mysql_query("INSERT INTO licensing_active (title_1) VALUES ('$title_1') ");
}
Note: Though this question is from 2012, keep in mind that mysql_*functions are no longer available since PHP 7.
注意：虽然这个问题是从 2012 年开始的，但请记住，自 PHP 7 起mysql_*函数不再可用。
回答by Fluffeh
This should do the trick for you:
这应该对你有用：
insert into
licensing_active (title_1, time)
VALUES('$title_1', '$time')
on duplicate key
update set time='$time'
This is assuming that title_1is a unique column (enforced by the database) in your table.
这是假设这title_1是表中的唯一列（由数据库强制执行）。
The way that insert... on duplicateworks is it tries to insert a new row first, but if the insert is rejected because a key stops it, it will allow you to update certain fields instead.
有效的方法insert... on duplicate是先尝试插入新行，但如果插入被拒绝，因为某个键阻止了它，它将允许您改为更新某些字段。
回答by JvdBerg
The syntax of your query is wrong. Checkout http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
您的查询语法错误。结帐http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
Use the on duplicate key syntaxto achieve the result you want. See http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
使用 onduplicate key syntax来实现您想要的结果。见http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
