Changing database character set

Why to consider changing database character set? Why do i need to do this? As to find a more straight answer please look at the image below, taken from one of my websites built in WordPress:

What was the problem?

You see those ?????? characters there in the form fields in imagge above. This was the problem occurred while saving the data. This problem usually occurs when you are try to save data to a table field but the table field does not understand the language which your data was in. Here in my case, the character set (or in short charset) of data i was trying to save was UTF8 while the table column only understood the latin1 character set .

NOTE: Prior to and including WordPress Version 2.1.3, most WordPress databases were created using the latin1 character set was latin1 and the collation was latin1_swedish_ci while data i was trying to save was in Hindi (UTF-8).

How to fix it?

The only problem in my case was with the difference between the character sets of data i was trying to save and columns of the table to which i tried to save the data. Now in order to fix this problem i will be changing character set of table columns to UTF8. UTF8 is the standard charset for modern data which supports all internet-friendly languages and it also support my mother language, Hindi.

To fix this problem of wordpress database character set i ran the following command through phpmyadmin (Actually phpMyAdmin ran it for me, i only tried to change the character set by using their web-interface ;)):

ALTER TABLE `wp_postmeta` CHANGE `meta_key` `meta_key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL , CHANGE `meta_value` `meta_value` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL

Hold on. This may not work for you yet, keep reading.

In some cases the charset of your database or table may be different than the data. To know which charset your table or the data is in there are different ways, one of which is to view your database and tables in a tool like phpMyAdmin. You can also view the character set by running the following commands:

SHOW CREATE DATABASE my_database;

which will show charset of your database and,

SHOW FULL COLUMNS FROM wp_postmeta;

will show the charset of you table columns under Collation column in the result.

Changing or converting database character set

The best way to do it is by preparing and running the appropriate sql commands or queries manually. First, note down all the table names and their column name which are required to be updated for character set. Prepare query and run one at a time or if you are confident enough run it as a single query, each separated by a new line while each query ending with a semicolon (;)

Changing charset of database

ALTER DATABASE my_database CHARACTER SET utf8;

Changing charset of a table

ALTER TABLE wp_posts CHARACTER SET utf8;

Changing charset of table columns or fields

The general format is this:

alter table TABLE_NAME change COLUMN_NAME COLUMN_NAME COLUMN_DATA_TYPE character set utf8;

So one would run to convert the database character set:

or to keep it simple, the following should also work:

ALTER TABLE `wp_postmeta` CHANGE `meta_key` `meta_key` VARCHAR( 255 ) CHARACTER SET utf8 , CHANGE `meta_value` `meta_value` LONGTEXT CHARACTER SET utf8

This page at WordPress website helped me with understanding the problem in detail and fixing it and it should also help you with as well.