在mySQL中插入带有特殊字符的JSON编码值失败 [英] Inserting in mySQL a JSON encoded value with special chars fails

问题描述

我正在尝试使用PDO执行此查询:

I am trying to execute this query with PDO:

UPDATE categories
SET keywords = '{"bg":["keyword1","\u0431\u044a\u043b\u0433\u0430\u0440\u0441\u043a\u0438 "]}'
WHERE id = 4;

问题在于，它似乎记录了没有反斜杠("\")的十六进制值.它在我的应用程序中以及PHPMyAdmin中的显示都没有反斜杠.

The problem is that it seems to record the hex value without the backslashes ("\"). It does show up without the backslashes in my app but also in PHPMyAdmin.

我在此查询上使用exec()，并且已在没有任何动态/转换变量的情况下使用此全文查询进行了尝试. PDO参数如下:

I use exec() on this query, and I have tried it with this full-text query, without any dynamic/transformed variables. The PDO parameters are as follow:

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"

有人可以告诉我这里发生了什么吗?谢谢！

Can someone explain me what is happening here? Thanks!

更新:我已经从mysql的CLI执行此精确查询，然后在此处显示它，问题是相同的:所有反斜杠都消失了.因此，问题似乎与PDO无关，而与MySQL有关-或对我而言！我的版本是5.1.53

UPDATE: I have executed this exact query from mysql's CLI, then displayed it there, and the problem is the same: all the backslashes have disappeared. So it doesn't seem the problem has anything to do with PDO, but with MySQL - or me! My version is 5.1.53

推荐答案

\字符在SQL中具有特殊含义.使用绑定参数，而不是将值硬编码到用于查询的基本字符串中.

The \ character has special meaning in SQL. Use bound parameters instead of hardcoding the value into the base string you use for the query.

这篇关于在mySQL中插入带有特殊字符的JSON编码值失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！