CODEWITHSUNDEEP
mysqlsqlutf-8character-encoding
Devashish Dixit
Devashish Dixit

Reputation: 1193

Chinese and Japanese characters not working with mysql

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?

Upvotes: 3

Views: 3121

Answers (2)

NicoNing
NicoNing

Reputation: 3242

If mysql>=5.5.3, use utf8mb4 .

  1. Alter origin table
ALTER TABLE $tablename
  CONVERT TO CHARACTER SET utf8mb4 
  COLLATE utf8mb4_general_ci
  1. Create new table 
CREATE TABLE $tablename (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  1. Modify Column
ALTER TABLE $tablename
    MODIFY $col1
    VARCHAR(191)
    CHARACTER SET utf8mb4;

refer: Mysql DOC: Column Character Set Conversion

Upvotes: 1

Mike Deluca
Mike Deluca

Reputation: 1210

Try the following to change the character set: SET NAMES 'big5';

Upvotes: -2

Related Questions