SQL-如果存在，则将更新插入其他 [英] SQL - IF EXISTS UPDATE ELSE INSERT INTO

问题描述

我想做的是数据库中的INSERT个订阅者，但是IF EXISTS它应该UPDATE该行，ELSE INSERT INTO一个新行.

What I'm trying to do is INSERT subscribers in my database, but IF EXISTS it should UPDATE the row, ELSE INSERT INTO a new row.

当然，我首先连接到数据库，然后从URL字符串连接到GET，$name，$email和$birthday.

Ofcourse I connect to the database first and GET the $name, $email and $birthday from the url string.

$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);

这就是我尝试过的；

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

和

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

和

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?

任何帮助将不胜感激！

推荐答案

1. 在上创建 UNIQUE约束您的subs_email列(如果尚不存在):

1. Create a UNIQUE constraint on your subs_email column, if one does not already exist:

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)

您可以在UPDATE子句中使用VALUES(col_name)函数来 从INSERT的INSERT部分引用列值... ON 重复密钥更新- dev.mysql.com

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

1. 请注意，我已经使用参数占位符代替了字符串文字，因为一个确实应该使用参数化的语句来防御SQL注入攻击.

1. Note that I have used parameter placeholders in the place of string literals, as one really should be using parameterised statements to defend against SQL injection attacks.

这篇关于SQL-如果存在，则将更新插入其他的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！