MySQL PDO操作JSON类型字段：解决语法错误与数据格式化指南

$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"'],
    ['customer_id' => 12345, 'purchased_products' => '"37"']
  ]
];

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

$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)",
  'data' => [
    // 第一次操作
    [
      'customer_id'             => 12345, 
      'purchased_products_insert' => '["36"]', // 用于INSERT，需要完整的JSON数组字符串
      'purchased_products_update' => '36'      // 用于UPDATE (JSON_ARRAY_INSERT)，只需要值本身
    ],
    // 第二次操作 (假设是追加商品)
    [
      'customer_id'             => 12345, 
      'purchased_products_insert' => '["37"]', 
      'purchased_products_update' => '37'
    ],
  ]
];

<?php

// 数据库配置
$servername = "localhost";
$database   = "your_database_name";
$u          = "your_username";
$p          = "your_password";

// 假设SSL证书路径 (如果不需要SSL，可以移除相关配置)
$ck = '/path/to/client-key.pem';
$cc = '/path/to/client-cert.pem';
$sc = '/path/to/server-ca.pem';

try {
    // 建立PDO连接
    $connection = new PDO("mysql:host=$servername;dbname=$database", $u, $p, [
      PDO::MYSQL_ATTR_SSL_KEY                => $ck, // 客户端私钥
      PDO::MYSQL_ATTR_SSL_CERT               => $cc, // 客户端证书
      PDO::MYSQL_ATTR_SSL_CA                 => $sc, // 服务器CA证书
      PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false, // 是否验证服务器证书，生产环境建议开启
      PDO::ATTR_EMULATE_PREPARES             => false, // 禁用模拟预处理，提高安全性
      PDO::MYSQL_ATTR_INIT_COMMAND           => 'SET NAMES utf8mb4' // 设置字符集
    ]);

    // 设置错误模式为异常，便于捕获错误
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // 设置默认的fetch模式
    $connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    echo "数据库连接成功！<br>";

    // 假设的插入/更新数据
    $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)",
      'data' => [
        ['customer_id' => 12345, 'purchased_products_insert' => '["36"]', 'purchased_products_update' => '36'],
        ['customer_id' => 12345, 'purchased_products_insert' => '["37"]', 'purchased_products_update' => '37'],
        ['customer_id' => 67890, 'purchased_products_insert' => '["42"]', 'purchased_products_update' => '42'] // 新客户
      ]
    ];

    $statement = $connection->prepare($item['statement']);

    foreach ($item['data'] as $rowData) {
        // 绑定参数
        foreach ($rowData as $key => $param) {
            // 根据参数类型进行绑定，这里假设都是字符串
            $statement->bindValue(':' . $key, $param, PDO::PARAM_STR);
        }

        try {
            $success = $statement->execute();
            if ($success) {
                echo "客户ID: " . $rowData['customer_id'] . " 数据操作成功。<br>";
            }
        } catch (PDOException $e) {
            echo "客户ID: " . $rowData['customer_id'] . " 数据操作失败: " . $e->getMessage() . "<br>";
        }
    }

} catch (PDOException $e) {
    echo "数据库连接失败: " . $e->getMessage();
}

// 辅助函数，用于更友好的输出 (可选)
function pre($data) {
    echo '<pre>';
    print_r($data);
    echo '</pre>';
}

?>