Support for Character Sets and Collations in MySQL DB.

Submitted by vivekanandan on Mon, 03/15/2010 - 07:52

The Things we have to consider
1. Database Level Collation Type
2. Table Level Collation Type
3. Fields Level Collation Type

Database Level Collation Type:
When using the parementer in stored procedure,function it will take from the database default character set Type only.
The newly created table also by default holds the database character set.Hence change it first;
Creating new database with utf8 as follows:

CREATE DATABASE testdb1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

but for changing to the existing Database, just give

ALTER DATABASE testdb1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Table Level Collation Type :
The purpose of givng Table level will take the further creating new column in that table will take the table character set.

CREATE TABLE studentinfo (
         	id    INT NOT NULL ,
	        stud_name  varchar(255) NOT NULL
	) CHARACTER SET utf8 COLLATE utf8_general_ci;

for existing table as follows:

ALTER TABLE studentinfo  DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Fields Level Collation Type :
There may be suitation in which each each column will have their their own character set.for that we have to mention by column wise as follwos

ALTER TABLE studentinfo CHANGE name stud_name VARCHAR( 33 ) CHARACTER SET utf8 COLLATE utf8_general_ci; 
ALTER TABLE studentinfo convert to stud_name VARCHAR( 33 ) CHARACTER SET utf8 COLLATE utf8_general_ci

TO change for all column along with valuse as follows

ALTER TABLE studentinfo  CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

Be Carefull before executing this statement;
This will change the following

1.All Fileds with current Collation Type with be converted to utf8_general_ci.
2.All Fileds Values with current Collation will be converted to equallent utf8_general_ci character set
3.The default Collation type for the tables and fileds also changed,
Hence further column will have default collation of utf8_general_ci;

Steps for Tesing Multilingual Character support:

Use The MySQL Editers any one of as follows
1. phpMyAdmin
2. SQLyog
3. Toad for MySQL

1. Goto Google Translate : http://translate.google.co.in/#
2. Type some character as "open source is good" and convert to korean.
3.copy the target korean character in the update statement and
run in any sql editer as mentioned above prefer phpmyadmin(because it shows teh actual foreign language character in the SQL Statement ) .

UPDATE `studentinfo` SET `stud_name` = '오픈 소스가 좋다' ;
SELECT stud_name FROM studentinfo;

The multilingual character will be displayed as boxes or korean characer, That is not a problem,
but it should not be as question mark(?????????) which means the character set is not properly defined.

Groups:

MySQL