如何使用PHP从MySQL giftcards表的JSON列查询指定客户的单条记录

前置说明 #

MySQL 5.7及以上版本提供了原生JSON操作函数，是处理该场景的最优方案，以下按customerid列常见的三种JSON存储结构分别给出实现方法。

1. 不同存储场景的SQL查询语句 #

• 场景1：customerid列直接存储JSON格式的客户ID数值（例如内容为2）

  -- 通用写法
  SELECT * FROM giftcards WHERE JSON_UNQUOTE(JSON_EXTRACT(customerid, '$')) = 2;
  -- MySQL 5.7+简化写法，->>等价于JSON_UNQUOTE+JSON_EXTRACT
  SELECT * FROM giftcards WHERE customerid->>'$' = 2;
  

• 场景2：customerid列存储的是包含客户ID的JSON对象（例如内容为{"customer_id":2,"name":"张三"}）

  SELECT * FROM giftcards WHERE customerid->>'$.customer_id' = 2;
  

• 场景3：customerid列存储的是包含多个客户ID的JSON数组（例如内容为[1,2,3]）

  SELECT * FROM giftcards WHERE JSON_CONTAINS(customerid, '2', '$');
  

2. PHP代码实现（PDO预处理，防SQL注入） #

以下以场景1的查询需求为例，替换对应SQL即可适配其他场景：

<?php
// 数据库连接参数替换为自己的配置
$dbHost = '127.0.0.1';
$dbName = '你的数据库名';
$dbUser = '你的数据库用户名';
$dbPass = '你的数据库密码';
$targetCid = 2; // 要查询的客户ID

try {
    // 初始化PDO连接
    $pdo = new PDO(
        "mysql:host={$dbHost};dbname={$dbName};charset=utf8mb4",
        $dbUser,
        $dbPass,
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );

    // 预处理并执行查询
    $stmt = $pdo->prepare("SELECT * FROM giftcards WHERE customerid->>'$' = ?");
    $stmt->execute([$targetCid]);
    
    // 获取单条结果
    $record = $stmt->fetch(PDO::FETCH_ASSOC);

    if ($record) {
        // 此处写业务处理逻辑，示例为打印结果
        var_dump($record);
    } else {
        echo "未找到对应客户的礼品卡记录";
    }
} catch (PDOException $e) {
    echo "查询失败：" . $e->getMessage();
}
?>

3. MySQL 5.7以下版本兼容方案 #

如果MySQL版本不支持JSON函数，可临时使用字符串模糊匹配，该方案准确率较低，优先建议升级MySQL版本：

SELECT * FROM giftcards WHERE customerid LIKE '%2%';

内容的提问来源于stack exchange，提问作者Qadeer Abbas