MySQL 仅在两个表中不存在时才插入 - MySQL insert only if not exist in two tables

MySQL insert only if not exist in two tables

本文关键字：不存在插入两个 MySQL | 更新日期: 2023-09-27

我对MySQL查询有一点问题。我正在使用PDO查询，现在只需将值插入表中（见下文）：

$stmt = $dbh -> prepare("INSERT INTO tmp (user_id, test_id, question_id, answer_id) VALUES (?, ?, ?, ?)");
$stmt -> bindParam(1, $_SESSION['UserID']); // binds parameter for user id
$stmt -> bindParam(2, $_GET['start_test']); // binds parameter for test id
$stmt -> bindParam(3, $_POST['question']); // binds parameter for selected answer
$stmt -> bindParam(4, $_POST['select_answer']); // binds parameter for selected answer
$stmt -> execute();

tmp表结构如下：

CREATE TABLE IF NOT EXISTS `tmp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `answer_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

如果不存在，我想插入answer_id值并同时检查是否user_id，否则只需更新，例如：

我有两个用户user_id=1和user_id=2，他们分别回答每个学生商店的问题和每个答案。User_id=1 选取answer_id = 3，user_id=2 选取answer_id = 3。但是，后来一个用户意识到这是一个错误的答案，并希望将其更改为另一个答案（假设 answer_id=2），我希望查询更新当前 answer_id=3 其中 user_id=1 到 answer_id=2

您可以

为相同的内容创建一个INSERT IGNORE，它看起来更简单，并且会有一些问题。

步骤就像

在（user_id、question_id、answer_id）上创建唯一索引
更改表 tmp 添加索引user_qn_ans_idx （user_id，question_id， answer_id）;
现在只需进行插入，并且可能还会进行重复键更新
插入到 TMP（user_id、question_id、answer_id）值中(, , )在重复键更新时answer_id = 值（answer_id）

添加选择计数并在下面执行条件：

$stmt = $dbh->prepare("SELECT count( 1 ) FROM tmp WHERE user_id = :user_id AND question_id = :question_id and answer_id = :answer_id");
$stmt->bindValue( 'user_id', 1 );
$stmt->bindValue( 'question_id', 1 );
$stmt->bindValue( 'answer', 1 );
$stmt->execute();
$oExists = $stmt ->fetch();
if( !empty( $oExists ) )
{
// Run update sql...
}
else
{
// Run insert sql...

$stmt = $dbh->prepare("SELECT count( 1 ) FROM tmp WHERE user_id = :user_id AND question_id = :question_id AND answer_id = :answer_id");
$stmt->bindValue( 'user_id', 1 );
$stmt->bindValue( 'question_id', 1 );
$stmt->bindValue( 'answer_id', 1 );
$stmt->execute();
$oExists = $stmt ->fetch();
	if( !empty( $oExists ) ) {
	 echo "update";
	$stmt = $dbh->prepare("UPDATE tmp SET answer_id = :answer_id WHERE user_id = {$_SESSION['UserID']} AND question_id = {$_POST['question']}"); //updates the counter each time test created                                 
	$stmt -> bindParam(':answer_id', $_POST['select_answer'], PDO::PARAM_INT);
	$stmt -> execute();
	} else {
	echo "insert";	
	$stmt = $dbh -> prepare("INSERT INTO tmp (user_id, test_id, question_id, answer_id) VALUES (?, ?, ?, ?)");
	$stmt -> bindParam(1, $_SESSION['UserID']); // binds parameter for user id
	$stmt -> bindParam(2, $_GET['start_test']); // binds parameter for test id
	$stmt -> bindParam(3, $_POST['question']); // binds parameter for selected answer
	$stmt -> bindParam(4, $_POST['select_answer']); // binds parameter for selected answer
	$stmt -> execute();
	}

这里总是更新，但不是插入。.