MySQL JSON编码字符串在INSERT..SELECT后损坏

I'm encoding array of Image URLS into json string and store them in database. (utf8_general_ci).

我将图像URL的数组编码为json字符串并将它们存储在数据库中。 (utf8_general_ci)。

When I insert data into table and retrive it, json_decode() is capable of decoding it.

当我将数据插入表并检索它时,json_decode()能够解码它。

However, when I copy data from one table to another (INSERT INTO ... SELECT statement) data after retrieving from database cannot be decoded anymore.

但是,当我将数据从一个表复制到另一个表(INSERT INTO ... SELECT语句)后,数据从数据库中检索后无法再解码。

Instead, i get corrupted json ENCoded string. Even empty array [] cannot be properly decoded.

相反,我得到了损坏的json ENCoded字符串。即使是空数组[]也无法正确解码。

It converts from http://pl.tinypic.com/r/fwoiol/8 into http://pl.tinypic.com/r/bgea05/8

它从http://pl.tinypic.com/r/fwoiol/8转换为http://pl.tinypic.com/r/bgea05/8

(had to make images since those squares cannot be copied as text).

(必须制作图像,因为这些正方形不能作为文本复制)。

Edit, After checking a bit more i tried to bin2hex() both strings from database.

编辑,检查了一下后,我试图从数据库bin2hex()两个字符串。

Both seem to be exactly same.

两者似乎完全相同。

However, one decodes and one does not. The

但是,一个解码,一个解码。该

5b22687474703a5c2f5c2f7777772e
changes into
0022687474703a5c2f5c2f7777772e

So, json_decode only changes 5b into 00 in string.

因此,json_decode仅在字符串中将5b更改为00。

It's like It's losing encoding somewhere?

这就像它在某处丢失编码?

Edit 2

static public function jsonDecodeFieldsArray($entries, $fields = array('features','images')){
    foreach($entries as $key => $entry){
        $entries[$key] = self::jsonDecodeFields($entry, $fields);
    }
    return $entries;
}
static public function jsonDecodeFields($entry, $fields = array('features','images')){
    foreach($fields as $field){
        if(isset($entry[$field])){
            $entry[$field] = json_decode((string) $entry[$field], true);
        }
    }
    return $entry;
}

I'm using code above, to decode keys of array specified by $fields. However, it not only decodes wrongfully. But also affects keys that are not listed in $fields. Corrupting their encodings.

我正在使用上面的代码来解码$ fields指定的数组的键。但是,它不仅错误地解码。但也会影响$ fields中未列出的键。腐蚀他们的编码。

More to add. If I dont use those functions and use only json_decode on fields json_decode($array[0][images], true) it works fine.

更多要补充。如果我不使用这些函数并且只在字段json_decode($ array [0] [images],true)上使用json_decode,它可以正常工作。

2 个解决方案

#1

To Clarify that I found answer/solution I write this Answer

为了澄清我找到了答案/解决方案,我写了这个答案

The reason behoind this error was not SQL error and data was proper. I had an example array of:

原因是这个错误不是SQL错误,数据是正确的。我有一个示例数组:

$many_entries = array(
    array(
        'features' = > 'json_encoded_string'
        'images' = > 'json_encoded_string'
    ),
    array(
        'features' = > 'json_encoded_string'
        'images' = > 'json_encoded_string'
    )

);
// And 
$one_entry = array(
    'features' = > 'json_encoded_string'
    'images' = > 'json_encoded_string'
);

Now I had 2 functions. One to Parse $many_entries (jsonDecodeFieldsArray) array and one to Parse $one_entry array structure (jsonDecodeFields).

现在我有2个功能。一个解析$ many_entries(jsonDecodeFieldsArray)数组,一个解析$ one_entry数组结构(jsonDecodeFields)。

The problem was I used jsonDecodeFieldsArray on $one_entry which made jsonDecodeFields iterate on strings.

问题是我在$ one_entry上使用了jsonDecodeFieldsArray,它使jsonDecodeFields迭代字符串。

#2

It is odd that the character encoding is changing through the transmission. I would say check your charset(s) in PHP but you said the issue is only occurring in a table => table SQL transfer. I would still check the charset of the column / table.

字符编码通过传输改变是奇怪的。我会说用PHP检查你的charset,但是你说这个问题只发生在table => table SQL transfer中。我仍然会检查列/表的字符集。

You can fix the issue by running a str_replace() upon decoding. For example:

您可以通过在解码时运行str_replace()来解决此问题。例如:

$DB_ARRAY = $DB_QUERY->fetch_array();
$CORRECT_ENCODING = json_decode(str_replace('0x93', '[', $DB_ARRAY['urlstring']), true);

You would, of course, need to know what the wrongly encoded character is. Or its ASCII code equivalent.

当然,您需要知道错误编码的字符是什么。或者它的ASCII码等价。

#1