1

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.

1

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?

  • thanks for your time and suggestion, but I kinda don't want to have a second table for that... I just cannot figure this out :( – Victor Tello Jul 5 '16 at 0:32
  • 1
    Not a second table - an extra field in a table. Minimal design or space overhead with a solution to your issue. – Vérace Jul 5 '16 at 1:11
  • Uhm I like that, but the problem is I'm joining 3 or more tables, I can't do that on all tables just because of some misconfiguration, I mean it should work. Thanks anyway. – Victor Tello Jul 5 '16 at 1:17
  • But, aren't you joining with FOREIGN KEYs? Maybe it could be a simple search and replace_all? Actually, think of it as name_display and name_shadow. Modifying answer. – Vérace Jul 5 '16 at 1:23
0

You just need to tell MySQL to use a different collation.

So, in your case:

select *
from t1
where description like 'instruccion' collate utf8_general_ci;

ci means Case Insensitive, and it flattens accents as well as lower-casing.

Example SQL Fiddle here.

  • the table is already utf8_general_ci, why would I need to put that again? Also I read that setting collate on-the-fly eliminates de benefits of INDEX and I surely don't want that. – Victor Tello Jul 4 '16 at 19:19
0

Got it!

You must ensure that all parties (your app, mysql connection, your table or column) have set utf8 as charset.

In my case all my scripts are routed so I just added this to my index.php:

header('Content-Type: text/html; charset=utf-8');

Ensure your mysqli connection use utf8 before any operation:

$mysqli->set_charset('utf8')

And of course create your table or column using utf8_general_ci

  • Funny, I would have said that case and accent are two completely unrelated concepts - I knew that _ci meant case-insensitive, but not accent insensitive. Anyway, don't foget to mark your own answer as correct for anybody with a similar issue in the future. I think that you have to wait 24hours? – Vérace Jul 5 '16 at 2:38
  • @Vérace Yeah, both case and accent insensitive xd... Thanks for your time tho. Yeah, 48 hours, I will :D – Victor Tello Jul 5 '16 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.