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://127.0.0.1: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')