MySQL插入然后更新表数据 - MySQL insert then update table data

MySQL insert then update table data

本文关键字：数据更新然后插入 MySQL | 更新日期: 2023-09-27

嘿，我在我的SQL/PHP/JAVA应用程序中遇到了一个小问题，我希望你们能帮忙：）我有一个java应用程序，当运行时连接到我的网站，当java应用程序验证它正在运行时，它会与我的网站对话，我的网站会为java应用程序和网站本身分配一个会话Id。

到目前为止我们还不错吗？

好吧，我的java应用程序定期将数据发送到一个名为Dashboard.php的页面。我想做的是将数据保存到我的Mysql表中，然后当Dashboardphp从我的java应用程序接收到新数据时，如果sessionID相同，我希望该表更新为刚刚接收到的新数据

这是我迄今为止拥有的php，尽管它不起作用。

function update($script_name, $version, $runtime, $status, $ranged, $attack, $defense, $strength, $magic, $sessionID, $username)
{
    global $db;
    $sql = "SELECT * FROM Dashboard WHERE session_id = '$sessionID'";
    try {
        $results = $db->query($sql);
        if ($results->rowCount() <= 0) {
            $query = "INSERT INTO Dashboard (script_name, version, runtime, status, ranged, attack, defense, strength, magic, session_id, username) VALUES ('$script_name', '$version', '$runtime', '$status', '$ranged', '$attack', '$defense', '$strength', '$magic', '$sessionID', $username)";
            $db->exec($query);
        } else {
            foreach ($results as $row) {
                $timerunnew = $row['runtime'] + $runtime;
                $v4new = $row['ranged'] + $range;
                $v5new = $row['attack'] + $attack;
                $v6new = $row['defense'] + $defense;
                $v7new = $row['strength'] + $strength;
                $v8new = $row['magic'] + $magic;
            }
            $db->exec("UPDATE Dashboard SET `runtime` = $timerunnew, `ranged` = $v4new, `attack` = $v5new, `defense` = $v6new, `strength` = $v7new, `magic` = $v8new WHERE session_id = '$sessionID'");
        }
    } catch (PDOException $ex) {
        echo "fail";
    }
}

我也尝试过用ON DUPLICATE KEY UPDATE value = VALUES(value)进行实验，但我运气不好，有人有解决方案吗？任何帮助都将不胜感激

如果这是将记录插入Dashboard表的唯一方法，则两个记录不可能共享同一个session_id（除了SELECT和INSERT命令之间发生的竞争危险）。在这种情况下，您应该：

确保在session_id:上定义了UNIQUE密钥

ALTER TABLE Dashboard ADD UNIQUE KEY (session_id);

使用INSERT ... ON DUPLICATE KEY UPDATE，最好与正确参数化的准备语句一起使用：

$qry = $db->prepare('
  INSERT INTO Dashboard (
     script_name,  version,  runtime,  status,  ranged,  attack,
     defense,  strength,  magic,  session_id,  username
  ) VALUES (
    :script_name, :version, :runtime, :status, :ranged, :attack,
    :defense, :strength, :magic, :session_id, :username
  ) ON DUPLICATE KEY UPDATE
    runtime  = runtime  + VALUES(runtime),
    attack   = attack   + VALUES(status),
    defense  = defense  + VALUES(defense),
    strength = strength + VALUES(strength),
    magic    = magic    + VALUES(magic)
');
$qry->execute([
  ':script_name' => $script_name,
  ':version'     => $version,
  ':runtime'     => $runtime,
  ':status'      => $status,
  ':ranged'      => $ranged,
  ':attack'      => $attack,
  ':$defense'    => $defense,
  ':strength'    => $strength,
  ':magic'       => $magic,
  ':session_id'  => $sessionID,
  ':username'    => $username
]);