php - 插入...重复键更新不适用于 php，但可直接在 MySQL 上使用

Question

$sql = "SET @tag_name = '$tag_value',
    @tag_link = '$tag_link',
    @user_value = '$user_value';
INSERT INTO urls_unis
    (tag_name, tag_link, user_data)
VALUES
    (@tag_name, @tag_link, @user_value)
ON DUPLICATE KEY UPDATE
    tag_name = @tag_name,
    tag_link = @tag_link,
    user_data = @user_value;
    ";

    if(mysqli_query($link, $sql)){
        echo "Records inserted successfully.";
    } else{
        echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
    }
    }

上面的代码返回的是:

ERROR: Could not able to execute
SET @tag_name = 'View history', @tag_link = 'zNIL', @user_value = '/w/index.php?title=Non-volatile_random-access_memory&action=history'; INSERT INTO urls_unis (tag_name, tag_link, user_data) VALUES (@tag_name, @tag_link, @user_value) ON DUPLICATE KEY UPDATE tag_name = @tag_name, tag_link = @tag_link, user_data = @user_value; .

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO urls_unis (tag_name, tag_link, user_data) VALUES (@tag_name,' at line 4

当我将 mysql 查询复制并粘贴到 phpmyadmin 来执行时，它工作正常。没有返回任何错误。

这是怎么回事，我该如何解决这个问题？

Answer 1

您无法在单个查询中运行多个查询行。您需要单独运行它们或使用mysqli_multi_query()。您可以通过仅使用变量来避免所有这些问题，而无需首先设置 MySQL 变量。

更好的是，您可以使用准备好的语句，而不是将变量直接注入(inject)查询中。因为您使用了两次值，并且 MySQLi 不支持命名占位符，所以我们必须将每个变量绑定(bind)两次。

$sql = "INSERT INTO urls_unis (tag_name, tag_link, user_data)
    VALUES
        (?, ?, ?)
    ON DUPLICATE KEY UPDATE
        tag_name = ?,
        tag_link = ?,
        user_data = ?;";

$stmt = $link->prepare($sql);
$stmt->bind_param("ssssss", $tag_value, $tag_link, $user_value, 
                            $tag_value, $tag_link, $user_value);
if ($stmt->execute()){
    echo "Records inserted successfully.";
} else{
    echo "ERROR: Could not able to execute $sql<br />";
    echo $stmt->error;
}
$stmt->close();

• When should I use prepared statements?