What does SELECT *, MATCH mean?

Question

I know SELECT * FROM table but I never saw SELECT *, MATCH before.

My working query is following

$query = $this->dbi
        ->prepare("SELECT *, MATCH(title, content) AGAINST (?) AS score FROM tmp_comments WHERE MATCH(title, content) AGAINST(?)")
        ->execute($this->key,$this->key)
        ->results();

It does not work if I remove , after SELECT *

Please help me out, I tried a Google search but could not find anything.

MATCH is the function in class imported for database operations in your framework — Bipin Chandra Tripathi, Dec 15 '12 at 16:03
A comma in the `SELECT` columnlist is just a separator. So, this gets all columns (as you would expect from `*`) and then also gets the result of the clause `MATCH(title, content) AGAINST (?) AS score`. So your question really is "what does `MATCH AGAINST` do"? — halfer, Dec 15 '12 at 16:06

score 2 · Answer 1 · edited May 23 '17 at 12:21

2

here an exempel

     SELECT * FROM users WHERE MATCH(username) AGAINST ('LU*' IN BOOLEAN MODE)

from here Problem with IE and setInterval() not refreshing/updating

edited May 23 '17 at 12:21

Community

1
1

answered Dec 15 '12 at 16:06

echo_Me

37,078
5
58
78

score 1 · Answer 2 · answered Dec 15 '12 at 16:03

1

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

There you go, it didn't take much googling

answered Dec 15 '12 at 16:03

martskins

2,920
4
26
52

score 1 · Answer 3 · answered Dec 15 '12 at 16:03

1

MATCH is a mysql function. It's just another column in the resultset.

answered Dec 15 '12 at 16:03

MLeblanc

1,816
12
21

John Parker · Answer 4 · 2012-12-15T18:11:28.917

What you're saying is "get all the fields from all the tables in the query" (the "*" bit) and return the value of the fulltext search query (the MATCH statement) as the "score" variable.

Incidentally, using SELECT * is potentially very inefficient unless you really need all of the fields. If possible, list the required fields via...

SELECT fieldname_a, fieldname_b, MATCH(title, content) AGAINST (?) AS score ...

By doing this, you won't be needlessly transferring data you don't require.

score 1 · Answer 5 · answered Dec 15 '12 at 16:06

Select * is used to select all the columns in the table. However in your case, the query is selecting all the columns in addition to the relevancy figure i.e. coming through using MATCH(title, content) AGAINST (?) AS score.

For more details on getting the relevancy/score through MATCH AGAINST query you can consult FULLTEXT Search.

score 1 · Accepted Answer · answered Dec 15 '12 at 16:07

It basically means that first print out all the columns of the desired resultset and then the match found(on the given rule).

See if the following example, makes it clear to you:

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')
    -> FROM articles;
+----+-----------------------------------------+
| id | MATCH (title,body) AGAINST ('Tutorial') |
+----+-----------------------------------------+
|  1 |                        0.65545833110809 |
|  2 |                                       0 |
|  3 |                        0.66266459226608 |
|  4 |                                       0 |
|  5 |                                       0 |
|  6 |                                       0 |
+----+-----------------------------------------+
6 rows in set (0.00 sec)

What does SELECT *, MATCH mean?

6 Answers6