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",
}); //连接到 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);
 //将数据插入到创建的表中
  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://localhost: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...!");
        //向表中插入数据
         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
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
fulltext_search_query = f"SELECT * FROM FILMS WHERE MATCH (NAME, DIRECTOR) AGAINST ('Rajamouli');"
cursorObj.execute(fulltext_search_query)
# 获取所有结果
results = cursorObj.fetchall()
# 显示结果
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')