Collation setting?

[Square1]

Hi all:

What is the proper MySQL collation setting for an American English database? Right now I am defaulting to latin1_swedish_ci and I have a gut feeling that isn't right! 😉

[johanafm]

Well, if you do not want swedish letters 'å' 'ä' and 'ö' to sort after 'z', but rather be treated in the english fashion as 'a' and 'o' respectively (with accents), then you need to change collations. But otherwise I doubt you'd need to worry. Read the documentation if you want to learn what's different.

You could of course switch to latin1_general_ci

[Square1]

Thank you.

Ok, I have been Googling and Googling and my head is spinning. I now the know the basic differences between the utf8 versions, but I don't think that is my issue.

I do not need the Swedish version. But I think my question is really, should I use latin1_general_ci or utf8_general_ci. And I could not find a straight answer for this.

Also, does my charset need to be changed? Currently it is charset=windows-1252.

I am doing a total recode of my site and would like to get this right from the get go!

[johanafm]

The collation you choose has to match the character set. The character set defines the mapping between values and characters, that is specifies how to interpret values to present something more user friendly as output. The collation specifies how to interpret the characters as such for the purpose of sorting and matching.

For example, wihtout collation, ordering of US-ASCII 'Z' and 'a' would put 'Z' (0x5a) before 'a' (0x61).

Match or no match?

WHERE 'a' = 'ä'

... that depends on the collation. In sweden, no. In english, yes.

So obviously the collation has to match the selected character set. You cannot store things as latin1 and collate utf8, or store things as utf8 and collate latin1

Windows 1252 is (more or less) latin1, aka iso-8859-1.

Generally I'd recommend UTF-8. That is, character set utf8, and collation utf8_unicode_ci.