MySQL accent insensitive word search

After spending almost all of my day searching a fix to this I come to you guys.

I need to do an accent insensitive word search on my db(InnoDB), but I cannot get it to work.

I have t1 table, the field description which is an indexed varchar(250), furthermore my table collation is utf8_general_ci.

t1 have a record in which the field description have stored bla bla... instrucción... bla. just in case you are wondering how are the records stored.

I'm trying to fetch the records using the words instrucción and instruccion using this:

SELECT id FROM t1 WHERE description LIKE '%instruccion%'

If I use instrucción I get a non-empty result, but using instruccion I get an empty result.

Please help and thanks.

A Google of "mysql accent insensitive search" produces various links with various "tricks" - which all seem to invalidate any indexes you may have on your table.

I had to implement something similar years ago for Irish names and addresses which may contain accents.

The strategy (if your RDBMS doesn't support accent insensitive searches) is to use a shadow column which keeps track of a "base" word - in our case unaccented, capitalised and spaces removed so that when a record with the name:

name_display
Ronán Ó Sé

is INSERTed (or UPDATEd), what will be stored as

name_shadow
RONANOSE

and then all searches are made against the shadow column.

In the app, we capitalised, and removed accents and spaces from all search strings when sending them to the server, so that the search was comparing like with like - i.e. unaccented, capitalised, space-removed strings.

This had the dual advantage of being always able to use indexes and being portable across RDBMS servers. Being an Irish app, we also had a strategem turning Mac and Mc into MC so that variant spellings were also taken into account - maybe an issue for you also?

We did this using TRIGGERs - but I believe that as of MySQL 5.7, GENERATED (also known as VIRTUAL or COMPUTED) columns are supported which may simplify the logic?

StackExchange Database Administrators Q#142943, answer score: 2