Wednesday, December 2, 2015

Display and store Hindi content in MYSQL

In this tutorial, I'll tell you how to insert Hindi content or any other script in MySQL, and also how to retrieve them from tables. Hindi data could be dealt using 'utf8' encoding. Only issue arises is because of display problem in MySql querry browsers. So here is a series of solutions to avoid that. 
  1. Write the output into any text file
    for example: select * into outfile "filepath/filename.txt" from tableName; 
  2. Since browsers support utf8, we can redirect this output to any browser, or even to any other application that supports utf8.
  3. We can also spool all the activities and output into any text file
     for example : spool "filepath/filename.txt" 

          spool off; //when you don't want to show output into text. 


Necessary steps to deal with Hindi Data(Devanagiri Script) or any other script which could be encoded as UTF8:
  1. Give the following in command line querry browser . 
      SET NAMES 'utf8'; 
  2. Create any database
    create database स्टुडेन्ट character set 'utf8'; 
  3. use database
    use स्टुडेन्ट;
  4. create tables as below:
    create table नाम (ID int,firstname char(20),lastname char(20)) character set 'utf8'; 
  5. Now perform any operation on this table/database which includes any hindi text. 
    insert into नाम values(001,'कमल','नयन'); 

All the output will be written to text file, mentioned during first step. To display hindi font on browser, use below line in your HTML file.
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8">
We can even provide database names, table names and column names in HINDI.