CONVERT function not work properly

Question

In my localhost I have Mysql 5.7 and in my server I have MariaDB 10.2.7 .

I have some json fields and I want sort my records by one of fields in json values, I search in google and stackoverflow and so I use this query :

SELECT id , CONVERT(JSON_EXTRACT(name, '$."fa-IR"') USING utf8) COLLATE utf8_persian_ci AS cName FROM `users` ORDER BY cName ASC

Sample Data:

id   |   Name    
-----+-------------------
1    | {"fa-IR":"\u062a\u0633\u062a","en-US":"Test"}

This function work without any problem in my localhost (MySQL) but not work in server (MariaDB) I mean my records cannot sort properly and show broken up !

@tadman records cannot sort properly, for example if records should sort by A,B,C,D,... in MariaDB sort like this C,D,B,A (but not random and each time I run this query return a same result) — MajAfy, Aug 11 '17 at 16:05
Have you explored how MySQL and MariaDB handle sorting those values independent of JSON being a factor? — tadman, Aug 11 '17 at 16:09
@tadman Sorry but I cannot understand your mean, Does your mean I should not use CONVERT ? — MajAfy, Aug 11 '17 at 16:23
I mean that in order to get to the bottom of this, try a simpler case first. Example: A table with a `VARCHAR(255)` column and some of your test data in it as just plain text. — tadman, Aug 11 '17 at 16:26

score 0 · Answer 1 · answered Aug 18 '17 at 03:29

Do not feed Unicode to MySQL. Especially not with the \u.

In your client use UTF-8 encoding, then configure MySQL to use utf8 (or utf8mb4).

See "Best practice" in http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored

CONVERT function not work properly

1 Answers1