MySQL Workbench: Which collation will allow the widest range of characters, including foreign/acented characters?

Question

I am creating an EER Model and want to find the collation that will provide me the most amount of characters to use. The characters that will be stored are generally standard English but on occasion the brands will have foreign and or accented characters. How can I ensure they are supported and not changed to squares or question marks down the road?

Generally I have them stored at UTF-16 but am not seeing that option available, in the default at least.

Answer 1

What you are looking for is the character set not the collation. The character set defines the set of symbols and encoding used to represent those symbols. The collation defines the rules used to compare the characters of a given character set and affect sorting.

Unicode character sets offer the broadest character support. MySQL supports two Unicode encodings:

• UTF8 - uses up to 24 bits to encode a character, backwards compatible with ASCII encoding.
• UCS2 - always uses 16 bits to encode each character, not compatible with ASCII encoding.

Within those two character sets MySQL has multiple collations that specify the sorting rules for different languages, Unicode rules, and binary comparison rules.

Look at: Character Set Support in MySQL Reference Manual.