Mysql inserts pinyin characters through INSERT IGNORE INTO. How to solve the problem?

1. Preparation work

Prepare a batch import keyword excel, which contains 2 keywords

1.pokemon

2.pokémon

Note: One of these two keywords is an ordinary e, and the other is a syllable é

Prepare database table sql script

-- 导入关键词表
CREATE TABLE `keyword_lexicon` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '搜索关键词',
  PRIMARY KEY ("id"),
  UNIQUE KEY "idx_keyword" ("keyword") USING BTREE COMMENT '关键词'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='导入关键词表';

2. Scene recurrence

Simply write an import interface, the code is not attached. It should be noted that the INSERT IGNORE INTO method is used to import sql. If the keyword already exists in the table, it will not be written to the table. (The keyword field is set as a unique index)

The import sql example is as follows

INSERT IGNORE INTO keyword_lexicon (`keyword`) VALUES ('pokemon'),('pokémon')

After adjusting the interface to complete the import, check the database table and find that there is only one piece of Pokemon data.

3. Solve the problem

Looking at the table structure, we found that the sorting rule of the keyword field is

• utf8mb4_general_ci

##When this sorting rule recognizes characters with syllables such as é, it will be recognized as e, resulting in 2 Each keyword will only have one record after being imported through INSERT IGNORE INTO. Just change the sorting rule to

• ##utf8mb4_bin

Then re-import. After viewing the results, you can find that both pieces of data have been inserted into the table, and the problem is solved.

##Summary

utf8mb4_bin is case-sensitive, and also distinguishes characters such as e and é

• utf8_genera_ci is not case-sensitive, nor does it distinguish between characters such as e and é

• Note: utf8_general_cs is case-sensitive, but does not distinguish between e and é

If you need to distinguish characters with syllables and do not want to be case sensitive, you can use the LOWER() function when querying the corresponding fields in SQL

The above is the detailed content of Mysql inserts pinyin characters through INSERT IGNORE INTO. How to solve the problem?. For more information, please follow other related articles on the PHP Chinese website!