MySQL Bugs: #98135: JSON to string includes extraneous spaces...

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

沉稳的冰棍 · C + + calling the ...· 24 分钟前 ·

鼻子大的杨桃 · vba如何获取本计算机名,【Access源码 ...· 25 分钟前 ·

闷骚的四季豆 · Form属性文档 | Avue· 3 小时前 ·

文雅的小蝌蚪 · RDS迁移至MaxCompute实现动态分区 ...· 3 小时前 ·

犯傻的蟠桃 · nginx post转get 并加参数 ...· 7 小时前 ·

傻傻的大脸猫 · Python递归调用深度的问题· 2 周前 ·

怕老婆的烈酒 · GitHub - ...· 2 月前 ·

眼睛小的铅笔 · 1.5 ...· 4 月前 ·

飘逸的水龙头 · 那些搞笑的谐音梗文案 1 吸血鬼喜... ...· 5 月前 ·

爱热闹的松鼠 · AE插件：VC Element 3D ...· 8 月前 ·

Joey PRIVATE

Description:
When MySQL converts JSON to a string it apparently gives no control over the default formatting.
This has two immediate and very obvious effects:
1. The default output includes whitespace which is unnecessary and which may reduce performance.
2. The inclusion of whitespace in the output significantly increases the proportion of cases in which serialized JSON strings from MySQL for the same data will differ from serialised strings from other sources.
Most sources will serialize JSON by default as efficiently as possible so will not include whitespace. Those with specific peculiarities tend to take flags to
There are many cases where two implementations cannot be relied upon to serialize structures to identical strings, for example, object key order, large string handling, escaping of non-ascii, etc. Generally however there are cases that are usually safe, for example:
For [0, 1, 2, 3] virtually all common and basic platform implementations can be relied upon to produce [0,1,2,3] where as MySQL will product [0, 1, 2, 3]. Some deduplication mechanisms may want to rely on traditionally safes cases but would not be able to.
Efficiency speaks for itself, for the ability to match identical documents based on serialized form, removing spaces would not be intended to exhaustively solve that problem.
How to repeat:
Any SQL that causes JSON to be converted to string.
Suggested fix:
Add options to control the format of JSON output, specifically to not include any excessive whitespace.

[9 Apr 2020 13:32] MySQL Verification Team

Hi Mr. PRIVATE,
Thank you for your feature request.
Can you just give us a small test case that will display this behaviour. If we can reproduce it, then we will verify this feature request.
Thanks in advance.

[10 May 2020 1:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

[10 May 2020 8:00] MySQL Verification Team

No need for a specific testcase.  Anything will do:
-- -----
create table t(a json);
set @a:=concat('[',repeat('1,',100),'1]');
insert into t values(@a);
select a from t;
-- -----
Output is:
[1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1]
A third of the output is wasted on whitespaces.  This would be more compact:
[1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]
Seems like reporter wants a function,  opposite of JSON_PRETTY that outputs the latter compact format.

[11 May 2020 12:11] MySQL Verification Team

I agree, but due to the existence of the good workaround, it is not a high priority request.

[10 Feb 2022 21:19] Marcin Gil

This causes serious issues with compatibility with json_encode in PHP.
After data is saved and read, the string is altered with MySQL format (added spaces). Some libs like ORMs will think that the field was modified.

[11 Feb 2022 13:18] MySQL Verification Team

Your note is copied to our internal bugs database.

[16 May 2023 19:32] Marcin Gil

MySQL Verification Team: could you share the "good workaround" mentioned in Your comment?
Thank you.

[17 May 2023 11:56] MySQL Verification Team

You can include TRIM() function in the proper places ......

[17 May 2023 13:22] MySQL Verification Team

Obviously TRIM() won't work.  One might be tempted to use REPLACE() but it will only work on datasets that don't contain legitimate spaces:
mysql> select replace(a,' ','') from t;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| replace(a,' ','')                                                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1] |
| [1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1] |
| [1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1] |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Hence,  there is no workaround.

[27 Jul 2023 12:24] Marcin Gil

It's hard to say who should adjust - PHP or MySQL, but the fact is that json output is different at the moment and two technologies which are often used together do not have a common json output format. It's not possible to bend one or another to get exactly the same output. 
For example one cannot use json_encode in PHP to produce exactly same format as MYSQL CAST(string as JSON). Taking example of an ORM library for PHP, it causes a case when json value is treated as modified, even though it was only decoded and encoded again.

[27 Jul 2023 12:27] Marcin Gil

I not not see TRIM() as a possible workaround. We are talking about an output of whole JSON object, not a single value.

[27 Jul 2023 12:49] MySQL Verification Team You can try using REPLACE() function ..... Anyway, this is a verified feature request. [27 Jul 2023 13:21] Marcin Gil

REPLACE could work for simpler test cases like the one described in the ticket, but not for complex JSON objects.
Thank you.

Page generated in 0.016 sec. using MySQL 8.0.36-u4-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of