Chinese and Japanese characters not working with mysql

Question

I have a table named CHINESE which has only one column NAME.
The output of SHOW VARIABLES LIKE 'char%' is:

+--------------------------+--------------------------------------------------------+
| Variable_name            | Value                                                  |
+--------------------------+--------------------------------------------------------+
| character_set_client     | utf8                                                   |
| character_set_connection | utf8                                                   |
| character_set_database   | latin1                                                 |
| character_set_filesystem | binary                                                 |
| character_set_results    | utf8                                                   |
| character_set_server     | latin1                                                 |
| character_set_system     | utf8                                                   |
| character_sets_dir       | /usr/local/mysql-5.1.73-osx10.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------+

When I run this query: INSERT INTO CHINESE VALUES ('你好'), the values get inserted.
But, when I try to execute this query: SELECT * FROM CHINESE, the result is:

+------+
| NAME |
+------+
| ??   |
+------+

The result of SELECT HEX(NAME) FROM CHINESE is:

+-----------+
| HEX(NAME) |
+-----------+
| 3F3F      |
+-----------+

Where am I making mistake?

You might have data in your database that's actually UTF-8 but the server is incorrectly assuming it's Latin1. Does setting your connection to Latin1 make it appear correctly? Since Latin1 is an 8-bit character set, it can have UTF-8 data stored in it, though it will look mangled when you try and display it as actual Latin1 characters. — tadman
When I insert values in database, the actual values aren't persisted, instead, '?' character is persisted. When I switch to 'latin1', and execute the select statement, the result is still the same. — Devashish Dixit
But, when I insert values while in 'latin1', the output is fine. — Devashish Dixit
You should try switching the server, connection, everything to utf8, make a new database and test table, insert there, and see if it works. I think you've got UTF-8 data but the server's doing conversion as if it was Latin1. — tadman

NicoNing NicoNing · Accepted Answer · 2019-05-17T03:02:31

If mysql>=5.5.3, use utf8mb4 .

Alter origin table

ALTER TABLE $tablename
  CONVERT TO CHARACTER SET utf8mb4 
  COLLATE utf8mb4_general_ci

Create new table

CREATE TABLE $tablename (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Modify Column

ALTER TABLE $tablename
    MODIFY $col1
    VARCHAR(191)
    CHARACTER SET utf8mb4;

refer: Mysql DOC: Column Character Set Conversion

Chinese and Japanese characters not working with mysql

2 Answers