MySQL 中不区分重音的搜索查询 [英] Accent insensitive search query in MySQL

问题描述

有没有办法让搜索查询重音不敏感?

Is there any way to make search query accent insensitive?

列和表的排序规则是 utf8_polish_ci，我不想更改它们.

the column's and table's collation are utf8_polish_ci and I don't want to change them.

示例词:toruń

select * from pages where title like '%torun%'

它没有找到toruń".我该怎么做?

It doesn't find "toruń". How can I do that?

推荐答案

您可以在 sql 查询中在运行时更改排序规则，

You can change the collation at runtime in the sql query,

...where title like '%torun%' collate utf8_general_ci

但请注意，在运行时动态更改排序规则会放弃 mysql 使用索引的可能性，因此在大型表上的性能可能会很糟糕.

but beware that changing the collation on the fly at runtime forgoes the possibility of mysql using an index, so performance on large tables may be terrible.

或者，您可以将该列复制到另一列，例如 searchable_title，但更改其上的排序规则.做这种类型的事情实际上很常见，在这种情况下，您复制数据，但以稍微不同的形式保存数据，这些形式针对某些特定的工作负载/目的进行了优化.您可以使用触发器作为保持重复列同步的好方法.如果编入索引，此方法有可能表现良好.

Or, you can copy the column to another column, such as searchable_title, but change the collation on it. It's actually common to do this type of stuff, where you copy data but have it in some slightly different form that's optimized for some specific workload/purpose. You can use triggers as a nice way to keep the duplicated columns in sync. This method has the potential to perform well, if indexed.

注意 - 确保您的数据库确实具有这些字符而不是 html 实体.此外，您的连接的字符集也很重要.上面假设它被设置为 utf8，例如，通过 set names 像 <代码>设置名称utf8

Note - Make sure that your db really has those characters and not html entities. Also, the character set of your connection matters. The above assumes it's set to utf8, for example, via set names like set names utf8

如果没有，您需要一个介绍人来获取文字值>

If not, you need an introducer for the literal value

...where title like _utf8'%torun%' collate utf8_general_ci

当然，单引号中的值实际上必须是 utf8 编码的，即使 sql 查询的其余部分不是.

and of course, the value in the single quotes must actually be utf8 encoded, even if the rest of the sql query isn't.

这篇关于MySQL 中不区分重音的搜索查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！