I can't figure out how to set proper encoding for json data.

I get some contents from API:

{"id":6889,"name":"IV liga \u015bl\u0105ska I"}
{"id":6890,"name":"IV liga \u015bl\u0105ska I"}

On the PHP side, I save this data to MySQL. Just before writing to database, the data has the correct form (like above).

I created table.

CREATE TABLE IF NOT EXISTS `tab_name` (
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	`content` JSON NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

And I executed two insert command. First command is without escaping and to second command I added backslash.

INSERT INTO `tab_name` (`content`) VALUES('{"id":6889,"name":"IV liga \u015bl\u0105ska I"}');
INSERT INTO `tab_name` (`content`) VALUES('{"id":6889,"name":"IV liga \\u015bl\\u0105ska I"}');

In result I get only the last row which is correct.

SELECT content->'$.name' as name from tab_name;

| name                       |
+----------------------------+
| "IV liga u015blu0105ska I" |
| "IV liga śląska I"         |

Other solution: preg_replace()?

Please help.

    You're not writing your JSON literal text strings in the PHP source code, right? Assuming the JSON from the API you're reading is well-formed, whatever normal escaping/binding mechanisms you're using in your code should get it into the database undamaged. (If you are talking about writing literal strings of JSON into your source code then you already have your answer about what to do with them.)

    (PS: Do you have any special requirements on your table that would justify using a MyISAM storage engine? Are they important enough to forego things like data integrity?)

    Weedpacket

    I'm not writing my JSON literal text strings in PHP source code.

    No, I don't have any special requirements on my table to use MyISAM. So, thanks for that.

    I found out that if I set SQL_MODE to NO_BACKSLASH_ESCAPES, it will stop MySQL from executing escape sequence processing. I can set this option on the specific session so that it is not global. Is this the right solution?

        I would point out that you've posted no PHP source code here. Clearly your JSON isn't being written to the database properly. Your PHP might not be writing the SQL you think it is.

          Write a Reply...