具有多个绑定参数的MySQL UTF-8全文搜索

Question

My query shows like that:

SELECT advert_id
FROM oop_adverts
WHERE cat_down = :id 
  AND province = :province
  AND MATCH (location) AGAINST (:location);

in practise:

SELECT advert_id 
FROM oop_adverts 
WHERE cat_down = 3 
  AND province = 5 
  AND MATCH (location) AGAINST ('Krakow');

And if I try this query, mysql finally get 0 results. The problem is polish phrases in query. When I replaced this query for:

SELECT advert_id 
FROM oop_adverts 
WHERE cat_down = 3
    AND province = 5 
    AND MATCH (location) AGAINST ('Krakow') COLLATE utf8_unicode_ci;`

I got:

Syntax error or access violation: 1253 COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'binary''

I don't know what collate I should use in script php and in mysql. Can you help me?

PS. Sorry for my "best english" bro.

Answer 1

EDITED as per discussion in comments:

As your table scheme looks fine (in terms of utf8) and the first code example you gave in OP is correct (the one without collate), assuming you've got the proper DB collation and the connection itself as well - most probably you have Kraków for advert_location in more than 50% of rows and that is why you get 0 rows of result.

If you want to user Full Text Search, you have to always remember that if a table's fulltext index contains a keyword that appears in 50% of the data rows, that keyword will be ignored by the match query.

So instead you can use Full Text Serach in boolean mode to bypass the 50% threshold. Check the docs here MySQL Boolean Full-Text Searches

So for example, if you have 3 rows in table with Kraków, Krakow and Warszawa as advert_location, the below query will give you 0 rows result:

SELECT advert_id 
FROM oop_adverts 
WHERE MATCH(`advert_location`) AGAINST ('Kraków')

But if you use the boolean mode, you will get 2 rows result:

SELECT advert_id 
FROM oop_adverts 
WHERE MATCH(`advert_location`) AGAINST ('Kraków' IN BOOLEAN MODE)

If you wish to match multiple words, you can use the "+" operator (refer to the docs linked above for details).

SELECT advert_id 
FROM oop_adverts 
WHERE MATCH(`advert_location`,`advert_title`) AGAINST ('+Kraków' '+Search phrase' IN BOOLEAN MODE)

One remark, remember to construct the bound parameter already with the "+" operator included, for example if you are using PHP you can do like this:

$query= "SELECT advert_id 
        FROM oop_adverts 
        WHERE MATCH(`advert_location`,`advert_title`) AGAINST (:location :title IN BOOLEAN MODE)";

$SQL=$db->prepare($query);                          
$SQL->bindValue(':location', '+'.$searched_location, PDO::PARAM_STR);
$SQL->bindValue(':title', '+'.$searched_title, PDO::PARAM_STR);