Accent insensitive search query in MySQL
Is there any way to make search query accent insensitive?
the column's and table's collation are utf8_polish_ci and I don't want to change them.
example word : toruń
select * from pages where title like '%torun%'
It doesn't find "toruń". How can I do that?
You can change the collation at runtime in the sql query,
...where title like '%torun%' collate utf8_general_ci
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.
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.
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
and of course, the value in the single quotes must actually be utf8 encoded, even if the rest of the sql query isn't.
This wont work in extreme circumstances, but try to change the column collation to UFT8 utf8_unicode_ci . Then accented characters will be equal to their non-accented counterparts.
You could try SOUNDEX:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_soundex
This compares two string by how they sound. But this obviously delivers many more results.
链接地址: http://www.djcxy.com/p/95196.html上一篇: 什么特殊字符必须在正则表达式中转义？
下一篇: 在MySQL中对口音不敏感的搜索查询