Tìm kiếm toàn văn MySQL (full text search)

Tìm kiếm toàn văn bản MySQL cung cấp một cách để triển khai các kỹ thuật tìm kiếm nâng cao khác nhau như tìm kiếm ngôn ngữ tự nhiên, tìm kiếm văn bản Boolean và mở rộng truy vấn. Giả sử bạn có một cơ sở dữ liệu tên là product với các cột khác nhau như product_id, product_name, description và price. Giả sử bây giờ bạn muốn tìm kiếm các sản phẩm có “smartphone” trong tên hoặc mô tả của chúng. Dưới đây là truy vấn SQL tương tự:

SELECT *
FROM products
WHERE MATCH(product_name, description) 
AGAINST('smartphone' IN BOOLEAN MODE);

Chi tiết:

Hàm MATCH được sử dụng để thực hiện tìm kiếm toàn văn trên cột tên sản phẩm và mô tả. 
AGAINST chỉ định cụm từ tìm kiếm “smartphone” và ở chế độ boolean.
 
Tất cả các bạn đều đã tìm kiếm thứ gì đó trên Google, Bing hoặc Yahoo! và tất cả đều đã trải qua các trang web khác nhau cung cấp chức năng tìm kiếm. Đây là những ứng dụng phổ biến sử dụng FULL-TEXT.

Thực hiện

Toán tử LIKE hoặc biểu thức REGULAR bắt đầu được MySQL sử dụng để triển khai tìm kiếm một phần. Dựa trên mẫu trong LIKE hoặc tìm kiếm MySQL Biểu thức thông thường trong toàn bộ bảng.

Để tạo tìm kiếm toàn văn bản, bạn phải tạo chỉ mục văn bản đầy đủ trên mỗi cột bạn muốn lập chỉ mục. Trong MySQL nó được thực hiện thông qua từ khóa FULLTEXT. MySQL tạo tìm kiếm toàn văn bản từ dữ liệu văn bản và tra cứu chỉ mục này bằng thuật toán để xác định các hàng khớp với truy vấn tìm kiếm.

Nhưng nó có một số hạn chế khi cột văn bản lớn và bảng có số lượng hàng lớn.

Các tính năng của Tìm kiếm toàn văn bản MySQL

1. Tìm kiếm ngôn ngữ tự nhiên : Có nghĩa là nó có thể hiểu truy vấn của người dùng theo những cách giống con người hơn, có tính đến độ gần của từ, từ đồng nghĩa và từ dừng.
2. Kích thước chỉ mục vừa phải : Kích thước của chỉ mục FULL-TEXT tương đối nhỏ.
3. Tìm kiếm Boolean : Tìm kiếm toàn văn bản MySQL cũng hỗ trợ các toán tử boolean như “AND”, “OR” và “NOT” cho phép người dùng tạo các truy vấn tìm kiếm phức tạp hơn.
4. Giao diện giống SQL gốc : MySQL cung cấp câu lệnh giống SQL để thực hiện tìm kiếm toàn văn bản.
5. Khả năng truy vấn phong phú

Tạo chỉ mục FULL-TEXT

Nếu bạn muốn thực hiện tìm kiếm FULL-TEXT trong một cột của bảng, bạn phải lập chỉ mục dữ liệu của nó. Bất cứ khi nào dữ liệu của cột thay đổi, MySQL sẽ tạo lại chỉ mục toàn văn bản. MySQL hỗ trợ lập chỉ mục và lập chỉ mục lại dữ liệu một cách tự động cho các cột kích hoạt toàn văn.

Các cách khác nhau để tạo chỉ mục Tìm kiếm FULLTEXT:

Sử dụng  câu lệnh CREATE TABLE

CREATE TABLE table_name(
          column_list,
          …,
          FULLTEXT (column1, column2, …)
);

CREATE TABLE posts (
    id INT NOT NULL AUTO_INCREMENT, 
    title VARCHAR(255) NOT NULL,
    body TEXT,
    PRIMARY KEY (id),
    FULLTEXT KEY(body)
);

INSERT INTO posts (title,body) VALUES
       ('MySQL Tutorial','DBMS stands for DataBase ...'),
       ('How To Use MySQL Well','After you went through a ...'),
       ('Optimizing MySQL','In this tutorial, we show ...'),
       ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
       ('MySQL vs. YourSQL','In the following database comparison ...'),
       ('MySQL Security','When configured properly, MySQL ...');
       
 SELECT * FROM posts
     WHERE MATCH (title,body)
     AGAINST ('database' IN NATURAL LANGUAGE MODE);

     
Sử dụng câu lệnh ALTER TABLE

CREATE TABLE posts (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT
     ) ENGINE=InnoDB;
     
ALTER TABLE Books
    ADD FULLTEXT INDEX FT_Books_Title_Description (title, body)
    WITH STOPLIST = SYSTEM;
    
SELECT * FROM posts
     WHERE MATCH (title,body)
     AGAINST ('database' IN NATURAL LANGUAGE MODE);

Sử dụng câu lệnh CREATE INDEX

CREATE TABLE posts (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT
     ) ENGINE=InnoDB;
     
CREATE FULLTEXT INDEX FTIndexName
        ON posts (title, body);
    
INSERT INTO posts (title,body) VALUES
       ('MySQL Tutorial','DBMS stands for DataBase ...'),
       ('How To Use MySQL Well','After you went through a ...'),
       ('Optimizing MySQL','In this tutorial, we show ...'),
       ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
       ('MySQL vs. YourSQL','In the following database comparison ...'),
       ('MySQL Security','When configured properly, MySQL ...');
       
SELECT * FROM posts
     WHERE MATCH (title,body)
     AGAINST ('database' IN NATURAL LANGUAGE MODE);

Xóa chỉ mục FULLTEXT

ALTER TABLE table_name

DROP INDEX index_name;

ALTER TABLE offices
DROP INDEX address;

Phần kết luận

Tìm kiếm toàn văn bản là một tính năng tìm kiếm mạnh mẽ của MySQL cho phép tìm kiếm nhanh chóng và hiệu quả trong cơ sở dữ liệu. Người dùng có thể tìm kiếm một từ hoặc cụm từ trong nhiều cột của bảng và cũng hoạt động trong trường hợp văn bản không chính xác. Nó cải thiện đáng kể hiệu suất và độ chính xác của truy vấn tìm kiếm dựa trên văn bản trong SQL.