Storing Multilingual/unicode characters(thai,hindi,philliphine) in a MYSQL database

Question

I have a MySQL DB. I created a table with below definition which supports multiple languages (Hindi, Thai, etc.).

CREATE TABLE test_multi_lang
(
   language_name varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci
)

INSERT INTO test_multi_lang
(language_name )
VALUES
('ตัวอย่าง')

INSERT INTO test_multi_lang
(language_name )
VALUES
('नमूना')

But when I select the data. It is showing all the data as ??????.

SELECT * FROM test_multi_lang

Sample Image

How can I solve this problem?

Thanks in advance.

How is it "showing the data"? Are you in an IDE or database UI tool? A command line window? Something else? If whatever ultimately displays the data is not set up for UTF-8 you'll get garbage out even if the data is correctly stored in the DB. — Jim Garrison, Jun 05 '15 at 07:21
I am selecting the data using TOAD for mysql. how do i check if the data correctly set in table or not. @JimGarrison — Mote Rahul, Jun 05 '15 at 07:23
@MoteRahul I tried your code on my machine, it is working correctly. — user4035, Jun 05 '15 at 07:24
@user4035. can you send me a screen shot how it is showing. what tool you are using to run the query. — Mote Rahul, Jun 05 '15 at 07:25
I am using Toad for MYSQL 7.3.1.290 version. when I executed through console everything looks good. but when i executes in TOAD, It is showing as ?????? @user4035. — Chakradhar, Jun 06 '15 at 10:33
@Chakradhar Check the settings for TOAD, if there is encoding. — user4035, Jun 06 '15 at 11:24
@user4035. Yes, there is setting Editor->Character Encoding->UTF-8. I enabled that too. But no luck. The result is same. — Chakradhar, Jun 06 '15 at 14:35

score 0 · Answer 1 · answered Jun 05 '15 at 07:27

It's not MySQL fault: the data is not being correctly output in your client program. Here is how it looks on my Linux Slackware machine. Native MySQL console:

mysql> CREATE TABLE test_multi_lang
(
   language_name varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci
);

Query OK, 0 rows affected (0.35 sec)

mysql> insert into test_multi_lang
(language_name )
values
('ตัวอย่าง');


mysql> SELECT * FROM test_multi_lang;
+--------------------------+
| language_name            |
+--------------------------+
| ตัวอย่าง                 |
+--------------------------+

mysql>  SET NAMES latin1;

mysql> SELECT * FROM test_multi_lang;
+---------------+
| language_name |
+---------------+
| ????????      |
+---------------+

mysql>  SET NAMES utf8;

mysql> SELECT * FROM test_multi_lang;
+--------------------------+
| language_name            |
+--------------------------+
| ตัวอย่าง                 |
+--------------------------+

You see: as soon as we used SET NAMES latin1, we got question marks. Try to connect to MySQL, using console application, and use SET NAMES utf8.

As the manual says:

SET NAMES indicates what character set the client will use to send SQL statements to the server... It also specifies the character set that the server should use for sending results back to the client.

score 0 · Answer 2 · answered Jun 05 '15 at 07:27

You can try to set the character set of the returned result before running the SELECT query:

SET NAMES 'utf8'

and

SET character_set_results = 'utf8', character_set_client = 'utf8', character_set_connection = 'utf8', character_set_database = 'utf8', character_set_server = 'utf8'

Storing Multilingual/unicode characters(thai,hindi,philliphine) in a MYSQL database

2 Answers2