How to deal with the failure of inserting rare words in MySQL

Recently, the business side reported that the insertion of individual user information failed, and the error message was similar to "Incorrect string value:"\ xF0\ xA5. "look at this prompt because the character set does not support a rare word.

The following is a scenario that is reproduced in a virtual machine:

Step1, 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'

Step2, insert obscure words (simulated with sqlyog):

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

2. Insert rare words (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 type 152964). You can see that the insertion 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 an online large table, you can use pt-osc to handle it. The command is as follows:

Pt-online-schema-change-uroot-h localhost-- alter= "change real_name real_name varchar CHARACTER SET utf8mb4 DEFAULT''COMMENT' name'" 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 CHARACTER SET utf8mb4 DEFAULT''COMMENT' name'" D=test, t=t1-- no-check-replication-filters-- alter-foreign-keys-method=auto-- recursion-method=none-- quiet-- charset=utf8mb4-- execute

Step4, re-insert experiment:

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: