MYSQL Selecting Emoji in Where Clause

Question

I have a weird issue in MYSQL Table

My table have column with varchar utf8mb4 charset and utf8mb_unicode_ci collation which has list of emoji and some other strings.

Issue is that whenever i select a single emoji (Eg: select * from tbl_codes where where code = '💎'), I get other emojis also ( 🦩,🕊,͏💠)

Also below query returns always 1

select '🦩' = '🕊';

Answer 1

You can try to use COLLATE utf8mb4_unicode_520_ci

 SELECT '?' = '?' COLLATE utf8mb4_unicode_520_ci;
 
 SELECT '?' = '?' COLLATE utf8mb4_unicode_520_ci;

| '?' = '?' COLLATE utf8mb4_unicode_520_ci |
| ---------------------------------------: |
|                                        0 |

| '?' = '?' COLLATE utf8mb4_unicode_520_ci |
| ---------------------------------------: |
|                                        1 |

From the document of Mysql 10.3.1 Collation Naming Conventions

Collation names for Unicode character sets may include a version number to indicate the version of the Unicode Collation Algorithm (UCA) on which the collation is based. UCA-based collations without a version number in the name use the version-4.0.0 UCA weight keys. For example:

• utf8mb4_unicode_520_ci is based on UCA 5.2.0 weight keys (http://www.unicode.org/Public/UCA/5.2.0/allkeys.txt).
• utf8mb4_unicode_ci (with no version named) is based on UCA 4.0.0 weight keys (http://www.unicode.org/Public/UCA/4.0.0/allkeys-4.0.0.txt).

db<>fiddle here