MySQL character sets and collations
Working with character-set definitions on the web can be difficult when moving between projects encoded in ISO-8859-1 (Latin) and UTF-8. Someone with limited experience might start by using only ISO-8859-1, then run into trouble on the first web-service integration or database connection for a more elaborate system that considers internationalization. The result can look like cóo.
Incorrect characters on a page are not always caused by the database character set. Application files may use the wrong character set, the database connection may be configured inconsistently with the rest of the project, or an HTML meta tag or HTTP response header may be incorrect.
Character set and collation
Charset and collation are not synonyms. A character set is a collection of characters and their binary representations; a collation is a set of rules for comparing those characters. For example, a case-insensitive collation determines that "A" is equal to "a".
Examples of character sets: latin1, utf8, ascii, cp1250, koi8r (find others with SHOW CHARACTER SET).
Examples of collations: latin1_general_ci, latin1_spanish_ci, utf8_unicode_ci, utf8_general_ci (find others with SHOW COLLATION).
If characters appear incorrectly on a page, check the character set. If comparisons in your queries behave unexpectedly, check the table's collation (and its fields; MySQL lets you set a collation for an individual field).
Collation names usually end in
_cifor case-insensitive,_csfor case-sensitive, and_binfor binary.
Setting the character set and collation in MySQL
To set the character set after connecting, use SET NAMES 'utf8'; (this example selects UTF-8).
To set the character set and collation when creating a database, use CREATE DATABASE minhabase DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;. Tables created in that database without their own character-set or collation settings inherit the database defaults.
If a database was created with incorrect settings, conversion is simple: ALTER TABLE tabela CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;. All content in the table will use the selected character set. Find other commands for changing these settings on the MySQL website.
Fixing "Illegal mix of collations" in a query
If a query reports something like Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE), it is probably comparing two columns with different collations that MySQL cannot reconcile. This often happens in views that use tables from different databases.
You can try converting a field in the query by adding the desired COLLATE, for example: SELECT * FROM campo1 COLLATE latin1_swedish_ci = campo2. This only adjusts the collation when the character set is compatible; you cannot convert campo1 to latin1_swedish_ci if its character set is UTF-8.
To adjust a field's character set, use CONVERT with the desired encoding: SELECT * FROM CONVERT(campo1 USING utf8) COLLATE utf8_bin = campo2.
Tips for avoiding problems
The solutions above are suitable only for small, urgent fixes: they can affect query performance while leaving the underlying inconsistency hidden.
The best approach is to standardize the database and avoid tables or fields with a character set or collation that differs from the rest of the system. This is a good practice, not a strict rule.