MySQL - 查询扩展全文搜索

Table of content
上一页
下一页

在像 MySQL 这样的关系数据库中，全文搜索是一种用于检索可能并不完全匹配搜索关键词的结果集的技术。这种类型的搜索在搜索关键词与用户期望的结果不匹配的情况下非常有用。因此，这种搜索技术旨在提高搜索相关性，以减少搜索查询和搜索结果之间的准确性差距。因此，搜索结果按与搜索关键词的相关性从高到低显示。

全文搜索使用三种搜索模式：

  • 自然语言模式

  • 查询扩展模式

  • 布尔模式

查询扩展全文搜索

用户搜索时总是基于其有限的知识。因此，在某些情况下，搜索关键词太短而无法进行适当的搜索。这就是盲目扩展搜索技术发挥作用的地方。

盲目扩展搜索，也称为自动相关反馈，用于根据与原始关键词密切相关的其他关键词来扩展搜索结果。它使用“WITH QUERY EXPANSION”搜索短语启用。

在这种情况下，搜索将执行两次，按照以下步骤：

步骤 1 - 首先搜索所有匹配给定搜索关键词的行。

步骤 2 - 然后检查这些获得的行中与原始关键词相关的词。

步骤 3 - 最后，根据这些相关词而不是用户指定的原始关键词再次搜索这些行。

要在数据库表上执行查询扩展全文搜索，必须在 AGAINST() 函数中指定 WITH QUERY EXPANSION 或 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 搜索修饰符。

示例

让我们在下面的示例中了解如何在数据库表上执行查询扩展全文搜索。

为此，我们将首先创建一个名为 DBMS_TUTORIALS 的表，其中包含文章的标题和描述。FULLTEXT 索引应用于文本列 TUTORIAL_TITLE 和 DESCRIPTIONS，如下所示：

CREATE TABLE DBMS_TUTORIALS(
   TUTORIAL_TITLE VARCHAR(200), 
   DESCRIPTIONS TEXT, 
   FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS)
);

现在，让我们使用以下查询将有关教程的详细信息（例如其标题和描述）插入此表：

INSERT INTO DBMS_TUTORIALS 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...');

表创建如下：

TUTORIAL_TITLE DESCRIPTIONS
MySQL 教程 MySQL 是一个使用 SQL 来组织存储数据的 RDBMS
ORACLE 教程 ORACLE 是一个使用 SQL 来组织存储数据的 RDBMS
MySQL 安全性 MySQL 数据库可以存储敏感数据，因此需要安全性
MySQL vs MariaDB 比较两个数据库……
JDBC 教程 在这个基于 Java 的数据库连接中……

使用全文搜索中的查询扩展模式，我们搜索与数据相关的文章记录，关键词为“RDBMS”

SELECT * FROM DBMS_TUTORIALS 
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) 
AGAINST ('RDBMS' WITH QUERY EXPANSION);

输出

获得的输出如下：

TUTORIAL_TITLE DESCRIPTIONS
ORACLE 教程 ORACLE 是一个使用 SQL 来组织存储数据的 RDBMS
MySQL 教程 MySQL 是一个使用 SQL 来组织存储数据的 RDBMS
MySQL 安全性 MySQL 数据库可以存储敏感数据，因此需要安全性
MySQL vs MariaDB 比较两个数据库……
JDBC 教程 在这个基于 Java 的数据库连接中……

IN NATURAL LANGUAGE MODE

在上表获得的结果集中，所有教程记录都与数据库相关，这就是查询根据相关性检索所有记录的原因。

SELECT * FROM DBMS_TUTORIALS 
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) 
AGAINST ('Security' IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION);

输出

获得的输出如下：

TUTORIAL_TITLE DESCRIPTIONS
MySQL 安全性 MySQL 数据库可以存储敏感数据，因此需要安全性
JDBC 教程 在这个基于 Java 的数据库连接中……
MySQL 教程 MySQL 是一个使用 SQL 来组织存储数据的 RDBMS
ORACLE 教程 ORACLE 是一个使用 SQL 来组织存储数据的 RDBMS
MySQL vs MariaDB 比较两个数据库……

在这个结果集中，即使搜索关键词是“安全性”，实际与安全性相关的教程只有“MySQL 安全性”和“JDBC 教程”，因此它们首先被检索。这些记录之后是作为扩展查询的数据库相关记录。

使用客户端程序进行查询扩展全文搜索

我们也可以使用客户端程序执行查询扩展全文搜索操作。

语法

PHP NodeJS Java Python

要通过 PHP 程序执行查询扩展全文搜索，我们需要使用mysqli函数query()执行 SELECT 语句，如下所示：

$sql = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
$mysqli->query($sql);

要通过 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)

示例

以下是程序：

PHP NodeJS Java Python
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
// printf('Connected successfully.
');
/*CREATE Table*/
$sql = "CREATE TABLE DBMS_TUTORIALS(TUTORIAL_TITLE VARCHAR(200), DESCRIPTIONS TEXT, FULLTEXT(TUTORIAL_TITLE, DESCRIPTIONS))";
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!\n");
}
//insert data
$q = "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...')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!\n");
}
//Using the Query Expansion Mode in Full-text search, try to search for records of DBMS_TUTORIALS relevant to data, with the keyword 'RDBMS'
$s = "SELECT * FROM DBMS_TUTORIALS WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) AGAINST ('RDBMS' WITH QUERY EXPANSION)";
if ($r = $mysqli->query($s)) {
    printf("Table Records: \n");
    while ($row = $r->fetch_assoc()) {
        printf("Tutorial_title: %s, Descriptions: %s", $row["TUTORIAL_TITLE"], $row["DESCRIPTIONS"]);
        printf("\n");
    }
} else {
    printf('Failed');
}
$mysqli->close();

输出

获得的输出如下所示：

Table created successfully...!
Data inserted successfully...!
Table Records:
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...

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://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 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...')
打印页面
上一篇
下一篇
广告