JSON in Mysql and Unicode Hex

Question

IN my mysql 5.7 database table there is a field description containing values like

"{\"1\":\"Glasvlies f\\u00fcr Gipskartonfugen\",\"2\":\"5 cm x 25 m Rolle\"}" I'd like to search for für Gipskartonfugen from a symfony 3.4 project.

I already tried with SQL-query:

SELECT * FROM vendor_article v0_ WHERE v0_.description LIKE ?;

WHERE description is %für Gipskarton% or %f\\\\u00cfr Gipskarton% or %f\\u00cfr Gipskarton% but don't get any results.

Same results with SELECT * FROM vendor_article v0_ WHERE CONCAT(v0_.description) LIKE ?;

But when I search for SELECT * FROM vendor_article WHERE description LIKE '%\\u00%'; I get results.

What type is your column?

Schwern
– Schwern

2020-11-07 08:51:23 +00:00
Commented Nov 7, 2020 at 8:51 — Schwern
– Schwern, Commented Nov 7, 2020 at 8:51
Oh, sorry, i missed this. The field type is JSON

sneaky
– sneaky

2020-11-07 09:01:34 +00:00
Commented Nov 7, 2020 at 9:01 — sneaky
– sneaky, Commented Nov 7, 2020 at 9:01

Rick James · Accepted Answer · 2020-11-07 16:47:50Z

1

This is a client issue at the time of INSERTing. If you are using PHP, be sure to include JSON_UNESCAPED_UNICODE when calling json_encode(). If you are using some other client, say so.

answered Nov 7, 2020 at 16:47

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sneaky Over a year ago

Seems you're right, but when I work next time on the project, I try to get this running with doctrine, because there is only one "normal" setter function: ` /** * Set description. * * @param json|null $description * * @return Article */ public function setDescription($description = null) { $this->description = $description; return $this; }` There I can't override the json_encode function. But when I manually set to für in Database, it works.

sneaky Over a year ago

I found the error now. The problem was, that there was a transformer for this field. The transformer already encoded to JSON and put that into the json mysql field. So it was double json encoded. If someone still has the problem with the unicode-chars, either your installation setting is wrong or take a look here: zgadzaj.com/development/php/symfony/…

Schwern · Accepted Answer · 2020-11-07 21:40:22Z

0

MySQL 5.7 seems to have real problems with escaped Unicode in JSON. I initially though it was because the default character set is latin1, but that doesn't seem to be the case.

create table vender_article (
  description json not null
) character set utf8mb4;

insert into vender_article values ('"f\\u00fcr"'), ('"für"');

select * from vender_article;

MySQL 5.7 gives null and "für". MySQL 8.0 gives für and für.

Try changing your database or server character set to utf8mb4 and see if that helps. I can't do this on dbfiddle.

alter database <your database> character set utf8mb4 collation utf8_unicode_ci

Alternatively, unescape your JSON before inserting it.

Alternatively, update to MySQL 8. This will give many features and bug fixes including better Unicode and JSON support.

If you store the column as [`JSON`](https://dev.mysql.com/doc/refman/5.7/en/json.html) it will understand the unicode escapes.
This also gives you proper JSON functions.

SELECT * FROM vendor_article where description->"$.*" like '%für Gipskartonfugen%';

edited Nov 7, 2020 at 21:40

answered Nov 7, 2020 at 8:59

Schwern

167k28 gold badges225 silver badges370 bronze badges

4 Comments

sneaky Over a year ago

Sorry, doesn't work in my case. I still doesn't get a result.

Schwern Over a year ago

@sneaky My mistake, looks like this requires MySQL 8. I would strongly suggest upgrading if you can, MySQL 8 brings a great many good features and fixes.

Schwern Over a year ago

@sneaky Does your JSON column have all those extra escapes and quotes in it? select description from vender_article should show '{"1":"Glasvlies f\u00fcr Gipskartonfugen","2":"5 cm x 25 m Rolle"}'); not "{\"1\":\"Glasvlies f\\u00fcr Gipskartonfugen\",\"2\":\"5 cm x 25 m Rolle\"}"

sneaky Over a year ago

On console it shows "{\"1\":\"Glasvlies f\\u00fcr Gipskartonfugen\",\"2\":\"5 cm x 25 m Rolle\"}" in PHPStorm Database Console it shows "{\"1\":\"Glasvlies f\\u00fcr Gipskartonfugen\",\"2\":\"5 cm x 25 m Rolle\"}" The Database is already utf8mb4, the table is already utf8mb4_unicode_ci. By the way the above written command must be alter table vendor_article convert to character set utf8mb4 collate utf8mb4_unicode_ci; in 5.7 this doesn't work on databases.

Collectives™ on Stack Overflow

JSON in Mysql and Unicode Hex

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related