MySQL SQL - IF EXISTS UPDATE ELSE INSERT INTO
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15383852/
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
SQL - IF EXISTS UPDATE ELSE INSERT INTO
提问by Laurence Cooper
What I'm trying to do is INSERTsubscribers in my database, but IF EXISTSit should UPDATEthe row, ELSE INSERT INTOa new row.
我想要做的是INSERT我的数据库中的订阅者，但IF EXISTS它应该UPDATE是一行，ELSE INSERT INTO一个新行。
Ofcourse I connect to the database first and GETthe $name, $emailand $birthdayfrom the url string.
Ofcourse我连接到数据库第一和GET的$name，$email并$birthday从URL字符串。
$con=mysqli_connect("localhost","---","---","---");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$name=$_GET['name'];
$email=$_GET['email'];
$birthday=$_GET['birthday'];
This works, but just adds the new row;
这有效，但只是添加了新行；
mysqli_query($con,"INSERT INTO subs (subs_name, subs_email, subs_birthday)
VALUES ('$name', '$email', '$birthday')");
mysqli_close($con);
Here's what I tried;
这是我尝试过的；
mysqli_query($con,"INSERT INTO subs (subs_name, subs_email, subs_birthday)
VALUES '$name', '$email', '$birthday'
ON DUPLICATE KEY UPDATE subs_name = VALUES($name), subs_birthday = VALUES($birthday)");
mysqli_close($con);
and
和
mysqli_query($con,"IF EXISTS (SELECT * FROM subs WHERE subs_email='$email')
UPDATE subs SET subs_name='$name', subs_birthday='$birthday' WHERE subs_email='$email'
ELSE
INSERT INTO subs (subs_name, subs_email, subs_birthday) VALUES ('$name', '$email', '$birthday')");
mysqli_close($con);
and
和
mysqli_query($con,"IF NOT EXISTS(SELECT * FROM subs WHERE subs_email='$email')
Begin
INSERT INTO subs (subs_name, subs_email, subs_birthday)
VALUES ('$name', '$email', '$birthday')
End");
mysqli_close($con);
But none of them work, what am I doing wrong?
但它们都不起作用，我做错了什么？
Any help is greatly appreciated!
任何帮助是极大的赞赏！
回答by eggyal
Create a
UNIQUEconstrainton yoursubs_emailcolumn, if one does not already exist:ALTER TABLE subs ADD UNIQUE (subs_email)Use
INSERT ... ON DUPLICATE KEY UPDATE:INSERT INTO subs (subs_name, subs_email, subs_birthday) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE subs_name = VALUES(subs_name), subs_birthday = VALUES(subs_birthday)
在您的列上创建一个
UNIQUE约束subs_email（如果尚不存在）：ALTER TABLE subs ADD UNIQUE (subs_email)使用
INSERT ... ON DUPLICATE KEY UPDATE：INSERT INTO subs (subs_name, subs_email, subs_birthday) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE subs_name = VALUES(subs_name), subs_birthday = VALUES(subs_birthday)
You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE - dev.mysql.com
您可以在 UPDATE 子句中使用 VALUES(col_name) 函数来引用来自 INSERT ... ON DUPLICATE KEY UPDATE 的 INSERT 部分的列值 - dev.mysql.com
- Note that I have used parameter placeholders in the place of string literals, as one reallyshould be using parameterised statements to defend against SQL injection attacks.
- 请注意，我使用参数占位符代替字符串文字，因为确实应该使用参数化语句来防御 SQL 注入攻击。
