0

I can do a MariaDB fulltext query which searches for the word beginning like this:

select * from mytable
where match(mycol) against ('+test*' in boolean mode)>0.0;

This finds words like "test", "tester", "testing".

If my search string contains special characters, I can put the search string in quotes:

select * from mytable
where match(mycol) against ('+"test-server"' in boolean mode)>0.0;

This will find all rows which contain the string test-server.

But it seems I cannot combine both:

select * from mytable
where match(mycol) against ('+"test-serv"*' in boolean mode)>0.0;

This results in an error:

Error: (conn:7) syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
SQLState:  42000
ErrorCode: 1064

Placing the ´*´ in the quoted string will return no results (as expected):

select * from mytable
where match(mycol) against ('+"test-serv*"' in boolean mode)>0.0;

Does anybody know whether this is a limitation of MariaDB? Or a bug?

My MariaDB version is 10.0.31

wknauf
  • 160
  • 1
  • 7

1 Answers1

1
WHERE MATCH(mycol) AGAINST('+test +serv*' IN BOOLEAN MODE)
  AND mycol LIKE '%test_serv%'

The MATCH will find the desired rows plus some that are not desired. Then the LIKE will filter out the duds. Since the LIKE is being applied to only some rows, its slowness is masked.

(Granted, this does not work in all cases. And it requires some manual manipulation.)

d'Artagnan - Use

WHERE MATCH(mycol) AGAINST("+Arta*" IN BOOLEAN MODE)
  AND mycol LIKE '%d\'Artagnan%'

Note that I used the suitable escaping for getting the apostrophe into the LIKE string.

So, the algorithm for your code goes something like:

  1. Break the string into "words" the same way FULLTEXT would.
  2. Toss any strings that are too short.
  3. If no words are left, then you cannot use FULLTEXT and are stuck with a slow LIKE.
  4. Stick * after the last word (or each word?).
  5. Build the AGAINST with those word(s).
  6. Add on AND LIKE '%...%' with the original phrase, suitably escaped.
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks, this will help for the "-" issue. But this is a general problem: for example searching for "d'Artagnan": if the string "d'Arta" is entered, I cannot perform a search like this: `match (mycol) against (+"d'Arta"* in boolean mode) > 0.0` Do you know of any doc/best pratice guide on how to handle those special chars? – wknauf Oct 29 '18 at 08:51
  • @wknauf - I responded in my Answer. – Rick James Oct 29 '18 at 15:20
  • After thinking about this a bit, I applied your suggestion to our code, and it works. Seems this is the "official" way to solve it ;-). Many thanks! – wknauf Nov 01 '18 at 14:03