mysql - SQL-如果存在，则将更新插入其他

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

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

$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'];

可以，但是只添加新行；

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

但是它们都不起作用，我在做什么错呢？

任何帮助是极大的赞赏!

最佳答案

(如果尚不存在)在UNIQUE列上创建一个 subs_email constraint:

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)

注意，我已经使用参数占位符代替字符串文字，因为实际上应该对defend against SQL injection attacks使用参数化语句。

关于mysql - SQL-如果存在，则将更新插入其他，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15383852/