Store Unicode in MySQL

  • Avinash
  • 6
  • Jan 30, 2012
  • Database

This article will be useful when you are dealing with the multilingual site with database driven content. While dealing with multilingual website you may have faced the problem of data is not being either saved or retrieved properly.

So what could be the reason for this? The reason is, you have not set proper Character set and Collation for the database and tables.

So to make your database able to store unicode characters you need to make sure that you have covered below points:

1) Your Database Collation should be utf8_general_ci.
2) Your Table Collation should be utf8_general_ci.
3) Your Field in which you want to store Unicode should have utf8_general_ci collation.

Let’s see how to set above things.

1) Set Database Collation and Character Set

If you are not sure about my ealier article then you would like to refer my earlier article which show ways to change the MySQL Database Collation. For now if you want to want to change the database collation then you need to execute below query in your query browser.

ALTER SCHEMA `schema_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
   ALTER SCHEMA `schema_name` DEFAULT CHARACTER SET utf8
   COLLATE utf8_general_ci;

2) Set Table Collation and Character Set

After setting up the Database Collation and Character set, you need to change the Table Collation and Character Set in same manner. Have a look at below image for more information on how to set the Table Collation and Character set.

ALTER TABLE `table_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
  ALTER TABLE  `table_name` DEFAULT CHARACTER SET utf8
  COLLATE utf8_general_ci;

Alternatively you can refer to below image for more information on this.

Set Table Collation in MySQL

Set Table Collation in MySQL

3) Set Field Collation

Now to change the Collation you need to execute below query in your query browser. You need to change all fields in which you want to store the Unicode Characters.

ALTER TABLE `table_name` CHANGE `field_name` `field_name` VARCHAR( 200 ) CHARACTER SET utf32 COLLATE utf8_general_ci;
  ALTER TABLE  `table_name` CHANGE  `field_name`  `field_name`
  VARCHAR( 200 ) CHARACTER SET utf32 COLLATE utf8_general_ci;

Alternatively you can refer to below image for more information on the same.

Change Field Collation in MySQL

Change Field Collation in MySQL

Conclusion

After performing above steps you are good to go with storing and retrieving the Unicode Character from your database tables.

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: