mysql

Khóa học miễn phí MySQL – Fulltext Search nhận dự án làm có lương

Posted on by user

MySQL – Full-Text Search

Table of content

The MySQL Full-Text Search allows us to search for a text-based data, stored in the database. Before performing the full-text search in a column(s) of table, we must create a full-text index on those columns.

The FTS (full-text search) provides the capability to match the searched string value through large text content such as blogs, articles, etc.

MySQL Full-Text Search

To perform a Full-Text Search on a MySQL table, we use MATCH() and AGAINST() functions in a WHERE clause of an SQL SELECT statement.

Stop words are words that are commonly used (such as ”on”, ”the”, or, ”it”) in sentences and will be ignored during the searching process.

The basic syntax to perform a full-text search on a MySQL is as follows −

SELECT column_name(s) FROM table_name 
WHERE MATCH(col1, col2, ...) 
AGAINST(expression [search_modifier])

Here,

  • MATCH() function contains one or more columns separated by commas to be searched.
  • AGAINST() function contains a search string to use for the full-text search.

Key Points of MySQL Full-Text Search

Following are some key points about the full-text search in MySQL −

  • Either InnoDB or MyISAM tables use the full-text indexes. The minimum length of the word for full-text searches is three characters for InnoDB tables and four characters for MyISAM tables.
  • Full-Text indexes can be created on text-based columns (CHAR, VARCHAR or TEXT columns).
  • A FULLTEXT index can be defined while creating the table using CREATE TABLE statement or can be defined later using the ALTER TABLE or CREATE INDEX statements.
  • Without FULLTEXT index, it is faster to load large data sets into a table than to load data into a table which has an existing FULLTEXT index. Therefore it is recommended to create the index after loading data.

Types of Full-Text Searches

There are three types of full-text searches. The same is described below:

  • Natural Language Full-Text Searches: This allows the user to enter the search query in a natural human language without any special characters or operators. The search engine will examine the query entered by the user and returns the relevant results based on the user”s intent.
  • Boolean Full-Text Searches: This allows us to perform a full-text search based on very complex queries in the Boolean mode along with Boolean operators such as +, -, >,
  • Query Expansion Searches: This expands the user”s query to widen the search result of the full-text searches based on automatic relevance feedback or blind query expansion.

Creating MySQL FULLTEXT Index

In MySQL, we can define a full-text index on particular column while creating a new table or on an existing table. This can be done in three ways:

  • Using the FULLTEXT Keyword

  • Using the ALTER TABLE Statement

  • Using the CREATE INDEX Statement

Using the FULLTEXT Keyword

To define full-text index on a column while creating a new table, we use the FULLTEXT keyword on that column within the CREATE TABLE query. Following is the syntax −

CREATE TABLE table_name(  
   column1 data_type,  
   column2 data_type,
   ...,
   FULLTEXT (column1, column2, ...)  
);

Example

Let us create first a table named FILMS and define the full-text index on NAME and DIRECTOR columns, using the following query −

CREATE TABLE FILMS (
   ID int auto_increment not null primary key,
   NAME varchar(50),
   DIRECTOR TEXT,
   FULLTEXT (NAME, DIRECTOR)
);

Now, let us insert values into this table using the following query − 

INSERT INTO FILMS (NAME, DIRECTOR) VALUES 
(''RRR'', ''Directed by Rajamouli''),
(''Bahubali'', ''Directed by Rajamouli''),
(''Avatar'', ''Directed by James cameron''),
(''Robot'', ''Directed by Shankar'');

The table will be created as −

ID NAME DIRECTOR
1 RRR Directed by Rajamouli
2 Bahubali Directed by Rajamouli
3 Avatar Directed by James Cameron
4 Robot Directed by Shankar

Here, we are fetching all the rows from the FILMS table where the NAME or DIRECTOR column matches the string ‘Rajamouli’ using the MATCH and AGAINST functions as shown below −

SELECT * FROM FILMS 
WHERE MATCH (NAME, DIRECTOR) 
AGAINST (''Rajamouli'');

Output

As we can see in the output below, the full-text search has been performed against a string ‘Rajamouli’ and it returned the rows which contains this string.

ID NAME DIRECTOR
1 RRR Directed by Rajamouli
2 Bahubali Directed by Rajamouli

Using the ALTER TABLE Statement

In MySQL, we can create full-text index on particular columns of an existing table using the ALTER TABLE statement. Following is the syntax −

ALTER TABLE table_name    
ADD FULLTEXT (column1, column2,...)

Example

In this example, we are defining a full-text index named FULLTEXT on NAME and DIRECTOR columns of the previously created FILMS table −

ALTER TABLE FILMS ADD FULLTEXT (NAME, DIRECTOR);

Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string ”Shankar”.

SELECT * FROM FILMS 
WHERE MATCH (NAME, DIRECTOR) 
AGAINST (''Shankar'');

Output

Following is the output −

ID NAME DIRECTOR
4 Robot Directed by Shankar

Using the CREATE INDEX Statement

In MySQL, we can also create a full-text index for an existing table using the CREATE INDEX statement. Following is the syntax −

CREATE FULLTEXT INDEX index_name  
ON table_name (index_column1, index_column2,...)

Example

We are creating a full-text index with the name INDEX_FULLTEXT on the NAME and DIRECTOR column of the FILMS table −

