如何使用utf8mb4在MySQL中通过emoji进行搜索? [英] How can I search by emoji in MySQL using utf8mb4?

查看:0
本文介绍了如何使用utf8mb4在MySQL中通过emoji进行搜索?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

请帮助我了解MySQL utf8mb4字段是如何处理像emoji这样的多字节字符的。

有关说明挑战的简单测试SQL,请参见下文。

/* Clear Previous Test */
DROP TABLE IF EXISTS `emoji_test`;
DROP TABLE IF EXISTS `emoji_test_with_unique_key`;

/* Build Schema */
CREATE TABLE `emoji_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `string` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `emoji_test_with_unique_key` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `string` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `status` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_string_status` (`string`,`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/* INSERT data */
# Expected Result is successful insert for each of these.
# However some fail. See comments.
INSERT INTO emoji_test (`string`, `status`) VALUES ('🌶', 1);                   # SUCCESS
INSERT INTO emoji_test (`string`, `status`) VALUES ('🌮', 1);                   # SUCCESS
INSERT INTO emoji_test (`string`, `status`) VALUES ('🌮🌶', 1);                 # SUCCESS
INSERT INTO emoji_test (`string`, `status`) VALUES ('🌶🌮', 1);                 # SUCCESS
INSERT INTO emoji_test_with_unique_key (`string`, `status`) VALUES ('🌶', 1);   # SUCCESS
INSERT INTO emoji_test_with_unique_key (`string`, `status`) VALUES ('🌮', 1);   # FAIL: Duplicate entry '?-1' for key 'idx_string_status'
INSERT INTO emoji_test_with_unique_key (`string`, `status`) VALUES ('🌮🌶', 1); # SUCCESS
INSERT INTO emoji_test_with_unique_key (`string`, `status`) VALUES ('🌶🌮', 1); # FAIL: Duplicate entry '??-1' for key 'idx_string_status'

/* Test data */

    /* Simple Table */
SELECT * FROM emoji_test WHERE `string` IN ('🌶','🌮','🌮🌶','🌶🌮'); # SUCCESS (all 4 are found)
SELECT * FROM emoji_test WHERE `string` IN ('🌶');                     # FAIL: Returns both 🌶 and 🌮
SELECT * FROM emoji_test WHERE `string` IN ('🌮');                     # FAIL: Returns both 🌶 and 🌮
SELECT * FROM emoji_test;                                              # SUCCESS (all 4 are found)

    /* Table with Unique Key */
SELECT * FROM emoji_test_with_unique_key WHERE `string` IN ('🌶','🌮','🌮🌶','🌶🌮'); # FAIL: Only 2 are found (due to insert errors above)
SELECT * FROM emoji_test_with_unique_key WHERE `string` IN ('🌶');                     # SUCCESS
SELECT * FROM emoji_test_with_unique_key WHERE `string` IN ('🌮');                     # FAIL: 🌶 found instead of 🌮
SELECT * FROM emoji_test_with_unique_key;                                              # FAIL: Only 2 records found (🌶 and 🌮🌶)

我有兴趣了解上述FAIL%s的原因以及如何解决此问题。

具体:

  1. 为什么选择一个多字节字符会返回任何多字节字符的结果?
  2. 如何将索引配置为处理多字节字符,而不是?
  3. 您能否建议对上面的第二个CREATE TABLE(具有唯一键的那个)进行更改,使所有测试查询都能成功返回?

推荐答案

您对列使用utf8mb4_unicode_ci,因此检查不区分大小写。如果您改用utf8mb4_bin,则表情符号🌮和🌶将被正确标识为不同的字母。

使用WEIGHT_STRING可以获取用于对输入字符串进行排序和比较的值。

如果您写:

SELECT
  WEIGHT_STRING ('🌮' COLLATE 'utf8mb4_unicode_ci'),
  WEIGHT_STRING ('🌶' COLLATE 'utf8mb4_unicode_ci')

然后您可以看到两者都是0xfffd。在Unicode Character Sets中他们说:

对于常规归类中的补充字符,权重为0xfffd替换字符的权重。

如果您写:

SELECT 
  WEIGHT_STRING('🌮' COLLATE 'utf8mb4_bin'),
  WEIGHT_STRING('🌶' COLLATE 'utf8mb4_bin')

您将获得它们的Unicode值0x01f32e0x01f336

对于ÄÁA等其他字母,如果使用utf8mb4_unicode_ci是相等的,差异可以在:

中看到
SELECT
  WEIGHT_STRING ('Ä' COLLATE 'utf8mb4_unicode_ci'),
  WEIGHT_STRING ('A' COLLATE 'utf8mb4_unicode_ci')

这些映射到权重0x0E33

Ä: 00C4  ; [.0E33.0020.0008.0041][.0000.0047.0002.0308] # LATIN CAPITAL LETTER A WITH DIAERESIS; QQCM
A: 0041  ; [.0E33.0020.0008.0041] # LATIN CAPITAL LETTER A

根据:Difference between utf8mb4_unicode_ci and utf8mb4_unicode_520_ci collations in MariaDB/MySQL?utf8mb4_unicode_ci使用的权重基于UCA 4.0.0,因为表情符号不在其中,所以映射的权重为0xfffd

如果您需要对常规字母和emoji进行不区分大小写的比较和排序,则可以使用utf8mb4_unicode_520_ci

解决此问题
SELECT
  WEIGHT_STRING('🌮' COLLATE 'utf8mb4_unicode_520_ci'),
  WEIGHT_STRING('🌶' COLLATE 'utf8mb4_unicode_520_ci')

这些表情符号0xfbc3f32e0xfbc3f336也将获得不同的权重。

这篇关于如何使用utf8mb4在MySQL中通过emoji进行搜索?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