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.