What is MySQL Collation, how to use it in practice?

Let's say I want to make a search engine in some weird languages in 4 languages:

English Swedish Hebrew Arabic

How would I set the col开发者_StackOverflow社区lations in MySQL ?

A collation defines:

• The character set used to store the characters (UTF8, ISO8859, etc.)
• The sorting and presentation rules

If you want to have different languages (where they cannot be sanely represented in the same collation, as you mention) you can have columns with different collations.

Of course you can set collation at database and table levels too, and even set collation to a string literal.

If you can find a single collation that handles all the languages you're interested in, that's best.

The collation determines how MySQL compares strings.

A list of all character sets and collations can be found with:

SHOW CHARACTER SET;
SHOW COLLATION;

To change the collation for a table use:

ALTER TABLE `my_table` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci

http://dev.mysql.com/doc/refman/5.0/en/charset.html