在准备好的语句中使用问号占位符时出现 MySQL 语法错误

尝试了我能想到的一切，我已经将范围缩小到“？”占位符。

我尝试过替换“？”带有随机文本的占位符，一切都很好（当然除了它不断覆盖同一行）。

我得到的错误：

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 '?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? 附近使用的正确语法 关于重复密钥更新 第 2 行的产品

这是我的代码（我会提供更多，但除了这个错误之外，一切都运行良好，如果我删除“？”占位符，那么除了值不是动态的之外，所有代码都运行良好，但如果您怀疑问题出在其他地方，请询问）：

    // Create MySQL connection to ds_signifyd_api
    $mysqli = mysqli_connect( $db_server_name, $db_username, $db_password, $db_name );
    // Check connection
    if ($mysqli->connect_error) {
        exit( $mysqliFailedBody );
    }

    $mainProdQueryStmt = "INSERT INTO products (`product_id`, `title`, `body_html`, `vendor`, `product_type`, `created_at`, `handle`, `updated_at`, `published_at`, `template_suffix`, `published_scope`, `tags`) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
        ON DUPLICATE KEY UPDATE 
            product_id = VALUES(product_id), 
            title = VALUES(title), 
            body_html = VALUES(body_html),
            vendor = VALUES(vendor), 
            product_type = VALUES(product_type), 
            created_at = VALUES(created_at), 
            handle = VALUES(handle), 
            updated_at = VALUES(updated_at), 
            published_at = VALUES(published_at), 
            template_suffix = VALUES(template_suffix), 
            published_scope = VALUES(published_scope), 
            tags = VALUES(tags)";

        $product_id = $product_title = $body_html = $vendor = $product_type = $created_at = $handle = $updated_at = $published_at = $template_suffix = $published_scope = $tags = "";

        foreach ($dss_product_db_array as $product) {

            $product_id = $product['id'];
            //... more variables here...
            $tags = mysqli_real_escape_string($mysqli, $tags);              

            if (!mysqli_query($mysqli, $mainProdQueryStmt)) {
                printf("Errormessage: %s\n", mysqli_error($mysqli));
            }

            $mainProdQuery->bind_param("isssssssssss", $product_id, $product_title, $body_html, $vendor, $product_type, $created_at, 
                $handle, $updated_at, $published_at, $template_suffix, $published_scope, $tags);
            $mainProdQuery->execute();
    //      $mainProdQuery->close();
       }

UPDATE

实施了此处提到的修复： 1.停止使用mysqli_real_escape_string2. 循环外绑定变量 3. 只使用面向对象的方法，而不是像下面那样混合使用它们mysqli_query($mysqli, $mainProdQueryStmt) VS $mysqli->prepare($mainProdQueryStmt)正如它应该的那样——这解决了“？”错误报告占位符语法错误

现在一切正常，没有错误。

更新的代码：

    $mainProdQueryStmt = "INSERT INTO dss_products (`product_id`, `title`, `body_html`, `vendor`, `product_type`, `created_at`, `handle`, `updated_at`, `published_at`, `template_suffix`, `published_scope`, `tags`) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
        ON DUPLICATE KEY UPDATE 
            product_id = VALUES(product_id), 
            title = VALUES(title), 
            body_html = VALUES(body_html),
            vendor = VALUES(vendor), 
            product_type = VALUES(product_type), 
            created_at = VALUES(created_at), 
            handle = VALUES(handle), 
            updated_at = VALUES(updated_at), 
            published_at = VALUES(published_at), 
            template_suffix = VALUES(template_suffix), 
            published_scope = VALUES(published_scope), 
            tags = VALUES(tags)";

    $mainProdQuery = $mysqli->prepare($mainProdQueryStmt);
    if ($mainProdQuery === FALSE) {
        die($mysqli->error);
    }

    $product_id = $product_title = $body_html = $vendor = $product_type = $created_at = $handle = $updated_at = $published_at = $template_suffix = $published_scope = $tags = "";
    $mainProdQuery->bind_param("isssssssssss", $product_id, $product_title, $body_html, $vendor, $product_type, $created_at, 
        $handle, $updated_at, $published_at, $template_suffix, $published_scope, $tags);

    if ($mainProdQuery) {

        foreach ($dss_product_db_array as $product) {

            $product_id = $product['id'];
            $product_title = $product['title'];
            $body_html = $product['body_html'];
            $vendor = $product['vendor'];
            $product_type = $product['product_type'];
            $created_at = $product['created_at'];
            $handle = $product['handle'];
            $updated_at = $product['updated_at'];   
            $published_at = $product['published_at'];
            $template_suffix = $product['template_suffix'];
            $published_scope = $product['published_scope'];
            $tags = $product['tags'];

            if (!$mysqli->prepare($mainProdQueryStmt)) {
                printf("Errormessage: %s\n", $mysqli->error);
            }
            $mainProdQuery->execute();
        }
    }

当您使用占位符时，您必须使用mysqli_prepare()，你不能使用mysqli_query()。看起来您打算这样做，但不知何故代码丢失了，因为您使用了变量$mainProdQuery你从未分配过的。

您应该在循环外部准备查询并绑定参数一次。然后打电话execute()循环内。

$mainProdQueryStmt = "INSERT INTO products (`product_id`, `title`, `body_html`, `vendor`, `product_type`, `created_at`, `handle`, `updated_at`, `published_at`, `template_suffix`, `published_scope`, `tags`) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 
    ON DUPLICATE KEY UPDATE 
        product_id = VALUES(product_id), 
        title = VALUES(title), 
        body_html = VALUES(body_html),
        vendor = VALUES(vendor), 
        product_type = VALUES(product_type), 
        created_at = VALUES(created_at), 
        handle = VALUES(handle), 
        updated_at = VALUES(updated_at), 
        published_at = VALUES(published_at), 
        template_suffix = VALUES(template_suffix), 
        published_scope = VALUES(published_scope), 
        tags = VALUES(tags)";
$mainProdQuery = $mysqli->prepare($mainProdQueryStmt);
$mainProdQuery->bind_param("isssssssssss", $product_id, $product_title, $body_html, $vendor, $product_type, $created_at, 
            $handle, $updated_at, $published_at, $template_suffix, $published_scope, $tags);
foreach ($dss_product_db_array as $product) {

    $product_id = $product['id'];
    //... more variables here...
    $mainProdQuery->execute();
}