Cannot insert chinese in mysql database through utf8 encoding ( Warning 1336 incorrect string value)

Question

I cannot insert Chinese in the table. I have already config the charset and collation as utf8 and utf8_general_ci. The database and table is created as utf8.

Also, I have tried charset as utf8mb4 and collation as utf8_unicode_ci, utf8mb4_general_ci, utf8mb4_unicode_ci. All of them point to same error : warning 1366 Incorrect String value.

Big5 and GBK encoding work in this case but I would like to use utf8, as simplified, traditional chinese will be allow to insert.

some of the code in mysql:

    create database db1 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

    create table table1 (
    lastName varchar(255),
    firstName varchar(255)
    ) ENGINE = MYISAM CHARSET=utf8 COLLATE utf8_general_ci;

    INSERT INTO table1 VALUES ('陳','小文');

  Warning | 1366 | Incorrect string value: '\xB3\xAF' for column 'lastName' at r
w 1       |
 Warning | 1366 | Incorrect string value: '\xA4p\xA4\xE5' for column 'firstName
 at row 1 |

configuration in my.ini:

[client]
default-character-set=utf8
[mysqld]
default-storage-engine=MyISAM
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'
character-set-server=utf8
skip-character-set-client-handshake=1
[mysql]
default-character-set=utf8

Using Mysql 5.6.8 version.

I suspect that MySQL is properly configured to use UTF-8 but your app is not. — Álvaro González
I had look through about the similar questions and still cannot solve in this case. Can you figure out where u suspected about the configuration? — kris ho
I'll use a metaphore. You want to pay the bill in a London restaurant and tell the waiter: "here you are a 100 euro note". But he refuses it: "I'm sorry, we can only accept British pounds". So you hand him the same 100 euro note and say: "then, here you are a 100 pound note". — Álvaro González
In the configuration of above, you can see the client ( the platform where I insert the data : cmd ) is set as utf8, and also the database. How do I still cannot insert Chinese when both of them are configured in same? — kris ho
Sorry I didn't explain myself properly. If you configure MySQL to assume UTF-8 you have to feed it with UTF-8. If your application is using anything else (e.g. Big5) you have to declare you're using that other encoding so MySQL libraries have the chance of converting data back and forth. Just saying "use UTF-8" is not a magic fix for all encoding issues ever. I'm sure your server-side settings are fine. — Álvaro González

A.Aleem11 A.Aleem11 · Accepted Answer · 2017-03-09T12:56:04

CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci set this collation on three places:

1- In database collation under operation or setting tab you can find collation dropbox select "utf8mb4_general_ci" from there.

2- In table again under operation tab you need to change collation value from dropdown for same "utf8mb4_general_ci".

3- Don't forget now to change the collation of your column that hold your data click on structure tab of your table edit the columns that need to be hold Chinese data and change collation for each as same: "utf8mb4_general_ci".

Try to place chinese character directly from phpmyadmin if you can successfully insert chinese character that's means you're done with database then try with server or from your website. If doesn't work from wesbite, Then May you need to add utf 8 meta tag on website script.

Cannot insert chinese in mysql database through utf8 encoding ( Warning 1336 incorrect string value)

1 Answers