How to handle the failure of inserting rare characters into MySQL (picture)

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.... This prompt should be Character setIt is caused by a rare word not being supported. Friends who need it can refer to it

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... .. "Looking at this prompt, it should be caused by the character set not supporting a certain rare character.

The following is a scenario 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 '姓名',
 `nick` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '昵称',
 PRIMARY KEY (`id`)
) ENGINE=InnoDBAUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员卡';

step2. Insert rare characters (use sqlyog simulation) :

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

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

##We try to insert the king (in w

ordHold down alt and enter 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 '姓名' ;

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

step4. Insert the experiment again:

1. Set the character set first:

2. Insert it again and you can see The insertion was successful.

In the command line

QueryThere is no garbled code:

That’s it. Yes, you can refer to it.

The above is the detailed content of How to handle the failure of inserting rare characters into MySQL (picture). For more information, please follow other related articles on the PHP Chinese website!