PHP JSON vs MySQL JSON Extra Space Between key:value-CodePudding

Using PHP 7.2 and MySQL 5.7

When I convert an array into json in php there are no spaces between values but when it's insert into MySQL JSON column there is a space after the ":". PHP and MySQL say they are using RFC 7159.

PHP- {"MYKEY":"MYVALUE"}

MySQL- {"MYKEY": "MYVALUE"}

Is there a setting in either php or mysql to standardized them so they produce the same result. I have some automated checks that detect changes to values to record in logs as changed values and it's flagging every update because the php produced values never matches the mysql version. I can decode the mysql value in php then encode it back to json to get a match but that seems like a really poor way to handle it, but it'll work if nothing else comes up. Thanks!

CodePudding user response：

MySQL stores JSON value in a binary format not as a string.

MySQL 8.0 Reference Manual / Data Types / The JSON Data Type

So when you get that value you just get its presentation. If you need to check if there is a change, you may need to store your JSON data as string in addition to a JSON field (which requires more space).

Or better calculate a hash value (SHA1 or MD5 is just fine in this case) and compare just the hash to detect any change.

CodePudding user response：

A clumsy solution would be to insert the JSON from PHP into MySQL JSON column and compare:

create temporary table temptbl (
  id int,
  jsonval json
);

insert into temptbl (id, jsonval) values
(?, ?),
(?, ?);
-- pass the JSON value as a string parameter through PHP

select *
from yourtable
where jsonval = (
  select jsonval
  from temptbl
  where temptbl.id = yourtable.id
);

According to the manual, two JSON objects could be compared using = as expected.