Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Set collation at runtime

Posted on 2005-04-25
7
Medium Priority
?
846 Views
Last Modified: 2008-02-01
I have all data in DB UTF-8 encoded with general-ci collation.
Depends on the user (multilingual) general-ci doesn't sort properly.
For example: if Slovenian user is connected I need lists to be sorted out by using "utf8-slovenian-ci" collation.
By default (using SET NAMEs) everything is set to utf8 using general collation.
How can I programatcaly change connection collation (using php preferably).

Thanks in advance.
Pet
0
Comment
Question by:hpet
  • 4
  • 3
7 Comments
 
LVL 8

Expert Comment

by:lokus
ID: 13864269
See this http://dev.mysql.com/doc/mysql/en/charset-connection.html
SET NAMES is acually just a shortcut to set the different character set, you can set the collation and character set each differently.

But, I don't know if the index can still be used or not since the collation should determine the order of the data.
0
 
LVL 8

Accepted Solution

by:
lokus earned 200 total points
ID: 13864468
Just found the way to do it better
http://dev.mysql.com/doc/mysql/en/charset-collate.html

You can have COLLATE in the ORDER BY which can be used to sort your result.

0
 
LVL 1

Author Comment

by:hpet
ID: 13864520
Hi Lokus,

Thanks, will check mysql docs once again. Been there before...
I am also aware of possibility to include collation in select query, but it would be nicer to set this once (at one place) then setting up this logic at every select statement.

I know that set names is a shorthand for 3 individual statements, but using later the collation didn't change... I probably did something wrong and I will try it again.

Do you know if it is possible to user SET NAMES at first and then just change collation using SET collation?

About the index I read that too, but I did some testing and everything seemed to be sorted out just fine.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 1

Author Comment

by:hpet
ID: 13864851
I can get correct results using COLLATE in ORDER BY clause, but is there a way to set this once? so that I don't have to write this at every statement? something in a similar way SET NAMES does (but unfortunately this sets to default UTF8 collation UTF8_GENERAL_CI.

I tried again all other options like: SET connection_collation, etc... and no results.
0
 
LVL 8

Expert Comment

by:lokus
ID: 13865728
There are different collation settings, to see the list
SHOW VARIABLES LIKE 'collation%';

Maybe 1 of these will helps if connection_collation does not work.

0
 
LVL 1

Author Comment

by:hpet
ID: 13865786
Did try changing those but they tend to stay unchanged :(
I guess currently I will update select statements with collation (those that display ordered lists are not that many) but if I come across "one time" solution it would be nice.
0
 
LVL 8

Expert Comment

by:lokus
ID: 13872796
There is always a collation in all the character string column, I guess by default, the actual collation of the column will be used, unless it is overwritten, so a global change is not possible unless you set the collation of the column to a particular collation sequence you want, however, this will be set across all connections.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question