Hello Mike,
Mike Futerko wrote:
> Hello Alexander,
>
>> ALTER DATABASE changes only the default collation, which
>> will be used in the further CREATE TABLE. It doesn't touch
>> the existing tables.
>>
>> You have to alter your tables, for example:
>>
>>
>> ALTER TABLE t1
>> MODIFY col1 VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci;
>>
>>
>> Or you can just run "mysqldump", then fix the dump file changeing
>> "utf8_general_ci" to "utf8_unicode_ci" and then fed it back using
>> "mysql".
>
I'm for a mistake in my previous letter.
utf8_general_ci is the default collation for utf8,
so the collate clause is not really displayed in the dump file.
After running "mysqldump", please open the dump file in
your preferred text editor, for example vi, and change ADD
"COLLATE utf8_unicode_ci" into each table defition, after
"CHARSET=utf8".
For example:
CREATE TABLE t1 (
a varchar(10) collate utf8_unicode_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
should become:
CREATE TABLE t1 (
a varchar(10) collate utf8_unicode_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;
Then save the dump file, and load it back to mysql.
Before running the ORDER BY query again make sure "SHOW CREATE TABLE"
says "COLLATE=utf8_unicode_ci". If it doesn't, then something went
wrong.
>
> The mysqldump trick didn't work for me, I have dumped the DB, dropped it
> by (DROP DATABASE) then created (CREATE DATABASE ... COLLATE
> utf8_unicode_ci;) and then restored the DB from dump.
Everything is correct. Every table has its own default collation,
which is I believe utf8_general_ci for you now.
It's written into the dump file when using "mysqldump".
Or it may not written if it is the default collation for
the character set (like utf8_general_ci for utf8), and
just implicitely assumed.
Changeing only the database default collation doesn't change
anything if you just load this dump without changeing/adding
table level collations.
Please find more information how the defauls work here:
"Determining the Default Character Set and Collation"
http://dev.mysql.com/doc/refman/4.1/en/charset-defaults.html
> But again the sort
> order was definitely wrong. So I just altered necessary tables as per
> your example - that helped. Looks like even CREATE DATABASE with the
> preferred collation is buggy :(
This is documented behaviour. I cannot see any bugs here.
By the way, there is an easier way to change collation:
alter table t1 convert to character set utf8 collate utf8_unicode_ci;
For example:
mysql> create table t1 (a varchar(10)) character set utf8 collate
utf8_general_ci;
mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` varchar(10) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> alter table t1 convert to character set utf8 collate utf8_unicode_ci;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`a` varchar(10) collate utf8_unicode_ci default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
>
>
> Many thanks for the help,
> Mike.
