Hướng dẫn search in database mysql - tìm kiếm trong cơ sở dữ liệu mysql

Giới thiệu

Full-text search, hoặc FTS, là một kỹ thuật được sử dụng bởi các công cụ tìm kiếm để tìm kết quả trong một cơ sở dữ liệu. Bạn có thể sử dụng nó để kết nối các kết quả tìm kiếm trên các trang web như cửa hàng, công cụ tìm kiếm, báo chí và nhiều hơn nữa.

Cụ thể hơn, FTS lấy ra các tài liệu không khớp hoàn toàn với tiêu chí tìm kiếm. Tài liệu là các thực thể cơ sở dữ liệu chứa dữ liệu văn bản. Điều này có nghĩa là khi người dùng tìm kiếm "mèo và chó", một ứng dụng được hỗ trợ bởi FTS có thể trả lại các kết quả chứa các từ riêng biệt (chỉ "mèo" hoặc "chó"), chứa các từ theo một thứ tự khác ("chó và mèo"), hoặc chứa các biến thể của từ ("cat" hoặc "dog"). Điều này cho phép ứng dụng một lợi thế trong việc đoán xem người dùng có ý nghĩa gì và trả lại các kết quả có liên quan nhanh hơn.

Về mặt kỹ thuật, các hệ thống quản lý cơ sở dữ liệu (DBMS) như MySQL thường cho phép tra cứu một phần văn bản bằng các mệnh đề LIKE. Tuy nhiên, các yêu cầu này có xu hướng kém hiệu quả hơn trên các bộ dữ liệu lớn. Chúng cũng bị hạn chế để khớp chính xác đầu vào của người dùng, có nghĩa là một truy vấn có thể không cho kết quả ngay cả khi có các tài liệu có thông tin liên quan.

Sử dụng FTS, bạn có thể xây dựng một công cụ tìm kiếm văn bản mạnh hơn mà không giới thiệu thêm phụ thuộc vào các công cụ tiên tiến hơn. Trong hướng dẫn này, bạn sẽ sử dụng MySQL 5.6 để truy vấn cơ sở dữ liệu bằng cách sử dụng tìm kiếm toàn văn, sau đó định lượng các kết quả theo sự liên quan của chúng với đầu vào tìm kiếm và chỉ hiển thị các kết quả phù hợp nhất.

Bước 1 - Tạo dữ liệu thử nghiệm

Để thử Full-text search, chúng ta cần một số dữ liệu. Trong bước này, chúng tôi sẽ tạo một cơ sở dữ liệu gọi là testdb với một bảng được gọi là news, chúng tôi sẽ điền vào một số dữ liệu ví dụ đại diện cho các bài viết từ trang tổng hợp tin tức giả tưởng.

Lưu ý: Nếu bạn có bảng của riêng mình với dữ liệu văn bản mà bạn muốn sử dụng thay thế, bạn có thể bỏ qua Bước 2 và thay thế thích hợp trong khi theo dõi.

Đầu tiên, truy cập vào bảng điều khiển MySQL. Bạn sẽ được nhắc nhập mật khẩu gốc mà bạn đặt khi cài đặt MySQL. mysql -u root -p Một khi bạn đã kết nối, promptcủa bạn sẽ thay đổi thành mysql>. Tiếp theo, tạo một cơ sở dữ liệu mới gọi là testdb. Cơ sở dữ liệu này sẽ chứa dữ liệu thử nghiệm. CREATE DATABASE testdb; Chuyển sang sử dụng cơ sở dữ liệu testdb theo mặc định vì vậy bạn sẽ không phải chỉ định tên của cơ sở dữ liệu để tạo hoặc cập nhật nội dung trong đó. USE testdb; Tiếp theo, tạo một bảng trong cơ sở dữ liệu news với các cột cho một bài báo ví dụ của nhà tổng hợp tin tức.

CREATE TABLE news (
   id INT NOT NULL AUTO_INCREMENT,
   title TEXT NOT NULL,
   content TEXT NOT NULL,
   author TEXT NOT NULL,

   PRIMARY KEY (id)
);
INSERT INTO news (id, title, content, author) VALUES 
    (1, 'Pacific Northwest high-speed rail line', 'Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.', 'Greg'),
    (2, 'Hitting the beach was voted the best part of life in the region', 'Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.', 'Ethan'),
    (3, 'Machine Learning from scratch', 'Bare bones implementations of some of the foundational models and algorithms.', 'Jo');

Bây giờ chúng ta có một số dữ liệu, chúng ta có thể bắt đầu viết các truy vấn để tìm kiếm dữ liệu bằng FTS.

Bước 2 - Tạo chỉ mục FTS và sử dụng chức năng FTS

Hãy làm một chỉ mục cho các cột văn bản mà chúng ta có để chúng ta có thể sử dụng FTS.

Để thực hiện việc này, chúng ta sẽ sử dụng lệnh MySQL độc quyền gọi là Full Full. Lệnh này nói với MySQL để đặt tất cả các trường chúng ta muốn có thể tìm kiếm với FTS thành một chỉ mục nội bộ.

