如何使用MySQL JSON_REPLACE函数设置JSON值？解决语法错误

错误原因 #

1. CAST语法不兼容：MariaDB不支持CAST('xxx' AS JSON)这种写法，json_encode已经生成了标准JSON字符串，无需额外转换，JSON_REPLACE可直接识别。
2. SQL注入风险：直接将变量拼接进SQL语句，不仅会因JSON字符串中的引号未转义导致语法错误，还存在严重的安全隐患。

修复方案（推荐：使用预处理语句） #

预处理语句能自动处理字符串转义，彻底避免SQL注入，同时解决语法问题：

// 构造目标JSON数据
$newElements = json_encode(['name' => 'jorj', 'age' => 21]);
$targetID = 8451; // 替换为实际业务中的ID值

// 初始化预处理语句
$stmt = $db->prepare("UPDATE dl SET data = JSON_REPLACE(data, '$.list', ?) WHERE id = ?");
// 绑定参数：s=字符串类型（对应JSON），i=整数类型（对应ID，若ID是字符串则改为s）
$stmt->bind_param("si", $newElements, $targetID);
// 执行更新操作
$stmt->execute();

// 验证更新结果
if ($stmt->affected_rows > 0) {
    echo "JSON字段更新成功";
} else {
    echo "无数据需要更新或更新失败";
}

// 关闭预处理语句
$stmt->close();

备选方案（不推荐：字符串拼接+手动转义） #

如果因环境限制无法使用预处理语句，需手动转义变量中的特殊字符：

$newElements = json_encode(['name' => 'jorj', 'age' => 21]);
// 对JSON字符串和ID进行转义处理
$escapedJSON = mysqli_real_escape_string($db, $newElements);
$escapedID = mysqli_real_escape_string($db, $ID);

// 构造SQL语句（移除无效的CAST语法）
$query = "UPDATE dl SET data = JSON_REPLACE(data, '$.list', '$escapedJSON') WHERE id = '$escapedID'";
$result = mysqli_query($db, $query);

额外注意事项 #

• 确认你的MariaDB版本≥10.2.3，该版本才正式支持JSON_REPLACE等JSON操作函数。
• 确保dl表的data字段类型为JSON，或TEXT/LONGTEXT（需保证存储的是合法JSON字符串）。

内容的提问来源于stack exchange，提问作者mezerji مزرجی