MySQL语句无法正确比较Unicode表情符号的问题排查

问题场景 #

使用discord.js开发时，通过parseEmoji()获取ParsedEmoji实例，判断为Unicode表情后，将其字面符号存入MySQL表emoji_role_links（字符集utf8mb4，排序规则utf8mb4_unicode_ci）。执行DELETE语句时，同messages_reactable_id下的所有表情关联行被误删，推测是AND emoji = '${fullEmoji}'的匹配逻辑失效。

相关代码：

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}';
`)

表结构：

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

表中数据：

核心原因 #

1. SQL字符串拼接的编码问题：直接将Unicode表情拼接进SQL字符串时，Node.js的字符串编码与MySQL驱动的字符处理可能存在差异，导致实际传入的表情符号与数据库存储的不匹配，使得emoji = 'xxx'条件失效，最终只保留messages_reactable_id的过滤条件，误删所有关联行。
2. 排序规则不匹配：应用端使用utf8mb4_0900_ai_ci排序规则，但表实际使用utf8mb4_unicode_ci，旧排序规则对部分Unicode表情的匹配逻辑存在偏差，可能导致字符比较失效。

解决方案 #

1. 使用参数化查询（最优方案，无需大量修改代码） #

参数化查询由MySQL驱动自动处理字符编码与转义，彻底避免字符串拼接带来的问题。修改代码如下：

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

// 使用?作为参数占位符，将参数放入数组传递
connPool.query<ResultSetHeader>(
    'DELETE FROM emoji_role_links WHERE messages_reactable_id = ? AND emoji = ?;',
    [reactableMsg.id, fullEmoji]
)

这种方式不仅解决表情匹配问题，还杜绝了SQL注入风险，是数据库操作的标准实践。

2. 统一排序规则为utf8mb4_0900_ai_ci #

修改表的排序规则，确保与应用端一致，避免字符比较偏差：

ALTER TABLE emoji_role_links 
MODIFY COLUMN emoji varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
MODIFY COLUMN role_id varchar(22) COLLATE utf8mb4_0900_ai_ci NOT NULL,
COLLATE=utf8mb4_0900_ai_ci;

修改后，Unicode表情的比较逻辑会更准确，减少匹配失效的概率。

3. 验证ParsedEmoji的正确性 #

确认parsedEmoji.name确实是正确的Unicode字面表情符号，可通过打印日志或正则二次验证：

import emojiRegex from 'emoji-regex';
const regex = emojiRegex();

if (!regex.test(parsedEmoji.name)) {
    console.error('Invalid Unicode emoji:', parsedEmoji.name);
    // 处理异常情况
}

确保传入数据库的表情符号格式正确。

验证步骤 #

1. 执行参数化查询的修改后，测试删除指定表情的关联行，检查是否仅目标行被删除。
2. 若修改排序规则，需重新插入测试数据，再执行删除操作验证匹配逻辑。

内容的提问来源于stack exchange，提问作者dev-syn