What to do if MySQL fails to insert rare characters

The business side reported that some user information failed to be inserted, and the error message was similar to "Incorrect string value:"\xF0\xA5...." This prompt should be caused by the character set not supporting a rare character. So what? How to solve it? This article will share with you how to solve the problem of MySQL rare character insertion failure.

The following is the scenario that is reproduced in the 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: http://www.qqxiuzi.cn/zh/hanzi-unicode-bianma.php?zfj=kzb&ks=24E20&js=257E3)

We try to insert Wang (hold alt in word and enter 152964). You can see that the insertion fails.

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 again and you can see that the insertion is successful.

There is no garbled code when querying in the command line:

##This is complete, you can refer to it

Related recommendations:

Development example of php supporting the conversion of Chinese characters into Pinyin

Recommended 10 articles about rare characters

PHP Chinese character to Pinyin conversion class that supports rare characters and automatically recognizes UTF-8 encoding_PHP tutorial

The above is the detailed content of What to do if MySQL fails to insert rare characters. For more information, please follow other related articles on the PHP Chinese website!