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.

Answer 1

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.

Answer 2

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'