MySQL, UTF-8, character ordering

Question

By using MySql server in Linux Ubuntu through C-Api and Gtk GUI toolkit I have some general problems regarding utf8 sorting and ordering when using croatian characters "čćžšđČĆŽŠĐ".

My MyISAM tables are created with ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci.

Server returns data but MySql are not aware of croatian letters. They "don't know" to sort them properly and in case of this characters they "don't know" to swap lowercase (say "č") to uppercase "Č". With all other letters everything works OK. So, now I have only option to additionally sorting query result with GTK which handles all those cases properly. But (of course) this ist "last" solution I would want.

Any recommendations?

score 3 · Answer 1 · answered Jul 22 '12 at 06:56

3

Use the following query:

ORDER BY n COLLATE utf8_croatian_ci

answered Jul 22 '12 at 06:56

Saeid

483
5
14

score 3 · Accepted Answer · answered Jan 09 '12 at 01:16

3

Use utf8_croatian_ci collation, if you're using mysql-5.6 or above. Discussion here

Sretno

answered Jan 09 '12 at 01:16

Amadan

191,408
23
240
301

I see this somewhere but if I use utf8_croatian_ci all my "INSERT" statements fails. Is it possible that my system don't have this encoding? I+m using "mysql Ver 14.14 Distrib 5.1.58, for debian-linux-gnu (x86_64) using readline 6.2" – Wine Too Jan 09 '12 at 01:21
3

I'm pretty sure 5.1.58 < 5.6... So, no Croatian collation for you. Croatian has some messed up sorting rules (polo < polje) which made it hard to implement, which is why `cp1250_croatian_ci` is incorrect, and `utf8_croatian_ci` is late. – Amadan Jan 09 '12 at 01:23
Thanks Amadan, then I should find first how to upgrade to 5.6 for my ubuntu version. I will note here what happens, or ask for additional help. Hvala. – Wine Too Jan 09 '12 at 01:32
After very much of troubles I finaly install "mysql Ver 14.14 Distrib 5.6.4-m7, for Linux (x86_64) using readline 5.1". Now utf8_croatian_ci is recognized and accepted but queryed data is not oredered properly again. I don't talk about digraphs but about basic order. Any idea what can be a problem? – Wine Too Jan 10 '12 at 11:00
2

Even if you try `SELECT * FROM t ORDER BY n COLLATE utf8_croatian_ci`? – Amadan Jan 10 '12 at 11:22
This is exact query I use: SELECT DTBL_ID, mjj, kolic, prodano, name, sb, prodajnac FROM invlista WHERE name LIKE 'proba%' ORDER BY name COLLATE utf8_croatian_ci ASC LIMIT 4048 – Wine Too Jan 10 '12 at 12:17
Hmm... I don't think I can help you after all. Can you check whether other collations work? E.g. the one from the docs, as referenced by [this question](http://stackoverflow.com/questions/280508/mysql-collations-not-working-as-advertised-in-documentation)? Then open a new question, integrating all the newly collected data, since at this point I'm probably the only one reading this. – Amadan Jan 10 '12 at 12:35

score 1 · Answer 3 · edited May 23 '17 at 12:13

1

as far as I know, I don't think MySQL support that kind of operations on this kind characters, I believe these two links might help, you have to solve the problem by hand.

Upper/Lower case in unicode

MySQL Reference, Unicode

edited May 23 '17 at 12:13

Community

1
1

answered Jan 09 '12 at 01:14

Adi

5,089
6
33
47

Thanks for helping. I will try simplest solutions first :) – Wine Too Jan 09 '12 at 01:36

score 0 · Answer 4 · answered Oct 05 '13 at 17:16

0

for persian language use this query... Great work for me !

ORDER BY n COLLATE utf8_persian_ci

Note: change 'n' with your Filed Name.

answered Oct 05 '13 at 17:16

M-O-H-S-E-N

346
4
7

score 0 · Answer 5 · answered Mar 18 '16 at 14:43

I have no access to Mysql server 5.6, so I did the following:

I added another column in my table and filled it with data I want to sort. Then I renamed all the words that are not readable to mysql - like čevapčići ... etc. I make čevapčići cvzevapcici and now I can make my result ordered by my sort column and show original column.

Example:

Ime

Čuka Ćićarija Anstar Žena Balast

Sort

Cvzuka Czzićarija Anstar Zzzena Balast

select Ime from table order by Sort;

Result is expected:

Anstar Balast Čuka Ćićarija Žena

score 0 · Answer 6 · answered Jan 22 '12 at 00:06

I find solution finally! MySql server should really be 5.6 or higher. If somebody stuck with this just do following:

//immediately after all connections, for example...
mysql_real_connect(conn, "localhost", mysql_user_name, mysql_password, database, 
0, NULL, 0);

//is needed to do this CRITICAL query:
//=====================================================================
  mysql_query(conn, "SET NAMES 'utf8' COLLATE 'utf8_croatian_ci'");
//=====================================================================

//and then query to get data, for example...
SELECT DTBL_ID, mjj, kolic, prodano, name, sb, prodajnac FROM invlista WHERE name
LIKE 'čoko%' ORDER BY name COLLATE utf8_croatian_ci ASC LIMIT 4048

In this case you will get sorted all with croatian rules, national letters "čćšđž" will be ignored case, digraphs will be sorted properly too. You don't have to setup any collation or charsets on your server.

This is all you need!

MySQL, UTF-8, character ordering

6 Answers6

Ime

Sort