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)

ordHold down alt and enter 152964). You can see that the insertion failed.

use test;

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

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

QueryThere is no garbled code:

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!