MySQL - 自然語言全文搜索

Hello, aspiring database enthusiasts! Today, we're going to embark on an exciting journey into the world of MySQL's Natural Language Fulltext Search. Don't worry if you're new to programming; I'll guide you through this topic step-by-step, just like I've done for countless students over my years of teaching. So, let's dive in!

What is Natural Language Full-text Search?

Imagine you're looking for a book in a massive library. Instead of scanning every single book, wouldn't it be great if you could just describe what you're looking for and have the librarian find it for you? That's essentially what Natural Language Full-text Search does for databases!

Natural Language Full-text Search is a powerful feature in MySQL that allows you to search for records based on their relevance to a given text query. It's like having a super-smart librarian for your database!

How Does It Work?

1. MySQL creates an index of all the words in the specified columns.
2. When you search, it looks for records containing those words.
3. It then ranks the results based on how relevant they are to your search query.

Let's see this in action with some code examples!

CREATE TABLE books (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
description TEXT,
FULLTEXT(title, description)
);

INSERT INTO books (title, description) VALUES
('The MySQL Handbook', 'A comprehensive guide to MySQL database management'),
('SQL for Beginners', 'Learn SQL from scratch with easy-to-follow examples'),
('Database Design Mastery', 'Advanced techniques for efficient database design');

SELECT * FROM books
WHERE MATCH(title, description) AGAINST('MySQL guide');

In this example, we:

1. Create a table called 'books' with a FULLTEXT index on 'title' and 'description'.
2. Insert some sample data.
3. Perform a Natural Language Full-text Search for "MySQL guide".

MySQL will return results ranked by relevance, likely with "The MySQL Handbook" at the top!

Stop Words in a Search

Now, let's talk about a concept called "stop words". Imagine if every time you spoke, you counted "the", "a", "an", etc. It would be exhausting, right? MySQL feels the same way!

Stop words are common words that MySQL ignores during a full-text search to save time and improve relevance. These typically include:

Stop Words
a, an, and
are, as, at
be, but, by
for, if, in
into, is, it
no, not, of
on, or, such
that, the, their
then, there, these
they, this, to
was, will, with

How to Handle Stop Words

By default, MySQL uses its own list of stop words. However, you can modify this behavior:

1. To see the current list of stop words:

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

2. To disable stop words entirely:

SET GLOBAL innodb_ft_enable_stopword = OFF;

3. To use a custom list of stop words:

SET GLOBAL innodb_ft_server_stopword_table = 'database_name/table_name';

Remember, handling stop words can significantly impact your search results, so use these options wisely!

Natural Language Full-text Search Using a Client Program

Now that we understand the basics, let's see how we can implement Natural Language Full-text Search in a real-world scenario using a client program. For this example, we'll use Python with the MySQL Connector library.

First, make sure you have MySQL Connector installed:

pip install mysql-connector-python

Now, let's create a simple Python script:

import mysql.connector

# Connect to the MySQL database
db = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="your_database"
)

cursor = db.cursor()

# Function to perform full-text search
def fulltext_search(search_term):
query = "SELECT * FROM books WHERE MATCH(title, description) AGAINST(%s IN NATURAL LANGUAGE MODE)"
cursor.execute(query, (search_term,))
results = cursor.fetchall()
return results

# Example usage
search_results = fulltext_search("MySQL guide")

for result in search_results:
print(f"ID: {result[0]}, Title: {result[1]}, Description: {result[2]}")

# Close the connection
db.close()

Let's break this down:

1. We import the MySQL Connector and establish a connection to our database.
2. We define a function fulltext_search that takes a search term as input.
3. Inside the function, we construct and execute a MySQL query using Natural Language Full-text Search.
4. We fetch and return the results.
5. Finally, we demonstrate how to use this function and print the results.

This script allows you to easily perform Natural Language Full-text Searches from within your Python application!

Conclusion

And there you have it, folks! We've journeyed through the fascinating world of MySQL's Natural Language Full-text Search. From understanding its basic concepts to implementing it in a real-world scenario, you now have the tools to make your database searches more efficient and relevant.

Remember, like learning any new language, mastering database queries takes practice. So don't be discouraged if it doesn't click immediately. Keep experimenting, and soon you'll be querying databases like a pro!

As I always tell my students, databases are like well-organized libraries, and you're learning to be the most efficient librarian in town. Happy querying!

Credits: Image by storyset