20

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?

zamir
  • 2,144
  • 1
  • 11
  • 23
Run
  • 54,938
  • 169
  • 450
  • 748
  • 1
    Re what collation is: http://stackoverflow.com/questions/3324900 – Pekka Jan 22 '11 at 17:54
  • possible duplicate of [Differences between utf8 and latin1](http://stackoverflow.com/questions/2708958/differences-between-utf8-and-latin1) – Pekka Jan 22 '11 at 17:55
  • possible duplicate of [I don't understand Collation? (Mysql, RDBMS, Character sets)](http://stackoverflow.com/questions/3324900/i-dont-understand-collation-mysql-rdbms-character-sets) – Crozin Jan 22 '11 at 17:59
  • 1
    @Pekka: The question is about collations not encodings, so it's not a duplicate of the question from the second link. ;) – Crozin Jan 22 '11 at 18:00

4 Answers4

6

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.

hobs
  • 18,473
  • 10
  • 83
  • 106
  • 2
    The bug in the above code is because you combine the UTF8 charset with a latin1_* collation. It should use instead `utf8_swedish_ci` (http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html). So, your answer should just say that collations are charset-specific, and collations for one charset cannot be combined with another charset. – Blaisorblade Nov 03 '13 at 19:42
  • Yea, that's a more concise way to say it, I just wanted to answer the OPs question and prevent them from making the mistake spelled out in the code. I should also provide code that works. Will add yours. – hobs Nov 05 '13 at 17:03
5

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.

LPL
  • 16,827
  • 6
  • 51
  • 95
aalanna
  • 51
  • 1
  • 2
0

In Wamp Server 2.5 you can change the collation by going into PHPAdmin, selecting the database you need to change. This will give you another set of tabs. Select the Tab called Operations. In that tab will be section called collation, pick the one you want in the drop-down and select go.

Cburns
  • 17
  • 1
-4

Try these:

<?php
echo htmlspecialchars($string);
echo htmlentities($string);
?>

You can see more info from http://php.net/manual/en/function.htmlspecialchars.php. :D

Worked for me! No more diamonds :)

user3914680
  • 23
  • 3