MySQL - Boolean Full-Text Search

To perform the Boolean Full-Text Search through a JavaScript program, we need to execute the following SELECT statement using the query() function of mysql2 library as follows −

sql = `SELECT * FROM articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST('+data -sets' IN BOOLEAN MODE)`;
con.query(sql);

To perform the Boolean Full-Text Search through a Java program, we need to execute the SELECT statement using the JDBC function executeQuery() as follows −

String sql = "SELECT * FROM articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST('+data -sets' IN BOOLEAN MODE)";
statement.executeQuery(sql);

To perform the Boolean Full-Text Search through a python program, we need to execute the SELECT statement using the execute() function of the MySQL Connector/Python as follows −

boolean_fulltext_search_query = 'select * from articles where MATCH (ARTICLE_TITLE, DESCRIPTION) AGAINST('data' IN BOOLEAN MODE)'
cursorObj.execute(boolean_fulltext_search_query)

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 Articles (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, ARTICLE_TITLE VARCHAR(100), DESCRIPTION TEXT, FULLTEXT (ARTICLE_TITLE, DESCRIPTION) )";
  con.query(sql);

  //insert data into created table
  sql = `INSERT INTO Articles (ARTICLE_TITLE, DESCRIPTION) VALUES
  ('MySQL Tutorial', 'MySQL is a relational database system that uses SQL to structure data stored'),
  ('Java Tutorial', 'Java is an object-oriented and platform-independent programming languag'),
  ('Hadoop Tutorial', 'Hadoop is framework that is used to process large sets of data'),
  ('Big Data Tutorial', 'Big Data refers to data that has wider variety of data sets in larger numbers'),
  ('JDBC Tutorial', 'JDBC is a Java based technology used for database connectivity')`;
  con.query(sql);

  //display the table details!...
  sql = `SELECT * FROM articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST('+data -sets' IN BOOLEAN MODE)`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});     

Output

The output obtained is as shown below −

[
  {
    id: 1,
    ARTICLE_TITLE: 'MySQL Tutorial',
    DESCRIPTION: 'MySQL is a relational database system that uses SQL to structure data stored'
  }
]  

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

public class BooleanFulltextSearch {
   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 Articles (ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY, ARTICLE_TITLE VARCHAR(100), DESCRIPTION TEXT, FULLTEXT (ARTICLE_TITLE, DESCRIPTION) )";
         statement.execute(sql);
         System.out.println("Table created successfully...!");

         //inserting data to the table
         String insert = "INSERT INTO Articles (ARTICLE_TITLE, DESCRIPTION) VALUES" + 
         "('MySQL Tutorial', 'MySQL is a relational database system that uses SQL to structure data stored')," + 
         "('Java Tutorial', 'Java is an object-oriented and platform-independent programming languag')," + 
         "('Hadoop Tutorial', 'Hadoop is framework that is used to process large sets of data')," + 
         "('Big Data Tutorial', 'Big Data refers to data that has wider variety of data sets in larger numbers')," + 
         "('JDBC Tutorial', 'JDBC is a Java based technology used for database connectivity')";
         statement.execute(insert);
         System.out.println("Data inserted successfully...!");

         //displaying the fulltext records in the boolean mode:
         ResultSet resultSet = statement.executeQuery("SELECT * FROM articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST('+data -sets' IN BOOLEAN MODE)");
         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 MySQL Tutorial MySQL is a relational database system that uses SQL to structure data stored

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()
boolean_fulltext_search_query = f"select * from articles where MATCH (ARTICLE_TITLE, DESCRIPTION) AGAINST('data' IN BOOLEAN MODE)"
cursorObj.execute(boolean_fulltext_search_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Boolean Fulltext search results:")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Output

The output obtained is as shown below −

Boolean Fulltext search results:
(4, 'Big Data Tutorial', 'Big Data refers to data that has wider variety of data sets in larger numbers')
(1, 'MySQL Tutorial', 'MySQL is a relational database system that uses SQL to structure data stored')
(3, 'Hadoop Tutorial', 'Hadoop is framework that is used to process large sets of data')