Accent insensitive search query in MySQL

mysql search diacritics
Okan Kocyigit picture Okan Kocyigit · Dec 27, 2011 · Viewed 26.5k times · Source

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?

Answer

goat picture goat · Dec 27, 2011

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.

Related questions

Search for string within text column in MySQL

I have mysql table that has a column that stores xml as a string. I need to find all tuples where the xml column contains a given string of 6 characters. Nothing else matters--all I need to know is if this 6 …

Read More
MySQL search and replace some text in a field

What MySQL query will do a text search and replace in one particular field in a table? I.e. search for foo and replace with bar so a record with a field with the value hello foo becomes hello bar.

Read More
Fulltext Search with InnoDB

I'm developing a high-volume web application, where part of it is a MySQL database of discussion posts that will need to grow to 20M+ rows, smoothly. I was originally planning on using MyISAM for the tables (for the built-in fulltext …

Read More