Re: Collation problem when used with "SET NAMES"

[Tore Krudtaa]

Thanks for your answer...

So:

using: collate with order by as below is working (at least for me)

$query = "select sql_cache f1, f2, f3
from page
order by f2, f3 collate utf8_danish_ci";

but: setting collate with SET NAMES like:
mysql_query("SET NAMES 'utf8' collate 'utf8_danish_ci' ");

have no effect on the ordering in this same query as above (but without collate in query though):
Like this:
$query = "select sql_cache f1, f2, f3
from page
order by f2, f3";

Is it a bug or...???

In my case I have
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
for the table:

Is the following two statements correct?
If I want to use another collation when using ORDER BY like example above I will have to use COLLATE in each query with the appropriate collation, in order to get the correct sort order for the language stored in the database.

But if I want to do textual comparison e.g. using LIKE, then I can trust that the COLLATION used in in SET NAMES will be used?

In my humble opinion: When using:
mysql_query("SET NAMES 'utf8' collate 'utf8_danish_ci' ");

then it should make MySQL use the utf8_danish_ci collation both for literals, and e.g. when using ORDER BY on strings.... in the query
and not only when comparing literals.
Is there a good reason for it to not behave like that?



Edited 1 time(s). Last edit at 07/28/2006 02:37PM by Tore Krudtaa.