Why is MySQL registering acuñar as a duplicate entry of acunar?

Question

I have a verbs table in my MySQL database with the following columns:

id smallint          UN AI PK
title varchar(25)

I have a script that tries to insert a new entry for the verb 'acuñar'. In the table, there already is an entry for a similarly spelled verb 'acunar'.

Despite my MySQL DB storing UTF8 characters successfully, when I run the following Python script:

import mysql.connector
import os
import io

db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="12345678",
  database="polly_es",
  use_unicode=True,
  charset="utf8",
)

cursor = db.cursor()

sql = "INSERT INTO verb (title) VALUES ('acuñar')"

cursor.execute(sql)

I get the following error:

mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry 'acuñar' for key 'verb.title'

Yes, I am certain that there is no 'acuñar' entry already in the table.

And yes, my schema is encoded.

CREATE SCHEMA `polly_es` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Answer 1

Because your collation is using utf8mb4_0900_ai_ci where the AI stands for

AI refers to accent insensitivity. That is, there is no difference between e, è, é, ê and ë when sorting or comparing.

So you could try utf8mb4_0900_as_ci