how can i find best match for a table in mysql db
I have a table in mysql database(product{id, name, category}
) which consists of hundreds of records.
Now a new product is add开发者_运维问答ed with the same fields and i want to search for the fields and match it with others records so that i could found the best match of product.
My question is what is the best approach for this, should i search through the name of product but what if there are more than one match with that names.
should i use something like this
SELECT * FROM table_name WHERE column1 LIKE '%column1%' OR column2 LIKE '%column2%'
You are looking for the MySQL "MATCH()" function. This comes with a couple caveats:
- This requires a fulltext index (meaning you must be using the MyISAM Engine for that table)
- Requires MySQL >= 5.1
From the Manual:
When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first.
http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html
You should look inty MySQL full-text indexing capabilities for generating relevance-sorted output. There are a number of posts on that subject. See for instance this post.
精彩评论