Hello
I've got a problem,, I've tried to search the archive for this list with no
luck.
Maybe it's because Unicode problems isn't that rare...
Anyway ..
Here is my problem.
Sorting on a VARCHAR collate utf8_swedish_ci (table with default charset
utf8)..
As many probably know, in Swedish we have the letters ÅÄÖåäö.
If I have a table which has default charset utf8 and collate utf8_swedish_ci
and
I sort the result on a VARCHAR, the letters ÅÄÖåäö comes in between the
'normal' letters
A-Z .. They should be last, after Z in order: ÅÄÖ
eg.
mysql>CREATE TABLE utftest (c1 VARCHAR(30) COLLATE utf8_swedish_ci) default
charset utf8;
mysql>insert into utftest (c1) VALUES ('A');
mysql>insert into utftest (c1) VALUES ('AAAA');
mysql>insert into utftest (c1) VALUES ('Z');
mysql>insert into utftest (c1) VALUES ('Å');
mysql>insert into utftest (c1) VALUES ('Ä');
mysql>insert into utftest (c1) VALUES ('Ö');
mysql>select c1 from utftest order by c1;
c1
----
A
Ö
Å
Ä
AAAA
Z
>total mess.. between A-Z and not the correct ÅÄÖ-order.
If I instead do this !and! change the encoding for my terminal to ISO-8859-1
mysql>CREATE TABLE utftest2 (c1 VARCHAR(30) CHARSET utf8) default charset
latin1;
mysql>insert into utftest (c1) VALUES ('A');
mysql>insert into utftest (c1) VALUES ('AAAA');
mysql>insert into utftest (c1) VALUES ('Z');
mysql>insert into utftest (c1) VALUES ('Å');
mysql>insert into utftest (c1) VALUES ('Ä');
mysql>insert into utftest (c1) VALUES ('Ö');
mysql>select c1 from utftest order by c1;
c1
----
A
Ö
Å
Ä
AAAA
Z
- Gives the exact same answer as before, but
mysql>select c1 from utftest order by c1 collate utf8_swedish_ci;
c1
----
A
AAAA
Z
Å
Ä
Ö
>CORRECT
I'm starting to get lost with every possible collate/charset configuration...
The sollution that gives the correct answer isn't really possible in my
situation.
I have a 30k+ lines application and that one has been reconfigured to receive
the data in utf8-format, therefor also saving it in MySQL as utf8.
What can I do?
As it is now, the data is saved as utf8, the columns/tables in MySQL are
created
as utf8 tables .. but MySQL can't seem to sort this correctly..
This is my current configuration
------------------------------------------
mysql>status
Server version: 5.0.24a-Debian_9-log
mysql>show variables like 'character_set%';
Variable_name | Value
character_set_client | latin1
character_set_connection | latin1
character_set_database | latin1
character_set_filesystem | binary
character_set_results | latin1
character_set_server | latin1
character_set_system | utf8
Running on Kubuntu 6.10 Edgy
$ export|grep LANG
declare -x LANG="en_US.UTF-8"
------------------------------------------
Regards
Joakim Lemström, Bytewize AB, Sweden
