我有一个包含varchar值的表格,需要存储带有表情符号的文本值:
CREATE TABLE `my_table` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`value` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `value_idx` (`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
现在我需要在这个表上进行选择,以查找以前缀开头的所有值。选择必须是不区分大小写的,并且必须匹配表情符号。到目前为止,我找到了4个选项,它们都存在权衡:
I can use utf8mb4_unicode_ci collation and do selects like
select * from my_table where value like 'prefix%'
It will wind all values starting with prefix ignoring its characters case, but will not find anything if prefix contains emojis
I can set collection to utf8mb4_bin and my selects will find values if prefix contains emojis, but will be case sensitive
I can do
select * from my_table where LOWER(value) like 'prefix%'
and it will work case insensitively and with emojis, but will not use index
And finally I can save all values in lower case and use utf8mb4_bin collation, but saving in lower case is also the trade off
更新:我不是在存储表情符号时遇到问题,而是在使用"like"查询时保持不区分大小写的排序顺序时找不到它们。