ALTER TABLE news ADD FULLTEXT (title, content, author);

Điều này hoạt động bằng cách kết hợp tất cả các cột văn bản và sanitizing chúng (ví dụ như loại bỏ các dấu chấm câu và làm cho chữ hoa in thường). Bây giờ chỉ mục này được tạo ra, nó sẽ được cập nhật bởi bất kỳ truy vấn SQL nào thay đổi nội dung của bảng mã nguồn.

Tiếp theo, thử thực hiện tìm kiếm toàn văn cho "Seattle beach" bằng cách sử dụng hàm MATCH () AGAINST ().

Output
*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
*************************** 2. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
2 rows in set (0.00 sec)

Phần MATCH () của lệnh chỉ định tập hợp các cột được lập chỉ mục sử dụng FTS; nó phải phù hợp với danh sách cột bạn đã sử dụng để tạo chỉ mục. Phần AGAINST () chỉ định từ nào chúng tôi đang thực hiện tìm kiếm toàn văn, đó là "Seattle beach" trong ví dụ này.

Trong NATURAL LANGUAGE MODE có nghĩa là các từ tìm kiếm được cung cấp trực tiếp từ đầu vào của người dùng mà không có bất kỳ thủ tục tiền xử lý trước. MySQL giả định chế độ ngôn ngữ tự nhiên theo mặc định, vì vậy bạn không phải chỉ rõ nó một cách rõ ràng.

Lưu ý: So với chế độ ngôn ngữ tự nhiên, bắt nguồn từ là một kỹ thuật FTS hữu ích khác làm cho chỉ số dải từ của một từ chỉ lưu trữ phần gốc. Ví dụ, từ "fits" và "fitted" sẽ giống nhau bằng cách sử dụng FTS với từ bắt nguồn.

Thật không may, MySQL không hỗ trợ bắt nguồn từ. Giãn cây là trong bài làm của MySQL, nhưng không có khung thời gian để thực hiện và phát hành nó được nêu ra. FTS vẫn còn hữu ích bởi vì nó vẫn nhanh hơn các điều khoản LIKE. Nếu bạn muốn sử dụng bắt nguồn từ, bạn có thể điều tra tích hợp với thư viện Snowball.

\ G ở cuối truy vấn ở trên làm cho mỗi cột trong đầu ra in trên một dòng mới. Điều này có thể làm cho kết quả dài hơn một chút dễ dàng hơn để đọc. Đầu ra cho lệnh trên sẽ giống như sau:

Output
*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
*************************** 2. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
2 rows in set (0.00 sec)

Không có mục có cụm từ "Seattle beach", nhưng vì chúng tôi sử dụng tìm kiếm toàn văn, chúng tôi vẫn có hai kết quả: dòng đầu tiên, chỉ chứa từ "Seattle", và hàng thứ hai, chỉ chứa từ "beach". Bạn có thể thử các tìm kiếm bổ sung bằng cách thay đổi từ khoá để xem kết quả.

Bây giờ bạn có thể sử dụng hàm FTS trong các truy vấn SQL để tìm các hàng có liên quan đến đầu vào tìm kiếm, bạn có thể làm cho những kết quả này có liên quan hơn.

Bước 3 - Tinh chỉnh kết quả FTS

Có hai kỹ thuật có thể giúp kết quả tìm kiếm toàn văn phù hợp hơn. Một là lọc theo điểm số kết quả của kết quả, và cái kia là sử dụng IN BOOLEAN để loại trừ các từ cụ thể khỏi kết quả và chỉ định khoảng cách tối đa giữa các cụm từ tìm kiếm.

Sử dụng Relevance Score

Relevance Score của một kết quả định lượng làm thế nào tốt của một phù hợp cho các thuật ngữ tìm kiếm, trong đó 0 là không có liên quan nào cả. Điểm số liên quan dựa trên một số yếu tố, bao gồm khoảng thời gian tìm thấy trong một tài liệu cụ thể và bao nhiêu tài liệu chứa thuật ngữ. Tài liệu tìm kiếm toàn văn của MySQL đi vào toán học phía sau tính toán con số này.

Nhận được điểm số phù hợp cho mỗi hàng dựa trên truy vấn "traveling to parks".

Output
*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
*************************** 2. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
2 rows in set (0.00 sec)

Phần số điểm của lệnh này nhãn cột thứ hai trong đầu ra dưới dạng số. Nếu không, nó sẽ được dán nhãn với lệnh được sử dụng để điền nó, mà trong trường hợp này là MATCH (tiêu đề, nội dung, tác giả) AGAINST ('traveling to parks').

Kết quả sẽ giống như sau:

Output
+----+----------------------+
| id | score                |
+----+----------------------+
|  1 | 0.031008131802082062 |
|  2 |  0.25865283608436584 |
|  2 |  0                    |
+----+----------------------+
3 rows in set (0.00 sec)

Hàng thứ ba có một điểm số liên quan là 0 vì không có cụm từ tìm kiếm nào xuất hiện trong đó. Dòng đầu tiên chứa từ "đi du lịch", nhưng không "đến" hoặc "công viên", và có điểm số liên quan rất thấp 0,03. Hàng thứ hai, chứa tất cả các từ, có điểm số liên quan cao nhất là 0,25.

