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.
[cc lang=”mysql”]
ALTER SCHEMA `schema_name` DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci;
[/cc]
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.
[cc lang=”mysql”]
ALTER TABLE `table_name` DEFAULT CHARACTER SET utf8
COLLATE utf8_general_ci;
[/cc]
Alternatively you can refer to below image for more information on this.
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.
[cc lang=”mysql”]
ALTER TABLE `table_name` CHANGE `field_name` `field_name`
VARCHAR( 200 ) CHARACTER SET utf32 COLLATE utf8_general_ci;
[/cc]
Alternatively you can refer to below image for more information on the same.
Conclusion
After performing above steps you are good to go with storing and retrieving the Unicode Character from your database tables.
[…] This article will be useful when you are dealing with the multilingual site with database driven content. Database Read the original post on DZone… […]
Nice stuff,
I found that for some chinese/japanese charecters we need to use utf8mb4 char set on the server to make things work.
See http://stackoverflow.com/questions/8230949/how-to-save-a-chinese-charecter-in-mysql
[…] Store Unicode in MySQL | Expert PHP Developer […]
Hi Avinash,
thanks for sharing the useful information.
This is a great post, I found similar here too http://www.gnudeveloper.com/groups/mysql/character-sets-collations.html
But I want to know what is the reson behind selecting collation.. what it means.. what make the diff. if I am selecting diff. collation.
Hi Avinash,
i want to store square root ( alt +251) in mysql database and retrieve the same using php and displaying in HTML(browser)
Can u please suggest me what type of database , table should be create in mysql
and how to retrieve using php.
please waiting for ur reply.
not clear with your question please elaborate.. do you want to store combined ASCII value is ALT+251 or what?