唯一列上的重复Unicode输入错误-MySQL [英] Duplicate unicode entry error on the unique column - mysql

问题描述

我有一个包含两列的表Labels:

I have a table Labels with 2 columns:

+-------------+--------------+-------------+
| Column Name |     Type     |     Key     |
+-------------+--------------+-------------+
| id          | integer      | primary key |
| label       | varchar(255) | unique      |
+-------------+--------------+-------------+

在此表中，我已经有如下记录: id: 1, label: 'café'

In this table, I already have a record as the following: id: 1, label: 'café'

现在我要添加更多记录，如下所示: id: auto, label: 'cafe'

And now I want to add more record as the following: id: auto, label: 'cafe'

但是当我尝试插入时，出现重复的错误

But when I try to insert, duplicate error appear

(1062, "Duplicate entry 'cafe' for key 'label_2'") [SQL: u'INSERT INTO vocabulary (label) VALUES (%s)'] [parameters: (u'cafe',)]

在这种情况下，你们能帮助我吗? 有关我的数据库的更多信息:字符集:utf8，整理:utf8mb4_unicode_ci

Could you guys help me in that case? Some more information about my database: character set: utf8, collate: utf8mb4_unicode_ci

更新:创建表

CREATE TABLE `labels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `label` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `label_2` (`label`),
  KEY `label` (`label`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

推荐答案

就label是唯一键而言，您将无法在该列中插入重复值. 由于要区分café和cafe，然后需要使用utf8_bin collat​​ion. 请尝试以下查询.

As far as label is unique key,you are not able to insert duplicate value in that column. As you want to distinguish between café and cafe and then you need to use utf8_bin collation . Try below query.

ALTER TABLE labels CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE labels CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

希望这会有所帮助.

这篇关于唯一列上的重复Unicode输入错误-MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！