如何处理引号和撇号以便在MySQL中进行字符串比较，使它们匹配(排序规则) [英] How to deal with quotes and apostrophes for string comparison in MySQL so they match (collation)

问题描述

MySQL使用归类进行字符串比较，因为某些字符应该匹配

MySQL uses collations to do string comparison because some characters should match

示例:

SELECT 'é' = 'e' COLLATE utf8_unicode_ci;
SELECT 'oe' = 'œ' COLLATE utf8_unicode_ci; 

都返回true

现在，如何对引号(')和撇号(')进行相同操作

Now, how can I do the same with quotes (') vs apostrophes (’)

这是不相同的字符，写它是"或"l'oiseau"(法文)时要使用的正确字符都是撇号.

This is not the same character, the proper character to use when writing "it’s" or "l’oiseau" (in french) are both the apostrophe.

事实是utf8_general_ci或utf8_unicode_ci都不对它们进行整理.

The fact is that neither utf8_general_ci or utf8_unicode_ci collate them.

简单的解决方案是将所有内容都用引号引起来，并在用户进行搜索时替换所有的撇号，但这是错误的.

The easy solution is to store everything in quotes and do a replace of all the apostrophes when a user does a search, but it’s wrong.

真正的解决方案是创建一个基于utf8_unicode_ci的自定义归类，并将两者都标记为等效，但是这需要编辑XML配置文件并重新启动数据库，但这并非总是可能的.

The real solution would be to create a custom collation based on utf8_unicode_ci and mark both as equivalent, but that requires to edit XML config files and to restart the database, which isn’t always possible.

你会怎么做?

推荐答案

自定义排序规则似乎是最合适的方法，但是如果不可能，则可以定制搜索以使用正则表达式.这并非完全理想，但在某些情况下可能会有用.至少它可以让您以正确的格式存储数据(而不必替换引号)，并且只需对搜索查询本身进行替换即可:

A custom collation seems to be the most appropriate, but if that is not possible, perhaps you could tailor your searches to use regular expressions. It's not exactly ideal, but may be of use in some situations. At least it allows you to store data in the correct format (without having to replace quotes), and just do the replacements on the search query itself:

INSERT INTO mytable VALUES
(1, 'Though this be madness, yet there is method in ''t'),
(2, 'Though this be madness, yet there is method in ’t'),
(3, 'There ’s daggers in men’s smiles'),
(4, 'There ’s daggers in men''s smiles');

SELECT * FROM mytable WHERE data REGEXP 'There [\'’]+s daggers in men[\'’]+s smiles';

+----+--------------------------------------+
| id | data                                 |
+----+--------------------------------------+
|  3 | There ’s daggers in men’s smiles     |
|  4 | There ’s daggers in men's smiles     |
+----+--------------------------------------+

SELECT * FROM mytable WHERE data REGEXP 'Though this be madness, yet there is method in [\'’]+t';

+----+-----------------------------------------------------+
| id | data                                                |
+----+-----------------------------------------------------+
|  1 | Though this be madness, yet there is method in 't   |
|  2 | Though this be madness, yet there is method in ’t   |
+----+-----------------------------------------------------+

这篇关于如何处理引号和撇号以便在MySQL中进行字符串比较，使它们匹配(排序规则)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！