Duplicate unicode entry error on the unique column

Question

This question already has an answer here:

• MySQL distinction between e and é (e acute) - UNIQUE index 4 answers

I have a table Labels with 2 columns:

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

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

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',)]

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

UPDATE: create table

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;

Answer 1

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;

Hope this will helps.