How to handle the failure of inserting rare characters in MySQL (Incorrect string value)

Recently, the business side reported that some user information failed to be inserted, and the error message was similar to "Incorrect string value:"\xF0\xA5.....". Judging from this message, it should be caused by the character set not supporting a certain uncommon character.

Here is the scenario reproduced in the virtual machine:

Step 1, simulate the original table structure character set environment:

use test;
CREATE TABLE `t1` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `real_name` varchar(255) CHARACTER SET utf8 DEFAULT '' COMMENT 'Name',
 `nick` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT 'Nickname',
 PRIMARY KEY (`id`)
) ENGINE=InnoDBAUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Membership Card';

Step 2, insert rare words (using sqlyog simulation):

1. First simulate the online environment and set the character set:

2. Insert rare characters (for rare characters, please refer to: http://www.qqxiuzi.cn/zh/hanzi-unicode-bianma.php?zfj=kzb&ks=24E20&js=257E3)

We try to insert Wang (hold down ALT in Word and enter 152964). You can see that the insert failed.

step3. Modify the character set of real_name:

use test;

alter table t1 change real_name real_name varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'Name';

If it is a large online table, you can use pt-osc to process it. The command is as follows:

pt-online-schema-change -uroot -h localhost --alter=" change real_name real_name varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '姓名' " D=test, t=t1 --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --charset=utf8mb4 --dry-run

pt-online-schema-change -uroot -h localhost --alter=" change real_name real_name varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '姓名' " D=test, t=t1 --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --charset=utf8mb4 --execute

Step 4, insert the experiment again:

1. Set the character set first:

2. Insert again and you can see that the insertion is successful.

There is no garbled code in the command line query:

That’s it, you can refer to it.