SQL - 如果存在更新 ELSE INSERT INTO [英] SQL - IF EXISTS UPDATE ELSE INSERT INTO

查看:24
本文介绍了SQL - 如果存在更新 ELSE INSERT INTO的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

当然,我首先连接到数据库,然后 GET $name$email$birthday 来自url 字符串.

$con=mysqli_connect("localhost","---","---","---");//检查连接如果(mysqli_connect_errno()){echo "连接 MySQL 失败:" .mysqli_connect_error();}$name=$_GET['name'];$email=$_GET['email'];$birthday=$_GET['生日'];

这可行,但只是添加新行;

mysqli_query($con,"INSERT INTO subs (subs_name, subs_email, subs_birthday)值 ('$name', '$email', '$birthday')");mysqli_close($con);

这是我尝试过的;

mysqli_query($con,"INSERT INTO subs (subs_name, subs_email, subs_birthday)值 '$name'、'$email'、'$birthday'重复键更新 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'别的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')开始插入到潜艇(subs_name、subs_email、subs_birthday)值('$name'、'$email'、'$birthday')结尾");mysqli_close($con);

但是它们都不起作用,我做错了什么?

非常感谢任何帮助!

解决方案

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

    ALTER TABLE subs ADD UNIQUE (subs_email)

  2. 使用 INSERT ... ON DUPLICATE KEY更新:

    INSERT INTO subs(subs_name, subs_email, subs_birthday)价值观(?,?,?)重复密钥更新subs_name = 值(subs_name),subs_birthday = 值(subs_birthday)

<块引用>

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

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

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.

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

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!

解决方案

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

    ALTER TABLE subs ADD UNIQUE (subs_email)
    

  2. 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)
    

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. 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 - 如果存在更新 ELSE INSERT INTO的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