MATCH ... AGAINST没有得出确切的结果

Question

I'm working for the first time with MATCH...AGAINST in php sql but there is one bothering me and I can't figure out how to fix it. This is my code:

SELECT * FROM m_artist WHERE match(artist_name) against('". $_POST['article_content'] ."' IN BOOLEAN MODE)

And this is $_POST['article_content']:

Wildstylez Brothers Yeah Frontliner Waveliner

Now my output should be: Wildstylez, Frontliner and Waveliner cause that's in my database. And I do but besides that I also get the Vodka Brothers, 2 Brothers of Hardstyle and more cause of the word brothers. How do I fix that SQL only selects the literal match?

Answer 1

Full-text search actually is a quite misleading name: you can search the full text by your query (like google does) but it won't guarantee you, that the full text equals your query.

So, according to documentation on Boolean Full-Text Searches your input Wildstylez Brothers Yeah Frontliner Waveliner is interpreted as artist_name contains (at least) one of Wildstylez, Brothers, Yeah, Frontliner and Waveliner as word. This is why you get e.g. the Vodka Brothers, which contains Brothers. For google-like purposes this is just what you want, as you want to get details on something you only know part of as in show me articles on music.

You probably want to use

artist_name LIKE '%name_part1%' OR artist_name LIKE '%name_part2%' ...

or

artist_name IN ('exact_name1', 'exact_name2', ...)

simpliest case would be doing something like

$names = explode(' ', $_POST['article_content']);
$name_searches = array_map(function($a) {return 'artist_name = \''.mysql_real_escape_string($a).'\'';}, $names);
$sql = "SELECT * FROM m_artist WHERE ".implode(" OR ", $name_searches);

but you would loose the ability to find 2 Brothers of Hardstyle as the name itself contains a space.

Another approach can be to prefix all words by '+' and stick to MATCH() AGAINST() and you will find only artists which include every word given.

Please provide more context if this is not what you are looking for.