mysqlbinaryunique-indexcollate

mySQL: update Record / duplicate entry / collate / binary

i'm having a field called url (varchar 255) which is set to unique and utf8mb4_general_ci as collation.

when trying to update a record to set this url: https://en.wikipedia.org/wiki/Léa_(film)

i am getting a duplicate error because another record exsists having https://en.wikipedia.org/wiki/Lea_(film) as URL (without accent).

i tried to add COLLATE utf8_bin which gave me an error:

COLLATION 'utf8_bin' is not valid for CHARACTER SET 'latin1'

i also tried converting the URL by using (CAST 'https://en.wikipedia.org/wiki/Léa_(film)' AS BINARY) but didn't help either.

is there a way or is the only solution changing the field collation to utf8_bin?

thanks

Solution

  • when trying to update a record to set this url: https://en.wikipedia.org/wiki/Léa_(film)

    i am getting a duplicate error because another record exsists having https://en.wikipedia.org/wiki/Lea_(film) as URL (without accent).

    If such values must be NOT treated as duplicates then you must execute ALTER TABLE for to alter the column's settings and set according collation to it (for example, utf8mb4_0900_as_ci or utf8mb4_bin - see SHOW COLLATION LIKE 'utf8mb4_%'; and select proper collation).