Not a duplicate, but still getting Integrity constraint violation: 1062 Duplicate entry

Question

I'm using MySQL (ver. 8.0.32)

I have a table whose schema looks like this:

CREATE TABLE `players` (
  `id` bigint UNSIGNED NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

With the following indexes:

ALTER TABLE `players`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `players_name_unique` (`name`),
ALTER TABLE `players` ADD FULLTEXT KEY `players_name_fulltext` (`name`);

There's already the following rows:

id	name
1	🅲🅷🅰🆁🅾🅽
2	jim

When I go to run the following query:

UPDATE `players` SET `name` = '𝓜𝓤𝓡𝓓𝓐𝓗' WHERE id = 2

I get the following error:

Integrity constraint violation: 1062 Duplicate entry '??????' for key 'players.players_name_unique'

I'm really stumped, I'm not sure how to remedy this? Am I doing something wrong? Should I be using a different charset?

Thank you for your help

Answer 1

For 𝓜𝓤𝓡𝓓𝓐𝓗 or 🅲🅷🅰🆁🅾🅽, you need CHARACTER SET utf8mb4.

The antique _general_ci collation does not take much into consideration. Change to utf8mb4_0900_as_ci.

SELECT '𝓜𝓤𝓡𝓓𝓐𝓗' = '🅲🅷🅰🆁🅾🅽' COLLATE utf8mb4_general_ci;  ==> 1
SELECT '𝓜𝓤𝓡𝓓𝓐𝓗' = '🅲🅷🅰🆁🅾🅽' COLLATE utf8mb4_0900_as_ci;  ==> 0

1 = true (a match)