CODEWITHSUNDEEP
mysql
Wine Too
Wine Too

Reputation: 4655

MySQL, UTF-8, character ordering

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?

Upvotes: 2

Views: 7238

Answers (6)

an-arhos
an-arhos

Reputation: 1

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

Upvotes: 0

M-O-H-S-E-N
M-O-H-S-E-N

Reputation: 376

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

ORDER BY n COLLATE utf8_persian_ci

Note: change 'n' with your Filed Name.

Upvotes: 0

Saeid
Saeid

Reputation: 493

Use the following query:

ORDER BY n COLLATE utf8_croatian_ci

Upvotes: 3

Wine Too
Wine Too

Reputation: 4655

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!

Upvotes: 0

Amadan
Amadan

Reputation: 198486

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

Sretno

Upvotes: 3

Adi
Adi

Reputation: 5179

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

Upvotes: 1

Related Questions