MySQL Collation: latin1_swedish_ci Vs utf8_general_ci

Question

What should I set for Collation when creating tables in MySQL:

• latin1_swedish_ci or utf8_general_ci

What is Collation anyway?

I have been using latin1_swedish_ci, would it cause any problems?

Answer 1

Whatever you do, don't try to use the default swedish_ci collation with utf8 (instead of latin) in mysql, or you'll get an error. Collations must be paired with the right charset to work. This SQL will fail because of the mismatch in charset and collation:

CREATE  TABLE IF NOT EXISTS `db`.`events_user_preference` (
  `user_id` INT(10) UNSIGNED NOT NULL ,
  `email` VARCHAR(40) NULL DEFAULT NULL ,
  PRIMARY KEY (`user_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = latin1_swedish_ci

And @Blaisorblade pointed out that the way to fix this is to use the character set that goes with the swedish collation:

DEFAULT CHARACTER SET = utf8_swedish_ci

The SQL for the cal (calendar) module for the Yii php framework had something similar to the above erroneous code. Hopefully they've fixed it by now.

Answer 2

You can read about character sets and collations as of MySQL 5.5 here:
Character Sets and Collations in General
Character Sets and Collations in MySQL

The collations support is necessary to support all the many written languages of the world. For instance in my language (Danish) we have a special character 'æ'. It sounds like Swedish, German, Hungarian (and more) 'ä' . That character also appears in Danish with words imported form one of those languages. Due to collations' support we can have both printed correctly and and the same sorted (ORDER BY ...) as being identical. Without collations support that was not possible.

Swedish collations is the MySQL default for latin charsets. It works fine with English. English is so easy - it works with everything, because it has no special characters, accents etc. But if you have another language that you use often (for instance Spanish) you could change collation to a Spanish one, so sorting of Spanish Strings would be correct according to Spanish language rules.

A very special example of a collation is one of the German ones. It was created to allowed for sorting like in German phone books. German phone books don't follow general rules of german language!

You can create your own collation if you like. Collations can be compiled or text-format.