how to properly store and display unicode in mysql

Question

i have installed WAMP in my laptop & i have created a mysql table

CREATE TABLE `users` (
  `uname` varchar(20) NOT NULL,
  `pass` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`uname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

the column property of the table is shown here when i insert a hindi unicode using

SET NAMES utf8;
insert into users(uname, pass) values ('सूरज','abc');

the content of the table is shown here

the unicode word सूरज does not shown properly. where i have done wrong ?

Still using MyISAM now a days? You should consider using InnoDB as engine. — Raymond Nijland, Oct 06 '17 at 15:36

score 2 · Answer 1 · answered Oct 06 '17 at 17:04

Here the problem is the SQL admin program like toadformysql, mysql-workbench-community etc. is not compatible to insert the unicode characters properly. Other program like SQLyog, phpMyAdmin etc. provide compatibility to insert the unicode characters properly into the mysql table/database.

The solution for this problem is to use the compatible SQL Admin application

score 0 · Answer 2 · answered Oct 06 '17 at 15:29

0

You should change to utf8 multibyte with.

ALTER TABLE `users` CHARSET=utf8mb4, COLLATE=utf8mb4_bin;

Query

SELECT * FROM users

Result

uname         pass    
------------  --------
सूरज          abc

demo http://www.sqlfiddle.com/#!9/4c5b37/1

answered Oct 06 '17 at 15:29

Raymond Nijland

11,488
2
22
34

still the same result. I am not an expert of mysql. i think i am missing some setting of mysql server. – Linendra Soni Oct 06 '17 at 15:41
@Linendra Soni Try a different SQL admin program like SQLyog. – Raymond Nijland Oct 06 '17 at 15:43
the same is working fine with the phpMyAdmin. but when i am using the Toad for mysql or mysql workbench it show the ????? result. Why these program is unable to insert the unicode data in mysql properly – Linendra Soni Oct 06 '17 at 16:00

score 0 · Answer 3 · answered Oct 07 '17 at 23:28

Probably the connection parameters are missing any specification of UTF-8.

Read Trouble with UTF-8 characters; what I see is not what I stored , especially the parts about "best practice", "debugging" and "question marks".

Debugging tip: सूरज, when correctly encoded in utf8 (or utf8mb4) is E0A4B8 E0A582 E0A4B0 E0A49C. Other Devanagari letters will look similar. If you are getting something different, I can help you decipher it.

(And, yes, use InnoDB, not MyISAM.)

how to properly store and display unicode in mysql

3 Answers3