MySQL - 布林全文搜尋

要透過 JavaScript 程式執行布林全文搜尋，我們需要使用 **mysql2** 庫的 **query()** 函式執行以下 SELECT 語句，如下所示：

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

要透過 Java 程式執行布林全文搜尋，我們需要使用 **JDBC** 函式 **executeQuery()** 執行 SELECT 語句，如下所示：

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

要透過 Python 程式執行布林全文搜尋，我們需要使用 **MySQL Connector/Python** 的 **execute()** 函式執行 SELECT 語句，如下所示：

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);
  });
});     

輸出

獲得的輸出如下所示：

[
  {
    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://: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);
      }
   }
}           

輸出

獲得的輸出如下所示：

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()

輸出

獲得的輸出如下所示：

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')