How to solve the failure of inserting rare words in MySQL

This article will explain in detail how to solve the failure of inserting rare words in MySQL. The editor thinks it is very practical, so I share it with you as a reference. I hope you can get something after reading this article.

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 'CHARACTER SET utf8 DEFAULT' 'COMMENT' name', `nick` varchar '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-runpt-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:

About MySQL rare word insertion failure to solve how to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.