如何使用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天全站免登陸
亚洲AV无码国产精品