Setting default charset/collation for MySQL database

Question

I have a MySQL DB that used to be latin1, and I changed all the columns in all the tables to be utf8. However, I evidently didn't do all I had to do, because I just found that I have a crazy mixture of latin and utf8. Basically, any new tables and columns are different.

Is there a way to set the default charset and collation for the database, so that when I add new tables to this database they are utf8?

Answer 1

Yes, you can set the default character set and collation on various levels.

First, in your question, on the database level:

CREATE DATABASE IF NOT EXISTS foo 
 DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'

or

ALTER DATABASE foo
 DEFAULT CHARACTER SET = 'utf8' DEFAULT COLLATE 'utf8_general_ci'

All new tables in that database should be created with the new character set and database if it's not specified in the table creation command (if it is specified in table command, the specification will override the database default).

You can set the default at a server level (so that all tables are created with the default for all databases unless explicitly stated otherwise) by providing the --character-set-server and --collation-server options in your my.cnf and restarting the server.

And finally, verify the client connection settings. This doc explains how the client connection can fine-tune (and override) the default settings.