Shulou(Shulou.com)06/01 Report--

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:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.