CREATE FULLTEXT INDEX INDEX_FULLTEXT ON FILMS (NAME, DIRECTOR);

Now, let us retrieve all the rows from the FILMS table where the NAME or DIRECTOR column matches the string value as shown in the below query −

SELECT * FROM FILMS 
WHERE MATCH(NAME, DIRECTOR) 
AGAINST (''James Cameron'');

Output

Following is the output −

ID NAME DIRECTOR
3 Avatar Directed by James Cameron

Dropping MySQL FULLTEXT index

In MySQL, we can remove or drop a full-text index from a table using the ALTER TABLE DROP INDEX statement.

Syntax

Following is the syntax −

ALTER TABLE table_name DROP INDEX index_name;

Example

In the following query, we will delete the previously created full-text index −

ALTER TABLE FILMS DROP INDEX INDEX_FULLTEXT;

Verification

Let us verify whether the index is dropped or not by executing the below query −

SELECT * FROM FILMS 
WHERE MATCH(NAME, DIRECTOR) 
AGAINST (''James Cameron'');

As we can see in the output, the full-text index is removed on the NAME and DIRECTOR columns.

ERROR 1191 (HY000): Can''t find FULLTEXT index matching the column list

Full-Text Search Using Client Program

In addition to performing the full-text search using MySQL Query, we can also do so using the client program.

Syntax

To perform the Fulltext Search on a MySQL database through a PHP program, we need to execute the CREATE TABLE statement using the mysqli function query() as follows −

$sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
$mysqli->query($sql);

To perform the Fulltext Search on a MySQL database through a JavaScript program, we need to execute the CREATE TABLE statement using the query() function of mysql2 library as follows −

sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
con.query(sql);

To perform the Fulltext Search on a MySQL database through a Java program, we need to execute the CREATE TABLE statement using the JDBC function execute() as follows −

String sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
statement.execute(sql);

To perform the Fulltext Search on a MySQL database through a python program, we need to execute the CREATE TABLE statement using the execute() function of the MySQL Connector/Python as follows −

fulltext_search_query = "SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')"
cursorObj.execute(fulltext_search_query)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf(''Connected successfully.
'');    
//creating a table films that stores fulltext.
$sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
//insert data
$q = "INSERT INTO FILMS (NAME, DIRECTOR) VALUES (''RRR'', ''The film RRR is directed by Rajamouli''), (''Bahubali'', ''The film Bahubali is directed by Rajamouli''),
      (''Avatar'', ''The film Avatar is directed by James cameron''), (''Robot'', ''The film Robot is directed by Shankar'')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records    
$s = "SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')";
if ($r = $mysqli->query($s)) {
    printf("Table Records: n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Name: %s, Director: %s", $row["ID"], $row["NAME"], $row["DIRECTOR"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Output

The output obtained is as shown below −

Table created successfully...!
Data inserted successfully...!
Table Records:
 ID: 1, Name: RRR, Director: The film RRR is directed by Rajamouli
 ID: 2, Name: Bahubali, Director: The film Bahubali is directed by Rajamouli  

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
  con.query(sql);

  //insert data into created table
  sql = `INSERT INTO FILMS (NAME, DIRECTOR)
  VALUES (''RRR'', ''The film RRR is directed by Rajamouli''),
  (''Bahubali'', ''The film Bahubali is directed by Rajamouli''),
  (''Avatar'', ''The film Avatar is directed by James cameron''),
  (''Robot'', ''The film Robot is directed by Shankar'')`;
  con.query(sql);

  //display the table details!...
  sql = `SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output obtained is as shown below −

[
  {
    ID: 1,
    NAME: ''RRR'',
    DIRECTOR: ''The film RRR is directed by Rajamouli''
  },
  {
    ID: 2,
    NAME: ''Bahubali'',
    DIRECTOR: ''The film Bahubali is directed by Rajamouli''
  }
]   

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class FulltextSearch {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //creating a table that takes fulltext column...!
         String sql = "CREATE TABLE FILMS (ID int auto_increment not null primary key, NAME varchar(50), DIRECTOR TEXT, FULLTEXT (NAME, DIRECTOR) )";
         statement.execute(sql);
         System.out.println("Table created successfully...!");

         //inserting data to the tables
         String insert = "INSERT INTO FILMS (NAME, DIRECTOR) VALUES (''RRR'', ''The film RRR is directed by Rajamouli''), (''Bahubali'', ''The film Bahubali is directed by Rajamouli'')," +
                 "(''Avatar'', ''The film Avatar is directed by James cameron''), (''Robot'', ''The film Robot is directed by Shankar'')";
         statement.execute(insert);
         System.out.println("Data inserted successfully...!");

         //displaying the table records...!
         ResultSet resultSet = statement.executeQuery("SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'')");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+ " "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
Table created successfully...!
Data inserted successfully...!
1 RRR The film RRR is directed by Rajamouli
2 Bahubali The film Bahubali is directed by Rajamouli  

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
fulltext_search_query = f"SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST (''Rajamouli'');"
cursorObj.execute(fulltext_search_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Full-text search results:")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Output

The output obtained is as shown below −

Full-text search results:
(1, ''RRR'', ''The film RRR is directed by Rajamouli'')
(2, ''Bahubali'', ''The film Bahubali is directed by Rajamouli'')

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user