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