本文深入探讨了在使用 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 {
以上就是MySQL JSON 类型字段在 PDO 中使用时的语法错误与解决方案的详细内容，更多请关注php中文网其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1338959.html
微信扫一扫 
支付宝扫一扫 
