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 at 19:08
  • @mustaccio so I'm not going crazy? its really a bug in MySQL? Sep 22 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 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 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 at 16:26
  • @DerekPollard - What is different about your example?
    – Rick James
    Sep 25 at 16:56

Your Answer

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

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