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