Menu
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
884
MySQL FULLTEXT SEARCH (FTS) Full-text search is a searching technique used to examine all words in the document that might not perfectly match the search criteria. The records contain textual data like product descriptions, blog posts, articles, etc.
The columns in the fulltext index can be placed in any sort of order, but all the columns as specified when creating the fulltext index must be referenced in the match() query.
Like uses wildcards only, and isn't all that powerful. Full text allows much more complex searching, including And, Or, Not, even similar sounding results (SOUNDEX) and many more items.
In the search grid, choose tables and views of interest or leave them all checked. To narrow down the MySQL search data scope, select the table, views, numeric, text type, and date columns checkboxes. To start the search, click the Find button or hit the Enter key from the keyboard.
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
85
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
