Como posso pesquisar por emoji no MySQL usando utf8mb4?

Question

Dec 14, 2016, 05:44 PM

Como posso pesquisar por emoji no MySQL usando utf8mb4?

Por favor, ajude-me a entender como caracteres multibyte como emoji são tratados nos campos utf8mb4 do MySQL.

Veja abaixo um SQL de teste simples para ilustrar os desafios.

/* 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 )

Estou interessado em aprender o que faz com que oFAILs acima e como eu posso contornar isso.

Especificamente:

Por que seleciona para um caractere multibyte retornar resultados paraqualquer caractere multibyte?Como posso configurar um índice para manipular caracteres multibyte em vez de??Você pode recomendar alterações no segundoCREATE TABLE (aquele com uma chave exclusiva) acima, de tal forma que faça com que todas as consultas de teste retornem com êxito?