Using the same match...against clause in select clause and where clause : MATCH « FullText Search « SQL / MySQL






Using the same match...against clause in select clause and where clause

     
mysql>
mysql> CREATE TABLE BOOKS(
    ->     BOOKNO INTEGER NOT NULL PRIMARY KEY,
    ->     AUTHORS TEXT NOT NULL,
    ->     TITLE TEXT NOT NULL,
    ->     YEAR_PUBLICATION YEAR NOT NULL,
    ->     SUMMARY TEXT NOT NULL
    -> )ENGINE = MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SET @@SQL_MODE = 'PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO BOOKS VALUES (1,
    -> 'Tom, Jack, Jane',
    -> 'January', 2007,
    -> 'January is the first month of the year in the Julian and Gregorian calendars and one of seven months with the length '||
    -> 'of 31 days. The first day of the month is known as New Year\'s Day. It is, on average, the coldest month of the year '||
    -> 'within most of the Northern Hemisphere (where it is the second month of winter) and the warmest month of the year '||
    -> 'within most of the Southern Hemisphere (where it is the second month of summer). In the Southern Hemisphere, January '||
    -> 'is the seasonal equivalent of July in the Northern Hemisphere.');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO BOOKS VALUES (2,
    -> 'George, Jean and Tim ',
    -> 'History', 2005,
    -> 'January is named after Janus (Ianuarius), the god of the doorway; the name has its beginnings in Roman mythology, '||
    -> 'coming from the Latin word for door (ianua) - January is the door to the year. Traditionally, the original Roman '||
    -> 'calendar consisted of 10 months, totalling 304 days, winter being considered a monthless period. Around 713 BC, '||
    -> 'the semi-mythical successor of Romulus, King Numa Pompilius, is supposed to have added the months of January and '||
    -> 'February, allowing the calendar to equal a standard lunar year (365 days). Although March was originally the first '||
    -> 'month in the old Roman Calendar, January became the first month of the calendar year either under Numa or under '||
    -> 'the Decemvirs about 450 BC (Roman writers differ). In contrast, years in dates were identified by naming two consuls,' ||
    -> 'who entered office on May 1 and March 15 before 153 BC when they began to enter office on January 1.');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO BOOKS VALUES (3,
    -> 'Rick',
    -> 'New Year\'s Day', 2007,
    -> 'The Romans dedicated this day to Janus, the god of gates, doors, and beginnings. The month of January was named after '||
    -> 'Janus, who had two faces, one looking forward and the other looking backward. This suggests that New Year\'s '||
    -> 'celebrations are founded on pagan traditions. Some have suggested this occurred in 153 BC, when it was stipulated that '||
    -> 'the two annual consuls (after whose names the years were identified) entered into office on that day, though no consensus '||
    -> 'exists on the matter. Dates in March, coinciding with the spring equinox, or commemorating the Annunciation of Jesus, '||
    -> 'along with a variety of Christian feast dates were used throughout the Middle Ages, though calendars often continued to '||
    -> 'display the months in columns running from January to December.');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO BOOKS VALUES (4,
    -> 'Chris Date ',
    -> 'Gregorian calendar', 2004,
    -> 'The Gregorian calendar, also known as the Western calendar, or Christian calendar, is the internationally accepted '||
    -> 'civil calendar. It was introduced by Pope Gregory XIII, after whom the calendar was named, by a decree signed on 24 '||
    -> 'February 1582, a papal bull known by its opening words Inter gravissimas. The reformed calendar was adopted later '||
    -> 'that year by a handful of countries, with other countries adopting it over the following centuries. The motivation '||
    -> 'for the Gregorian reform was that the Julian calendar assumes that the time between vernal equinoxes is 365.25 days, '||
    -> 'when in fact it is presently almost exactly 11 minutes shorter. The error between these values accumulated at the '||
    -> 'rate of about three days every four centuries, resulting in the equinox occurring on March 11 (an accumulated error '||
    -> 'of about 10 days) and moving steadily earlier in the Julian calendar at the time of the Gregorian reform. Since the '||
    -> 'Spring equinox was tied to the celebration of Easter, the Roman Catholic Church considered that this steady movement '||
    -> 'in the date of the equinox was undesirable.');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> INSERT INTO BOOKS VALUES (5,
    -> 'Thomas, Carolyn and Mary',
    -> 'Lunar calendar',2005,
    -> 'The Catholic Church maintained a tabular lunar calendar, which was primarily to calculate the date of Easter, '||
    -> 'and the lunar calendar required reform as well. A perpetual lunar calendar was created, in the sense that 30 '||
    -> 'different arrangements (lines in the expanded table of epacts) for lunar months were created. One of the 30 '||
    -> 'arrangements applies to a century (for this purpose, the century begins with a year divisible by 100). When '||
    -> 'the arrangement to be used for a given century is communicated, anyone in possession of the tables can find '||
    -> 'the age of the moon on any date, and calculate the date of Easter.');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> ALTER TABLE books ADD FULLTEXT (authors, title, summary);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE books ADD FULLTEXT (authors);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE books ADD FULLTEXT (title);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE books ADD FULLTEXT (summary);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> SELECT   BOOKNO, MATCH(summary) AGAINST ('calculate')
    -> FROM     BOOKS
    -> WHERE    MATCH(summary) AGAINST ('calculate');
+--------+--------------------------------------+
| BOOKNO | MATCH(summary) AGAINST ('calculate') |
+--------+--------------------------------------+
|      5 |                     1.38794815540314 |
+--------+--------------------------------------+
1 row in set (0.00 sec)

mysql> drop table books;
Query OK, 0 rows affected (0.00 sec)

mysql>

   
    
    
    
    
  








Related examples in the same category

1.A MATCH expression for fulltext search can be used to order results.
2.Use MATCH in where statement
3.MATCH(TITLE) AGAINST ('to')
4.Using match in where clause
5.Using match in select statement
6.Matches two words
7.Matches two columns
8.Matches two columns in boolean mode
9.Match two words in boolean mode
10.Match against a long sentence
11.Get the numbers and relevance values of the books in which distributed appears in the summary.
12.Get the numbers and titles of the books in which database appears in the title.
13.Get the numbers and titles of the books in which the phrase design implementation appears.
14.You can include additional criteria to narrow the search further.
15.Full-Text Search syntax