Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

noob sysadmin here...

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?

Thanks!
Daniel

share|improve this question

migrated from serverfault.com Oct 6 '11 at 14:38

This question came from our site for professional system and network administrators.

    
Hi Daniel, I'd like to encourage you to visit Database Administrators for any database configuration and tuning issues you might face. –  Derek Downey Oct 6 '11 at 14:12

1 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.

share|improve this answer
2  
You should also check that the clients default char-set is correct, as the db server can have a different default to the clients! –  Dave Rix Oct 6 '11 at 14:52
    
@daverix thanks, forgot about that one. –  Derek Downey Oct 6 '11 at 15:06
    
@DTest : Concise and direct answer. +1 !!! –  RolandoMySQLDBA Oct 6 '11 at 15:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.