Database Reference
In-Depth Information
Another comparison functionis
MATCH() AGAINST()
, which searches for a string and
returns matching rows from the table. It even ranks the rows by relevance, but that is bey-
ond the scope of this chapter. Among the complications of
MATCH() AGAINST()
, it
works only on columns that have been indexed with aspecial
FULLTEXT
index. To test
this function, we'll first add a
FULLTEXT
index to the
bird_sightings
table, basing
it on the
comments
column because that's a
TEXT
column:
CREATE FULLTEXT INDEX
comment_index
ON
bird_sightings
(
comments
);
Now you can use
MATCH() AGAINST()
. It is commonlyfound in
WHERE
clauses as a
condition to find columns containing a given string. Text in the given string, which is de-
limited by spaces or quotes, is parsed into separate words. Small words (i.e., three charac-
ters or fewer) are generally ignored. Here is an example:
SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,
common_name AS Bird,
SUBSTRING(comments, 1, 25) AS Comments
FROM birdwatchers.bird_sightings
JOIN birdwatchers.humans USING(human_id)
JOIN rookery.birds USING(bird_id)
WHERE MATCH (comments) AGAINST ('beautiful');
+-------------------+-----------------+---------------------------+
| Name | Bird | Comments |
+-------------------+-----------------+---------------------------+
| Elena Bokova | Eskimo Curlew | It was a major effort get |
| Katerina Smirnova | Eurasian Curlew | Such a beautiful bird. I |
+-------------------+-----------------+---------------------------+
In the
WHERE
clause, we're able now to match the
comments
column against the string
beautiful
. The
comments
column from the
birdwatchers.bird_sightings
is combined in the results with three other columns:
common_name
from
rook-
ery.birds
and
name_first
and
name_last
from
birdwatchers.humans
.
We're using the
SUBSTRING
function to limit the amount of text displayed. This cuts off
the text abruptly. You could use the
CONCAT()
functionto append ellipses to indicate
there is more text. You might also use the
IF()
functionto determine whether there is
more text before appending ellipses. There are other functions you can use for locating the
beautiful
within the column so that you can display only the text around it. We'll cov-
er that kind of function later in thischapter.
