MySQL不区分大小写和点号的敏感搜索

Question

问题: 我正在尝试实现一个搜索算法，即使提供了点字符也会显示结果。换句话说：SELECT 'über' = 'uber'或SELECT 'mas' = 'maş'这些结果将返回true。这适用于以下数组中的每个字符：

$arr = array('ş' => 's', 'ç' => 'c', 'ö' => 'o', 'ü' => 'u' and so on ...);

我心中的解决方案：除了原有的列，我可以再添加一列来存储英文名称。因此，在将“über”存储到数据库之前，我将在php中将其转换为“uber”，然后将“über”（作为原始数据）和“uber”（作为可搜索的数据）都存储到数据库中。

但是，尽管我已经全天搜索了这个问题，我仍然相信应该有更简单和更清晰的方法来完成这项任务，因为这意味着在数据库中存储相同的数据两次（或多次）。那么，各位，你认为解决方案是唯一的方式，还是你知道更好的方法？

编辑

对于重音符号不敏感，我已经看到了SO上的帖子，它们可以工作，但由于我还要考虑点字符，所以必须询问这个问题。

编辑2

出于某些原因，我不能完全发布表结构和代码，但我将提供一个近似的示例。

myusers | CREATE TABLE `myusers` (
id int auto_increment not null primary key,
email varchar(100) COLLATE latin1_general_ci not null,
fullname varchar(75) COLLATE latin1_general_ci not null)
PRIMARY KEY('id')
) ENGINE=MyISAM AUTO_INCREMENET=2 DEFAULT CHARSET=latin1 COLLATE latin1_general_ci |

上面是表格的结构。接下来是插入和查询操作：

INSERT INTO myusers (fullname) VALUES ('Agüeda');
INSERT INTO myusers (fullname) VALUES ('Agueda');

SELECT * FROM myusers WHERE fullname = 'Agüeda' COLLATE latin1_general_ci 

+----+-------+----------+
| id | email | fullname |
+----+-------+----------+
|  1 |       | Agüeda   |
+----+-------+----------+
1 row in set (0.00 sec)

SELECT * FROM myusers WHERE fullname = 'agueda' COLLATE latin1_general_ci 

+----+-------+----------+
| id | email | fullname |
+----+-------+----------+
|  2 |       | Agueda   |
+----+-------+----------+
1 row in set (0.00 sec)

很明显，期望的结果是搜索“agueda”时，“Agueda”和“Agüeda”都会返回结果，但实际情况并非如此。正如我上面提到的，我创建了一个新的列，并将整个名称以英文字符存储，同时也从那里进行搜索。但仍然需要进行两次搜索（因为我还在原始列中进行搜索，这些列在搜索结果中排名更高）。应该有更好的方法...

- Shaokan

---

那些“点字符”被称为“变音符号”。搜索该术语可能会产生更好的结果。 :) - GolezTrol

哇，谢谢你，我一直在找那个呢 :D - Shaokan

你尝试过不区分大小写的排序规则（以“_ci”结尾的）吗？ - ypercubeᵀᴹ

是的，我尝试过了，但没有达到预期的结果。 - Shaokan

我看到了你之前的问题。请发布一个示例，包括：表定义（使用_ci排序规则），添加一些数据（几个INSERT语句）以及SELECT语句的结果。还请告知你使用的MySQL版本。 - ypercubeᵀᴹ

Answer 1

只需使用适当的排序规则。例如：

create table test(
    foo text
) collate = utf8_unicode_ci;
insert into test values('Agüeda');
insert into test values('Agueda');
select * from test where foo = 'Agueda';

这会给你两行。

- Artefacto

---

不行，那也不起作用。我还尝试了 foo = _utf8 'su' collate utf8_unicode_ci，但没有结果。 - Shaokan

是的，我刚刚再次更改了它，只是为了确保。 - Shaokan

@Sha 希望你不是在 Windows 上使用命令行。 - Artefacto

@Sha 试试这个：http://pastebin.com/ADS0SdTP，使用类似MySQL Workbench的工具。在命令行中，必须设置正确的代码页，即使如此... - Artefacto

不，我使用的是Ubuntu 10.04 :)，那段代码也不起作用。只是为了确保，我使用的是默认的MySQL设置，除了将字符集更改为utf8。 - Shaokan

@Shaokan：尝试将表的字符集更改为CHARSET ='utf8'，并将排序规则更改为'utf8_unicode_ci'。 - ypercubeᵀᴹ

Answer 2

1) 编写自己的排序规则。latin1_general_diacriticinsensitive。不过我甚至不知道从哪里开始 :)

2) 使用正则表达式和字符组：/[uü]ber/

3) 你脑海中的解决方案。个人而言，我会选择这个方案，因为设计都是妥协的过程，这是一个简单的解决方案，只需要增加100%的空间开销。当然，随着时间的推移，空间开销可能会转化为速度开销，特别是在MySQL中，但这是以后的问题。如果需要，这也非常容易撤消。

- aib

Answer 3

相比于尝试替换它们并运行x次搜索，我建议使用mysql函数LIKE。

SELECT * FROM x WHERE search LIKE '%ber'

您需要使用"%"替换重音符号。

编辑：我的错误，"%"可以替代任意数量的字符。对于单个字符，请使用"_"。

- redshark1802

---

是的，但这也会返回“Ultraber”和其他很多不需要的结果。 - ypercubeᵀᴹ

请使用"_"代替"%"，对不起我的错误 :) - redshark1802

Answer 4

看一下这篇帖子：https://dev59.com/MnRB5IYBdhLWcg3wz6ed

他遇到的问题正好与你相反。看看所选答案中的 WHERE 子句。可能你只需要使用 _ci 后缀就可以解决问题。

请告诉我们这个问题是如何解决的。

- Mosty Mostacho

---

我已经尝试过那个方法，但对我没有用。当我执行 select _utf8 'ş' collate utf8_general_ci = _utf8 's' 时，它可以工作，但是当我将其应用到一个列上时，它就不起作用了。所以我想我会按照我在脑海中描绘的解决方案去做。 - Shaokan