本文深入探讨了在使用 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
错误分析
导致上述语法错误主要有两个原因：
VALUES 子句中占位符的错误使用： 在 SQL 的 VALUES 子句中，占位符 :purchased_products 周围不应包含方括号 [ 和 ]。例如，VALUES(:customer_id, [:purchased_products]) 是错误的语法。正确的写法是直接使用占位符：VALUES(:customer_id, :purchased_products)。PDO 会负责将绑定的值插入到正确的位置。
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 参数绑定策略进行调整。
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 执行代码如下：
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 { 
