CODEWITHSUNDEEP
mysqlsqlmysql-workbenchcollation
Dark
Dark

Reputation: 81

MYSQL Selecting Emoji in Where Clause

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 '🦩' = '🕊';

Upvotes: 1

Views: 604

Answers (1)

D-Shih
D-Shih

Reputation: 46219

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:

db<>fiddle here

Upvotes: 2

Related Questions