PHP MySQL JSON 字段操作与同步逻辑优化

摘要：本文总结了 PHP 脚本中使用 JSON 字段进行数据同步的逻辑，重点在于正确处理 JSON_CONTAINS 和 JSON_QUOTE 的使用，以及优化数据库更新性能。

1. 问题背景

      在处理 MySQL 的 JSON 字段时，需要确保传入的字符串被正确封装为 JSON 格式，以便与 JSON 数组进行比较。例如，在 `JSON_CONTAINS` 函数中，普通字符串不能直接用于匹配 JSON 数组中的元素。

2. 正确使用 JSON_QUOTE

      为了将 SQL 字符串转换为 JSON 字符串，应使用 `JSON_QUOTE()` 函数。例如：

JSON_CONTAINS(clients, JSON_QUOTE('LAPTOP-ATPMBNE8'), '$')

      这样可以确保字符串被正确识别为 JSON 字符串。

3. 更新同步状态的优化

      原始代码中使用 `foreach` 循环执行多次 `UPDATE` 操作，效率较低。建议改为批量更新，减少数据库交互次数。

$sql = "
    UPDATE datas 
    SET clients = CASE 
        WHEN clients IS NULL THEN JSON_ARRAY(?)
        WHEN NOT JSON_CONTAINS(clients, JSON_QUOTE(?), '$') THEN JSON_ARRAY_APPEND(clients, '$', ?)
        ELSE clients
    END
    WHERE md5 IN ($placeholders)
      AND groupID = ?
";

      通过一次性更新多条记录，提高性能。

4. 安全性与健壮性增强

• 限制请求体大小，防止 DOS 攻击。
• 对输入参数进行 trim 和空值检查。
• 捕获 PDOException，避免宽泛的 Exception 捕获。
• 添加日志记录，便于调试和监控。

5. 数据库索引建议

      为提高查询效率，建议在以下字段上创建索引：

• `datas(groupID, lastUse)`：加速排序查询。
• `datas(md5)`：加速 UPDATE 操作。
• `clients(groupID, clientID)`：加速客户端授权验证。

6. 其他注意事项

• 确保 `clients` 字段是 JSON 类型，并且存储的是合法的 JSON 数组。
• 在高并发场景下，考虑使用 `JSON_SEARCH` 判断是否已存在目标值，避免重复添加。