Full-text search of mysql tutorial. SQL statement:
MATCH (col1, col2 ,...) AGAINST (expr [in boolean mode | with query expansion])
The MATCH () function performs a natural language search in the database for a string. A database contains one or two columns in FULLTEXT. The search string is given as a parameter to AGAINST. For each row in the table, MATCH () returns a correlation value, that is, a similarity measurement between the text of the row in the specified column in the search string and MATCH () table.
Alter table <table> add fulltext (fields)
We cocould query the database using the MATCH () and AGAINST () functions.
SELECT * FROM tableName where match (fields) AGAINST ('search Terminal ')
Mysql> create table Employee (
-> Id int,
-> First_name VARCHAR (15 ),
-> Last_name VARCHAR (15 ),
-> Start_date DATE,
-> End_date DATE,
-> Salary FLOAT (8, 2 ),
-> City VARCHAR (10 ),
-> Description VARCHAR (15)
-> );
Query OK, 0 rows affected (0.02 sec)
Mysql>
Mysql>
Mysql> alter table employee add fulltext (first_name );
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql>
Mysql>
Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (1, 'jason J', 'martin ', '000000', '000000', 19960725, 'toronto', 'programmer ');
Query OK, 1 row affected (0.03 sec)
Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (2, 'Ali J son', 'mathews ', '000000', '000000', 19760321, 'vancouver', 'tester ');
Query OK, 1 row affected (0.00 sec)
Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (3, 'James ', 'Smith', '000000', '000000', 19781212, 'vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)
Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (4, 'cela', 'Rice ', '000000', '000000', 19821024, 'vancouver', 'manager ');
Query OK, 1 row affected (0.00 sec)
Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (5, 'Robert ', 'black', '000000', '000000', 19840115, 'vancouver', 'tester ');
Query OK, 1 row affected (0.00 sec)
Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (6, 'linda ', 'green', '000000', '000000', 19870730, 'New York', 'tester ');
Query OK, 1 row affected (0.01 sec)
Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (7, 'David ', 'Larry', '123', '123', 19901231, 'New York ', 'manager ');
Query OK, 1 row affected (0.00 sec)
Mysql>
Mysql> insert into Employee (id, first_name, last_name, start_date, end_Date, salary, City, Description)
-> Values (8, 'James ', 'cat', '000000', '000000', 19960917, 'vancouver', 'tester ');
Query OK, 1 row affected (0.00 sec)
Mysql>
Mysql> select * from Employee;
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
| Id | first_name | last_name | start_date | end_date | salary | city | description |
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
| 1 | Jason J | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Ali J son | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 4322.78 | New York | Tester |
| 7 | David | Larry | 7897.78 | New York | Manager |
| 8 | James | Cat | 1232.78 | Vancouver | Tester |
+ ------ + ------------ + ----------- + ------------ + --------- + ----------- + ------------- +
8 rows in set (0.00 sec)
Mysql>
Mysql>
Mysql>
Mysql>
Mysql> SELECT id, first_name
-> FROM employee
-> WHERE
-> MATCH (first_name) AGAINST ('J ');
Empty set (0.02 sec)
Mysql>
Mysql>
Mysql>
Mysql>
Mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
Mysql>
Mysql>