MySQL - 查詢擴充套件全文搜尋

要透過 JavaScript 程式執行查詢擴充套件全文搜尋，我們需要使用mysql2庫的query()函式執行 SELECT 語句，如下所示：

sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
con.query(sql);

要透過 Java 程式執行查詢擴充套件全文搜尋，我們需要使用JDBC函式executeQuery()執行 SELECT 語句，如下所示：

String sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
statement.executeQuery(sql);

要透過 Python 程式執行查詢擴充套件全文搜尋，我們需要使用MySQL Connector/Python的execute()函式執行 SELECT 語句，如下所示：

queryexpansionfulltext_search = 'SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS)  AGAINST ('RDBMS' WITH QUERY EXPANSION)'
cursorObj.execute(queryexpansionfulltext_search)

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

  //create a table...
  sql =
    "CREATE TABLE DBMS_TUTORIALS(TUTORIAL_TITLE VARCHAR(200), DESCRIPTIONS TEXT, FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS))";
  con.query(sql);

  //insert data
  sql = `INSERT INTO DBMS_TUTORIALS (TUTORIAL_TITLE , DESCRIPTIONS) VALUES 
  ('MySQL Tutorial', 'MySQL is an RDBMS that uses SQL to structure the data stored'), 
  ('ORACLE Tutorial', 'ORACLE is an RDBMS that uses SQL to structure the data stored'), 
  ('MySQL Security', 'MySQL Database can store sensitive data, so security is required'), 
  ('MySQL vs MariaDB', 'Comparing two databases...'), 
  ('JDBC Tutorial', 'In this Java-based database connectivity...')`;

  con.query(sql);

  //Using the Query Expansion Mode in Full-text search, try to search for records of DBMS_TUTORIALS relevant to data, with the keyword 'RDBMS'
  sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});               

輸出

獲得的輸出如下所示：

[
    {
      TUTORIAL_TITLE: 'ORACLE Tutorial',
      DESCRIPTIONS: 'ORACLE is an RDBMS that uses SQL to structure the data stored'
    },
    {
      TUTORIAL_TITLE: 'MySQL Tutorial',
      DESCRIPTIONS: 'MySQL is an RDBMS that uses SQL to structure the data stored'
    },
    {
      TUTORIAL_TITLE: 'MySQL Security',
      DESCRIPTIONS: 'MySQL Database can store sensitive data, so security is required'
    },
    {
      TUTORIAL_TITLE: 'MySQL vs MariaDB',
      DESCRIPTIONS: 'Comparing two databases...'
    },
    {
      TUTORIAL_TITLE: 'JDBC Tutorial',
      DESCRIPTIONS: 'In this Java-based database connectivity...'
    }
]

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

public class QeFSearch {
   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 DBMS_TUTORIALS(TUTORIAL_TITLE VARCHAR(200), DESCRIPTIONS TEXT, FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS))";
         statement.execute(sql);
         System.out.println("Table created successfully...!");

         //inserting data to the table
         String insert = "INSERT INTO DBMS_TUTORIALS (TUTORIAL_TITLE , DESCRIPTIONS) VALUES" + 
         "('MySQL Tutorial', 'MySQL is an RDBMS that uses SQL to structure the data stored')," + 
         "('ORACLE Tutorial', 'ORACLE is an RDBMS that uses SQL to structure the data stored')," + 
         "('MySQL Security', 'MySQL Database can store sensitive data, so security is required')," + 
         "('MySQL vs MariaDB', 'Comparing two databases...')," + 
         "('JDBC Tutorial', 'In this Java-based database connectivity...')";
         statement.execute(insert);
         System.out.println("Data inserted successfully...!");

         //Using the Query Expansion Mode in Full-text search, try to search for records of DBMS_TUTORIALS relevant to data, with the keyword 'RDBMS'...!
         ResultSet resultSet = statement.executeQuery("SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

輸出

獲得的輸出如下所示：

Connected successfully...!
Table created successfully...!
Data inserted successfully...!
ORACLE Tutorial ORACLE is an RDBMS that uses SQL to structure the data stored
MySQL Tutorial MySQL is an RDBMS that uses SQL to structure the data stored
MySQL Security MySQL Database can store sensitive data, so security is required
MySQL vs MariaDB Comparing two databases...
JDBC Tutorial in this Java-based database connectivity... 

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()
queryexpansionfulltext_search = '''
SELECT * FROM DBMS_TUTORIALS 
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) 
AGAINST ('RDBMS' WITH QUERY EXPANSION)
'''
cursorObj.execute(queryexpansionfulltext_search)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Query expansion Fulltext search results:")
for row in results:
    print(row)
cursorObj.close()
connection.close()

輸出

獲得的輸出如下所示：

Query expansion Fulltext search results:
('ORACLE Tutorial', 'ORACLE is an RDBMS that uses SQL to structure the data stored')
('MySQL Tutorial', 'MySQL is an RDBMS that uses SQL to structure the data stored')
('MySQL Security', 'MySQL Database can store sensitive data, so security is required')
('MySQL vs MariaDB', 'Comparing two databases...')
('JDBC Tutorial', 'In this Java-based database connectivity...')