Storing Chinese Characters in mysql

Im trying to store chinese characters to mysql database but am unable to.

Changed characterset for the table to utf8 and utf8mb4

Table looks something like this


id  bigint(20)  NO  PRI     auto_increment
version bigint(20)  YES         
country varchar(255)    YES         
englishName varchar(255)    YES
chineseName varchar(255)    YES

Trying to alter the chineseName cell to chinese using the following query.

UPDATE nametable` SET `chineseName` = '上海' WHERE (`id` = '1');

Still get and error as follows. ERROR 1366: 1366: Incorrect string value: '\xE4\xB8\x8A\xE6\xB5\xB7...' for column 'code' at row 1

Solution

You answered in comments that this is what you did to change the character set:

ALTER TABLE nameTable CHARACTER SET = utf8mb4 , COLLATE = utf8mb4_bin

This only changes the table's default character set and collation. It doesn't change the existing columns, it only applies to future columns you add to the table.

Here's what the table looks like after this change:

mysql> SHOW CREATE TABLE nameTable\G

CREATE TABLE `nameTable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) DEFAULT NULL,
  `country` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `englishName` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `chinesename` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

You can see that the table's default (on the last line) is changed, but each column still has the old character set.

You can convert existing columns one by one, or else you can convert them all at once this way:

mysql> ALTER TABLE nameTable CONVERT TO CHARACTER SET utf8mb4, COLLATE utf8mb4_bin;

mysql> SHOW CREATE TABLE nameTable\G

CREATE TABLE `nameTable` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` bigint(20) DEFAULT NULL,
  `country` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `englishName` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  `chinesename` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Now all the columns have been converted.

MySQL has some strange rules about when each column shows its character set. See How does MySQL determine when to show explicit character set & collation values?