MySQL Fulltext search against column value?

Question

I need to do a Fulltext search for a whole bunch of values out of a column in another table. Since MATCH() requires a value in the AGAINST() part, a straightforward: "SELECT a.id FROM a,b WHERE MATCH(b.content) AGAINST(a.name)" fails with "Incorrect arguments to AGAINST".

Now, I know I could write a script to query for a list of names and then search for them, but I'd much rather work out a more complex query that can handle it all at once. It doesn't need to be speedy, either.

Ideas?

thanks

Norvun

Answer 1

Unfortunately, http://dev.mysql.com/doc/refman/5.6/en/fulltext-search.html says:

The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.

Looks like you'll have to search for the patterns one at a time if you use MySQL's FULLTEXT index as your search solution.

The only alternative I can think of to allow searching for many patterns like you describe is an Inverted Index. Though this isn't as flexible or scalable as a true full-text search technology.

See my presentation http://www.slideshare.net/billkarwin/practical-full-text-search-with-my-sql

Sewoll

Yeah, I've seen that, but dev.mysql.com/tech-resources/articles/full-text-revealed.html says ""The argument to AGAINST() must be a constant string." This is nothing to worry about. We just haven't updated the manual yet to show that the AGAINST() argument can be a variable or parameter, if you use MySQL Version 5." I was hoping someone would have some advice using some kind of more complex sql approach (views, procedures, or something). Actually, what I'm up to is basically building an inverted index of terms from table a occurring in table b's text column. – Norvun

That article is from May 2004, but they still haven't updated the manual?! That's pathetic! – Sewoll

Says something about the importance of fulltext to mysql, i suppose. I've found myself studying Squid lately, but this is a side-aspect to a project, and I really had no intention of systemic changes. – Norvun

@LightnessRacesinOrbit, thanks for the correction. I have edited the above to match the manual update mentioned in that bug log. Though it makes no difference to the original question here -- the OP still cannot use a column name as an argument to AGAINST(). Can you consider reversing your downvote? – Sewoll

@BillKarwin: I certainly can :) – Mercantile

Answer 2

I hope my solution will be useful to you:

PREPARE stat FROM 'SELECT user_profession FROM users INNER JOIN professions ON MATCH(user_profession) AGAINST (?)';
SET @c_val = (SELECT prfs_profession FROM professions WHERE prfs_ID=1);
EXECUTE stat USING @c_val;

Alius

I don't think this is helpful, this is basically passing the value as a named parameter in a prepared statement, the question is about passing different values based upon a column value in one query.. thank you very much for the effort – Ondometer