MySQL - 查询扩展全文搜索

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

在像 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 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...

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

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

输出

输出结果为 −

TUTORIAL_TITLE	DESCRIPTIONS
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...

自然语言模式

在上面获得的结果集中，所有教程记录都与数据库有关，因此查询会检索所有按相关性排序的记录。

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

输出

输出结果为 −

TUTORIAL_TITLE	DESCRIPTIONS
MySQL Security	MySQL Database can store sensitive data, so security is required
JDBC Tutorial	In this Java-based database connectivity...
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 vs MariaDB	Comparing two databases...

在此结果集中，即使搜索关键字是"安全"，实际的安全相关教程也只是"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...!
");
}
//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...!
");
}
//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: 
");
    while ($row = $r->fetch_assoc()) {
        printf("Tutorial_title: %s, Descriptions: %s", $row["TUTORIAL_TITLE"], $row["DESCRIPTIONS"]);
        printf("
");
    }
} 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",
}); //连接到 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://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 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
# 建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# 创建游标对象
cursorObj = connection.cursor()
queryexpansionfulltext_search = '''
SELECT * FROM DBMS_TUTORIALS 
WHERE MATCH(TUTORIAL_TITLE, DESCRIPTIONS) 
AGAINST ('RDBMS' WITH QUERY EXPANSION)
'''
cursorObj.execute(queryexpansionfulltext_search)
# 获取所有结果
results = cursorObj.fetchall()
# 显示结果
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...')

MySQL 8 教程

MySQL 8 数据库

MySQL 8 用户

MySQL 8 表

MySQL 8 查询

MySQL 8 视图

MySQL 8 索引

MySQL 运算符和子句

MySQL 连接

MySQL 键

MySQL 触发器

MySQL 8 数据类型

MySQL 正则表达式

MySQL 全文搜索

MySQL8 函数和运算符

MySQL 8 其他概念

MySQL 8 实用资源

MySQL - 查询扩展全文搜索

查询扩展全文搜索

示例

输出

自然语言模式

输出

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

语法

示例

输出

输出

输出

输出

颜色选择器

读后有收获微信请站长喝咖啡

错误报告

您的建议:

感谢您的帮助！