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: 'xE4xB8x8AxE6xB5xB7...' for column 'code' at row 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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 nameTableG

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 nameTableG

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?


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x