Displaying and Configuring Character Sets in MySQL

Display all character sets:

show variables like 'character_set%';

Change the character set for a table:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

BUT with the caveat that

The CONVERT TO operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). In this case, you have to do the following for each such column:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

The reason this works is that there is no conversion when you convert to or from BLOB columns.


The process to completely change all mysql's character sets from latin-1 and make this garbage:
(using the show variables command from above)

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

look beautiful like this:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

involves simply adding the following to the [mysqld] section of your my.cnf (or /etc/mysql/my.cnf if you like, and are on a debian based machine)

collation_server=utf8_unicode_ci
character_set_server=utf8

and adding the following to BOTH the [client] section AND the [mysqld] section:

default-character-set=utf8


This is simply in case you cannot go back and re-compile mysql using
./configure --with-charset=utf8 --with-collation=utf8_general_ci