Change MySQL Database Collation and Character Set

  • Avinash
  • 2
  • Jan 30, 2012
  • Database

This article is about to explain how you can manage database collation and character set of your database. If you are working on website which covers only one language which is english then you may not worry about these stuff.

But If you are dealing with the multilinguag website which includes various languages (chinese, hindi, gujarati, etc.) then you MUST need to take care of this stuff.

To make your database able to handle these characters of different languages you need to set proper Database Collation and Character Set for the database.

Change MySQL Database Collation

Before set the proper Database Collation you may like to get the List of MySQL Database Collation. Execute below query to get the list of all available Database Collation.

SHOW COLLATION;
  SHOW COLLATION;

You can select any Database Collation which arrived from above query.
Now to change the Database Collation you need to perform below query to change it.

ALTER SCHEMA `schema_name` DEFAULT COLLATE collation_name_here ;
  ALTER SCHEMA `schema_name` DEFAULT COLLATE collation_name_here ;

Note: To make your database able to handle these characters of different languages you need to set proper Database Collation and Character Set for the database.

Change MySQL Database Character Set

Same as Database Collation you can get the list of Character Set using below query.

SHOW CHARACTER SET;
  SHOW CHARACTER SET;

Now to change the Database Character Set you need to execure below query.

ALTER SCHEMA `schema_name` DEFAULT CHARACTER SET character_set_name;
  ALTER SCHEMA `schema_name`  DEFAULT CHARACTER SET character_set_name;

Conclusion

With this article you will come to know about how to set the Database Collation and Character Set in MySQL. This will be useful when you are facing a problem related to different language characters.

Next I will post the article about storing unicode characters in database. So subscribe to our feed with your email address.

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by: