插入

我正在尝试做的是 INSERT订户在我的数据库，但 IF EXISTS它应该 UPDATE行，ELSE INSERT INTO一个新的行。

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

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

但都不管用，我哪里做错了？

非常感谢您的帮助！

208355 次浏览
小开
最佳答案

  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.
小开

Try this:

INSERT INTO `center_course_fee` (`fk_course_id`,`fk_center_code`,`course_fee`) VALUES ('69', '4920153', '6000') ON DUPLICATE KEY UPDATE `course_fee` = '6000';
小开 
INSERT ... ON DUPLICATE KEY UPDATE

is a good solution as long as you don't mind AUTO_INCREMENT counters unnecessarily incrementing every time you end up doing an UPDATE. Since it tries to INSERT first, I noticed auto counters do increment. Another solution I like that may be less performant, but easy to maintain is:

IF EXISTS(SELECT 1 FROM table WHERE column = value...) THEN
UPDATE table
SET column = value ...
WHERE other_column = other_value ...;
ELSE
INSERT INTO table
(column1, column2, ...)
VALUES
(value1, value2, ...);
END IF;

提交答案