Bạn có thể sử dụng các điểm này để trả lại kết quả có liên quan nhất trước hoặc chỉ trả lại kết quả vượt quá phạm vi mức độ liên quan nhất định. Điểm tương quan sẽ khác nhau theo số liệu, do đó, việc chọn điểm cắt cần phải điều chỉnh thủ công.

Lệnh sau chạy truy vấn tương tự, nhưng thêm hai điều:

Nó chỉ hiển thị các hàng có điểm số không liên quan khác bằng cách thêm WHERE MATCH (tiêu đề, nội dung, tác giả) AGAINST ('đi đến công viên')> 0 Nó sắp xếp các kết quả theo mức độ liên quan bằng cách thêm ORDER BY score DESC

Output
*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
*************************** 2. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
2 rows in set (0.00 sec)

Bạn cần lặp lại hàm MATCH () AGAINST () trong mệnh đề WHERE vì các hạn chế SQL đối với những gì có thể được bao gồm trong mệnh đề đó.

Đầu ra sẽ như sau:

Output
+----+----------------------+
| id | score                |
+----+----------------------+
|  2 |  0.25865283608436584 |
|  1 | 0.031008131802082062 |
+----+----------------------+
2 rows in set (0.01 sec)

Kết quả phù hợp nhất, dòng 2, được hiển thị đầu tiên, tiếp theo là hàng ít liên quan hơn. Dòng 3 không được hiển thị bởi vì điểm số liên quan của nó là 0.

Bạn có thể thay đổi các cutoffs để tiếp tục tinh chỉnh các kết quả của bạn. Ví dụ, nếu bạn sử dụng 0,1 thay vì 0 như là cắt, chỉ có hàng 2 sẽ được trả lại.

Sử dụng IN BOOLEAN

Ở Bước 2, bạn đã sử dụng chế độ mặc định của NGÔN NG NAT NATURAL khi chỉ định một thuật ngữ truy vấn. Có một chế độ khác, IN BOOLEAN, cho phép bạn loại trừ các từ cụ thể khỏi một tìm kiếm, xác định một phạm vi cách các từ trong đầu vào phải xa nhau, và nhiều hơn nữa.

Để bỏ một thuật ngữ từ truy vấn, sử dụng toán tử trừ với IN BOOLEAN. Lệnh sau sẽ trả lại kết quả chứa từ "traveling" nhưng không chứa từ "Seattle".

Output
*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
*************************** 2. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
2 rows in set (0.00 sec)

Kết quả sẽ chỉ hiển thị hàng 2:

Output
*************************** 1. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
1 row in set (0.01 sec)

Điều này hoạt động bởi vì toán tử trừ cho DMS đánh dấu bất kỳ tài liệu nào với các từ bị loại trừ với điểm số liên quan là 0. Chỉ những kết quả có điểm số liên quan khác không được hiển thị trong chế độ này.

Bạn cũng có thể sử dụng IN BOOLEAN MODE để xác định khoảng cách tối đa giữa các cụm từ tìm kiếm. Khoảng cách này được tính bằng từ và, quan trọng, bao gồm các thuật ngữ tìm kiếm. Ví dụ: cụm từ "mèo và chó" có khoảng cách là 3.

Kết quả lợi nhuận sau lệnh trong đó dòng chữ "traveling" và "miles" xuất hiện với không quá 2 chữ giữa chúng.

Output
*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
*************************** 2. row ***************************
     id: 2
  title: Hitting the beach was voted the best part of life in the region
content: Exploring tracks and trails was second most popular, followed by visiting the shops and then traveling to local parks.
 author: Ethan
2 rows in set (0.00 sec)

Bạn sẽ thấy một kết quả, mà phù hợp traveling the 140 miles trong nội dung dòng 2.

Output
*************************** 1. row ***************************
     id: 1
  title: Pacific Northwest high-speed rail line
content: Currently there are only a few options for traveling the 140 miles between Seattle and Vancouver and none of them are ideal.
 author: Greg
1 row in set (0.00 sec)

Nếu bạn thay đổi @ 4 thành @ 3 trong lệnh ban đầu, bạn sẽ không thấy kết quả.

Việc hạn chế kết quả tìm kiếm theo khoảng cách giữa các cụm từ tìm kiếm có thể hữu ích khi tìm kiếm các tài liệu rất lớn với các từ ngữ đa dạng. Khoảng cách giữa các thuật ngữ truy vấn càng nhỏ thì kết quả càng chính xác, mặc dù việc tinh chỉnh khoảng cách sẽ phụ thuộc vào tập hợp các tài liệu mà bạn đang làm việc. Ví dụ: một tập hợp các bài báo khoa học có thể hoạt động tốt với khoảng trống từ nhỏ đến 3, nhưng tìm kiếm các bài đăng trên diễn đàn có thể hoạt động tốt hơn với khoảng cách từ 8 trở lên tùy thuộc vào mức độ bạn muốn có kết quả rộng và hẹp.