Problem: Giả sử bạn không nhớ chính xác cả câu cần tìm kiếm mà chỉ nhớ một số từ trong câu đó thôi. Làm cách nào để search được bản ghi đó ?
Ví dụ cụ thể: Mình có 1 bảng articles
Nếu 1 chàng trai nào đó muốn tìm kiếm những bài viết liên quan tới các cô nàng developer. Họ lên mạng search "xinh gái developer" thì làm thế nào để chàng trai
có thể thấy được bài viết liên quan tới Hằng xinh.
Theo cách truyền thống, chúng ta hoàn toàn có thể sử dụng search like trong trường hợp này.
Tuy vẫn tìm kiếm được kết quả, nhưng việc sử dụng theo cách search vẫn để lại một số hạn chế như:
- Khi không đánh index thì tốc độ tìm kiếm chậm.
- Hiệu suất không cao.
- Xảy ra tình trạng overload nếu dữ liệu quá dài hoặc quá nhiều
Để giải quyết vấn đề trên MySQL đã hỗ trợ thêm MySQL Full Text Search
I. Full Text Search là gì ?
- Kĩ thuật tìm kiếm toàn văn cho phép tìm kiếm các mẩu thông tin khớp với một chuỗi trên một hay một số cột nhất định.
- MySQL chỉ hỗ trợ FULLTEXT cho các kiểu dữ liệu CHAR, VARCHAR hoặc TEXT, kiểu lưu trữ table phải là MyISAM hoặc InnoDB [từ phiên bản 5.6 mới có]
1.2 Inverted
index
Điều làm nên sự khác biệt giữa Full Text Search và các kĩ thuật search thông thường chính là Inverted index
- Là kĩ thuật đánh index theo đơn vị term
- Nhằm mục đích map giữa các term với các bản ghi chứa term đó.
Vậy việc tạo index theo term như trên có lợi thế nào?
Gỉa sử bạn có 3 dòng dữ liệu:
D1 = "This is first document"
D2 = "This is second one"
D3 = "One two"
Inverted Index của 3 dòng đó sẽ được lưu dưới dạng
như sau:
"this" => {D1, D2} "second" => {D2}
"is" => {D1, D2} "one" => {D2, D3}
"first" => {D1} "two" => {D3}
"document" => {D1}
Khi tìm kiếm cụm từ "This is first", thay vì phải tìm kiếm cụm từ này trong toàn bộ các dòng, thì chỉ cần tìm kiếm các từ này ở {D1, D2}
Tóm lại, bạn cần lưu ý những vấn đề sau khi làm việc với full-text search trong MySQL:
- Nếu dùng InnoDB thì độ dài tối thiểu cần tìm là
3.
- MyISAM thì độ dài tối thiểu là 4.
Ví dụ bạn tìm từ "and" hoặc "I" thì mặc định MySQL sẽ xác định đó là những từ vô nghĩa.Vì trong tiếng Anh những từ có 3 chữ cái đều là vô nghĩa. Tuy nhiên, bạn có thể thay đổi độ dài này bằng cách mở file /etc/mysql/my.cnf và thực hiện thay đổi giá trị:
- Với InnoDB
+ innodb_ft_min_token_size [độ dài tối thiểu]
+ innodb_ft_max_token_size [độ dài tối đa] - Với MyISAM
+ ft_min_word_len [độ dài tối thiểu]
+ ft_max_word_len [độ dài tối đa]
Vậy kiểu lưu trữ table MyISAM và InnoDB khác nhau như thế nào ?
II. Cách sử dụng Full Text
Search
2.1 Tạo Full Text Search ngay lúc tạo bảng Create Table
CREATE TABLE table_name[
column_list,
...,
FULLTEXT [column1,column2,..]
];2.2 Tạo Full Text Search trong lệnh Alter Table
ALTER TABLE table_name
ADD FULLTEXT[column_name1, column_name2,…]2.3 Tạo Full Text Search bằng CREATE INDEX trong MySQL
CREATE FULLTEXT INDEX index_name
ON table_name[idx_column_name,...]2.4 Xóa Index Full Text Search
ALTER TABLE table_name
DROP INDEX index_name;2.5 Cách sử dụng Full Text Search
CREATE TABLE articles [
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR[200],
body TEXT,
FULLTEXT [title,body]
] ENGINE=InnoDB;INSERT INTO articles [title,body] VALUES
['MySQL Tutorial','This database tutorial ...'],
["How To Use MySQL",'After you went through a ...'],
['Optimizing Your Database','In this database tutorial ...'],
['MySQL vs. YourSQL','When comparing databases ...'],
['MySQL Security','When configured properly, MySQL ...'],
['Database, Database, Database','database database database'],
['1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'],
['MySQL Full-Text Indexes', 'MySQL fulltext indexes use a ..'];SELECT * FROM articles
where MATCH [title,body]
AGAINST ['database Tutorial']Trong đó:
- Hàm MATCH chỉ ra sẽ tìm kiếm trên cột nào
- Hàm AGAINST chỉ ra biểu thức
tìm kiếm [hay chính là cụm từ tìm kiếm theo ngôn ngữ tự nhiên mà người dùng nhập vào]
Kết qủa :
III. Full Text Search functions
3.1 Natural Language Full-Text Searches
Để thực hiện tìm kiếm theo ngôn ngữ tự nhiên, chúng ta sử dụng hai hàm MATCH[] và AGAINST[] . Hàm AGAINST[] theo mặc định sẽ nằm ở chế độ IN NATURAL LANGUAGE MODE.
Chế độ sort mặc định theo
mức độ phù hợp
Được tính theo công thức : w = [log[dtf]+1]/sumdtf * U/[1+0.0115*U] log[[N-nf]/nf]
Giải thích về công thức như sau: Nếu 1 từ khóa xuất hiện nhiều lần trong 1 bản ghi thì điểm weight của từ khóa đó sẽ tăng lên và ngược lại nếu từ khóa xuất hiện trong nhiều bản ghi thì điểm weight sẽ bị giảm đi.
3.2 Boolean Full-Text Searches
Theo tìm kiếm tự nhiên thì trong văn bản chỉ cần xuất hiện một trong số những từ mà ta đặt nó ở đầu vào là sẽ trả kết quả
về. Tuy nhiên có một số trường hợp mình muốn phải xuất hiện ít nhất 2 từ nào đó thì lúc này phải sử dụng các chế độ MODE.
Để thực hiện tìm kiếm toàn văn bản trong chế độ Boolean, bạn sử dụng công cụ sửa đổi IN BOOLEAN MODE trong biểu thức AGAINST.
Ví dụ : Tìm kiếm bài viết bắt buộc phải có cả hai từ khóa mysqldatabase
SELECT
*
FROM
articles
WHERE
MATCH[title, body] AGAINST[
'+mysql +database' IN BOOLEAN MODE
]Các toán tử trong Boolean Full Text Searches
Các ví dụ sau minh họa cách sử dụng toán tử boolean trong truy vấn tìm kiếm:
Để tìm kiếm các hàng có chứa ít nhất một trong hai
từ: “mysql” hoặc “tutorial”
'mysql tutorial'Để tìm kiếm các hàng có chứa cả hai từ: “mysql” và “tutorial”
'+mysql +tutorial'Để tìm kiếm các hàng có chứa từ “mysql”, nhưng đặt thứ hạng cao hơn cho các hàng có chứa “tutorial”:
'+mysql tutorial'Để tìm kiếm các hàng có chứa từ “mysql” nhưng không chứa từ “tutorial”
'+mysql -tutorial'Để tìm kiếm các hàng có chứa từ “mysql” và xếp hạng hàng thấp hơn nếu nó chứa từ “tutorial”.
'+mysql ~tutorial'Để tìm kiếm các hàng có chứa các từ “mysql” và “tutorial” hoặc “mysql” và “training” theo bất kỳ thứ tự nào, nhưng hãy đặt các hàng có chứa “mysql tutorial” cao hơn “mysql training”.
'+mysql +[>tutorial