MySQL - 自然语言全文搜索

要通过 JavaScript 程序执行自然语言全文搜索，我们需要使用mysql2库的query()函数执行以下 SELECT 语句：

sql = `SELECT * FROM Articles  WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST ('data set' IN NATURAL LANGUAGE MODE)`;
con.query(sql);

要通过 Java 程序执行自然语言全文搜索，我们需要使用JDBC函数executeQuery()执行 SELECT 语句：

String sql = "SELECT * FROM Articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)  AGAINST ('data set' IN NATURAL LANGUAGE MODE)";
statement.executeQuery(sql);

要通过 Python 程序执行自然语言全文搜索，我们需要使用MySQL Connector/Python的execute()函数执行 SELECT 语句：

natural_language_search_query = 'SELECT * FROM Articles WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) AGAINST ('data set' IN NATURAL LANGUAGE MODE)'
cursorObj.execute(natural_language_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);

  //display the table details!...
  sql = `SELECT * FROM Articles  WHERE MATCH(ARTICLE_TITLE, DESCRIPTION)  AGAINST ('data set' IN NATURAL LANGUAGE MODE)`;
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});    

输出

获得的输出如下所示：

We get the following output, after executing the above NodeJs Program.
[
  {
    id: 4,
    ARTICLE_TITLE: 'Big Data Tutorial',
    DESCRIPTION: 'Big Data refers to data that has wider variety of data sets in larger numbers'
  },
  {
    id: 1,
    ARTICLE_TITLE: 'MySQL Tutorial',
    DESCRIPTION: 'MySQL is a relational database system that uses SQL to structure data stored'
  },
  {
    id: 3,
    ARTICLE_TITLE: 'Hadoop Tutorial',
    DESCRIPTION: 'Hadoop is framework that is used to process large sets of data'
  }
]  

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

public class NaturalLanguageSearch {
   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...!");

         //displaying the fulltext records in the Natural language mode:
         ResultSet resultSet = statement.executeQuery("SELECT * FROM Articles WHERE MATCH(ARTICLE_TITLE, descriptions)  AGAINST ('data set' IN NATURAL LANGUAGE 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...!
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

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()
natural_language_search_query = '''
SELECT * FROM Articles 
WHERE MATCH(ARTICLE_TITLE, DESCRIPTION) 
AGAINST ('data set' IN NATURAL LANGUAGE MODE)
'''
cursorObj.execute(natural_language_search_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("NATURAL LANGUAGE search results:")
for row in results:
   print(row)
cursorObj.close()
connection.close()            

输出

获得的输出如下所示：

NATURAL LANGUAGE 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')