MySQL - 全文搜尋

要在 JavaScript 程式中對 MySQL 資料庫執行全文搜尋，我們需要使用 mysql2 庫的 query() 函式執行 CREATE TABLE 語句，如下所示：

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

要在 Java 程式中對 MySQL 資料庫執行全文搜尋，我們需要使用 JDBC 函式 execute() 執行 CREATE TABLE 語句，如下所示：

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

要在 Python 程式中對 MySQL 資料庫執行全文搜尋，我們需要使用 MySQL Connector/Python 的 execute() 函式執行 CREATE TABLE 語句，如下所示：

fulltext_search_query = "SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST ('Rajamouli')"
cursorObj.execute(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 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);
  });
});  

輸出

獲得的輸出如下所示：

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

輸出

獲得的輸出如下所示：

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

輸出

獲得的輸出如下所示：

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