MySQL JSON 类型字段在 PDO 中使用时的语法错误与解决方案

本文深入探讨了在使用 PHP PDO 向 MySQL JSON 类型字段执行 `INSERT` 和 `UPDATE` 操作时常见的语法错误。核心问题在于 `VALUES` 子句中占位符的错误使用以及 `JSON_ARRAY_INSERT` 函数对插入值格式的特殊要求。教程将提供详细的错误分析、修正后的 SQL 语句和 PDO 参数绑定策略，确保 JSON 数组数据的正确插入与更新。

背景与问题描述

在现代 Web 应用中，将 JSON 数据存储在 MySQL 数据库的 JSON 类型字段中已成为常见实践。然而，当结合 PHP PDO 预处理语句进行数据操作时，尤其是涉及到 JSON 数组的插入和更新（例如使用 JSON_ARRAY_INSERT 函数），开发者可能会遇到 SQLSTATE[42000]: Syntax error or access violation: 1064 这样的语法错误。这通常是由于对 SQL 语法和 MySQL JSON 函数参数格式的误解造成的。

考虑一个场景，我们有一个 purchased_products 表，其中包含 customer_id (INT) 和 purchased_products (JSON) 字段，期望 purchased_products 存储一个 JSON 数组，例如 ["32", "33", "34"]。

当尝试执行以下包含 INSERT ... ON DUPLICATE KEY UPDATE 逻辑的 PDO 语句时，可能会触发语法错误：

$item = [
  'statement' => "INSERT INTO purchased_products 
                        (customer_id, purchased_products) 
                  VALUES(:customer_id, [:purchased_products]) 
                    ON DUPLICATE KEY 
                    UPDATE purchased_products = JSON_ARRAY_INSERT(purchased_products, '$[0]',:purchased_products)",
  'data' => [
    ['customer_id' => 12345, 'purchased_products' => '"36"'], // 原始错误数据
  ]
];

执行上述代码后，会得到类似以下错误信息： SQLSTATE[42000]: Syntax error or access violation: 1064 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 '["3784835"]) ON DUPLICATE KEY UPDATE purchased_products = JSON_ARRAY_INSERT(purc' at line 1

错误分析

导致上述语法错误主要有两个原因：

1. VALUES 子句中占位符的错误使用： 在 SQL 的 VALUES 子句中，占位符 :purchased_products 周围不应包含方括号 [ 和 ]。例如，VALUES(:customer_id, [:purchased_products]) 是错误的语法。正确的写法是直接使用占位符：VALUES(:customer_id, :purchased_products)。PDO 会负责将绑定的值插入到正确的位置。

2. JSON_ARRAY_INSERT 函数对插入值的格式要求： JSON_ARRAY_INSERT(json_doc, path, val) 函数用于向 JSON 数组中插入一个值。这里的 val 参数期望是一个 JSON 标量值（例如一个字符串、数字或布尔值），而不是一个包含方括号的 JSON 数组字符串。如果 val 是一个字符串，它应该表示为 '"some_value"'（带双引号的 JSON 字符串），而不是 '"[some_value]"'。在我们的场景中，如果 purchased_products 字段存储的是 JSON 数组，并且我们想插入一个新的产品ID "36"，那么 JSON_ARRAY_INSERT 的第三个参数应该绑定为 '36' (一个不带外部方括号的字符串，代表一个 JSON 字符串字面量)，而不是 '["36"]'。原始代码中，purchased_products 参数同时用于 INSERT 和 UPDATE 部分，且绑定了一个不符合 JSON_ARRAY_INSERT 期望格式的值。

解决方案

为了解决上述问题，我们需要对 SQL 语句和 PDO 参数绑定策略进行调整。

BRANDMARK

AI帮你设计Logo、图标、名片、模板……等

180

查看详情

1. 修正 SQL 语句

首先，修正 INSERT 语句中 VALUES 子句的语法，并为 JSON_ARRAY_INSERT 函数引入一个独立的参数，以处理其特定的格式要求。

INSERT INTO purchased_products 
    (customer_id, purchased_products) 
VALUES
    (:customer_id, :purchased_products_insert) -- 移除方括号，并为INSERT部分使用独立参数
ON DUPLICATE KEY 
UPDATE 
    purchased_products = JSON_ARRAY_INSERT(purchased_products, '$[0]', :purchased_products_update_val) -- 为JSON_ARRAY_INSERT使用独立参数

说明：

• VALUES(:customer_id, :purchased_products_insert)：移除了 [:purchased_products] 周围的方括号。
• purchased_products = JSON_ARRAY_INSERT(purchased_products, '$[0]', :purchased_products_update_val)：为 JSON_ARRAY_INSERT 的第三个参数引入了一个新的占位符 :purchased_products_update_val。

2. 调整 PDO 参数绑定

接下来，根据修正后的 SQL 语句，调整 PDO 的参数绑定数据。我们需要为 INSERT 和 UPDATE 部分分别准备格式正确的参数。

• 对于 INSERT 部分的 :purchased_products_insert，我们需要绑定一个完整的 JSON 数组字符串，例如 '["36"]'。
• 对于 UPDATE 部分的 :purchased_products_update_val (用于 JSON_ARRAY_INSERT)，我们需要绑定一个表示要插入的单个 JSON 标量值的字符串，例如 '36'。

以下是修正后的 $item 数组示例：

$item = [
  'statement' => "INSERT INTO purchased_products 
                        (customer_id, purchased_products) 
                  VALUES(:customer_id, :purchased_products_insert) 
                    ON DUPLICATE KEY 
                    UPDATE purchased_products = JSON_ARRAY_INSERT(purchased_products, '$[0]', :purchased_products_update_val)",
  'data' => [
    // 第一次操作：插入或更新 '36'
    [
      'customer_id' => 12345, 
      'purchased_products_insert' => '["36"]', // 供 INSERT 使用，完整的 JSON 数组字符串
      'purchased_products_update_val' => '36'  // 供 JSON_ARRAY_INSERT 使用，单个 JSON 标量值字符串
    ],
    // 第二次操作：插入或更新 '37'
    [
      'customer_id' => 12345, 
      'purchased_products_insert' => '["37"]', 
      'purchased_products_update_val' => '37'
    ],
  ]
];

3. 完整的 PDO 执行代码

结合修正后的 SQL 语句和参数绑定，完整的 PDO 执行代码如下：

<?php

// 假设 PDO 连接已建立
// $this->connection = new PDO("mysql:host=$servername;dbname=$database", $u, $p, [...]);
// $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$item = [
  'statement' => "INSERT INTO purchased_products 
                        (customer_id, purchased_products) 
                  VALUES(:customer_id, :purchased_products_insert) 
                    ON DUPLICATE KEY 
                    UPDATE purchased_products = JSON_ARRAY_INSERT(purchased_products, '$[0]', :purchased_products_update_val)",
  'data' => [
    ['customer_id' => 12345, 'purchased_products_insert' => '["36"]', 'purchased_products_update_val' => '36'],
    ['customer_id' => 12345, 'purchased_products_insert' => '["37"]', 'purchased_products_update_val' => '37'],
    ['customer_id' => 12346, 'purchased_products_insert' => '["40"]', 'purchased_products_update_val' => '40'], // 示例：新用户
  ]
];

try {

以上就是MySQL JSON 类型字段在 PDO 中使用时的语法错误与解决方案的详细内容，更多请关注php中文网其它相关文章！