1

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

3
  • I dunno, looks like a bug to me: dbfiddle.uk/MlcV8HFm
    – mustaccio
    Sep 22, 2023 at 19:08
  • @mustaccio so I'm not going crazy? its really a bug in MySQL? Sep 22, 2023 at 19:13
  • @mustaccio - Over time new COLLATIONs are added as Unicode adds/fixes the sorting rules. So this should be fixed by the user picking a better collation. If the latest collation fails to compare correctly, then file a MySQL bug.
    – Rick James
    Sep 23, 2023 at 19:34

1 Answer 1

2

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)

3
  • A more thorough answer: stackoverflow.com/a/77163335/1766831
    – Rick James
    Sep 23, 2023 at 15:46
  • okay, so what would I use since I also need these to not match: SELECT 'The Divine Comedy' = '๐•ฟ๐–๐–Š ๐•ฏ๐–Ž๐–›๐–Ž๐–“๐–Š ๐•ฎ๐–”๐–’๐–Š๐–‰๐–ž' COLLATE utf8mb4_0900_as_ci; - that shows 1 when I need it not to Sep 25, 2023 at 16:26
  • @DerekPollard - What is different about your example?
    – Rick James
    Sep 25, 2023 at 16:56

Your Answer

By clicking โ€œPost Your Answerโ€, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.