My SQL statement is not comparing unicode emojis properly?

Question

fullEmoji is a property stored in discord.js ParsedEmoji object which should just be a literal emoji ''.

The ParsedEmoji is just an interface but I get that from calling parseEmoji() which is a private it's mainly for the discord custom emojis since if the ParsedEmoji.id is not valid I know it's either a unicode or not an emoji and then I just do a regex test with emoji-regex to see if it's a unicode emoji

const fullEmoji: string = parsedEmoji.id ? 
`<:${parsedEmoji.name}:${parsedEmoji.id}>` : parsedEmoji.name;

connPool.query<ResultSetHeader>(`
    DELETE FROM emoji_role_links WHERE messages_reactable_id = ${reactableMsg.id}
    AND emoji = '${fullEmoji}';
`)

When I run this query it ends up deleting all emoji-link rows and I'm assuming it's because the AND emoji = '${fullEmoji}' is not properly working.

The charset I am using is utf8mb4 and collation utf8mb4_0900_ai_ci

Listing the Rows below:

id, emoji, role_id, messages_reactable_id

'3', '', '1083419092192600086', '2'

'4', '', '1099145715982278696', '2'

SHOW CREATE TABLE:

Table, Create Table

CREATE TABLE emoji_role_links (
 id int unsigned NOT NULL AUTO_INCREMENT,
 emoji varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 role_id varchar(22) COLLATE utf8mb4_unicode_ci NOT NULL,
 messages_reactable_id int unsigned DEFAULT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY role_id_UNIQUE (role_id),
 KEY fk_messages_reactable_id_idx (messages_reactable_id),
 CONSTRAINT fk_messages_reactable_id FOREIGN KEY (messages_reactable_id) REFERENCES messages_reactable (id)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

So this is showing the collation to be utf8mb4_unicode_ci

I don't know what more to try I have tried a little researching and couldn't find much directly related to this I don't know what exact problem I have here since this is all in the query itself

one thing I would try if there is no way to get this to work is to escape the emoji's and unescaped them again later but I would have to rewrite a lot of code so I'm saving that as a last resort

Which also includes validating that the values you then use for executing that prepared statements are what they should be, e.g. id should be a number (presumably ParsedEmoji already validates, but why does it use class casing, is it actually a static class?). And finally: please put that table data in your post as text. Don't make it a link to an image, you can copy/paste or even hand copy two rows of data just fine =) — Mike 'Pomax' Kamermans, Apr 28 '23 at 18:05
please edit your question to show (as text, not an image) output of `show create table emoji_role_links` — ysth, Apr 28 '23 at 18:54
I copied the rows and pasted them instead. I will also use prepared statements when I get home I haven't used them yet but I have read that it's better. I've also reclarified ParsedEmoji which is just an interface — dev-syn, Apr 28 '23 at 18:57
the pasted rows are helpful, but please do add the show create table output. — ysth, Apr 28 '23 at 19:35
I have confirmed though by running my code that fullEmoji is the emoji literally for example what I'm trying to do is delete the emoji-link where the column value is '' emoji but once again when I check the table all the rows are then deleted so I know for sure it has to do with the `emoji = ''` — dev-syn, Apr 28 '23 at 21:24

score 1 · Accepted Answer · answered May 01 '23 at 16:28

SHOW VARIABLES LIKE 'coll%'; to see if the connection the test will be performed with. Note that the column is using utf8mb4_unicode_ci.

Note that 'ai_ci' treats those emogi as different, but the [vary old] 'unicode' collation treats them as equal.

mysql> SELECT '' = '' COLLATE utf8mb4_0900_ai_ci;
+--------------------------------------+
| '?' = '?' COLLATE utf8mb4_0900_ai_ci |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '' = '' COLLATE utf8mb4_unicode_ci;
+--------------------------------------+
| '?' = '?' COLLATE utf8mb4_unicode_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

unicode refers to Unicode standard 4.0; 0900 refers to 9.0.

When I get back to that part in my system I will make the changes necessary then thank you — dev-syn, May 03 '23 at 00:44

My SQL statement is not comparing unicode emojis properly?

id, emoji, role_id, messages_reactable_id

SHOW CREATE TABLE:

Table, Create Table

1 Answers1