Tìm văn bản trong cơ sở dữ liệu mysql

Cách sử dụng tìm kiếm toàn văn và từ khóa khớp để tìm một chuỗi trong mọi bảng trong cơ sở dữ liệu MySQL

2 năm trước   •   7 phút đọc

Bởi Tetyana Skorykh

Mục lục

Mục lục

Tìm kiếm toàn văn bản trong MySQL là gì?

Bạn có thể có siêu năng lực giống như Google để tìm các từ và cụm từ trên toàn bộ cơ sở dữ liệu của mình. Công cụ tìm kiếm sử dụng Full-Text Search để tìm kết quả trong cơ sở dữ liệu. Những cơ sở dữ liệu này thường chứa rất nhiều dữ liệu dạng văn bản. Ví dụ: các trang web tin tức chứa nhiều bài báo và có thể muốn tìm một cụm từ cụ thể. Hoặc người dùng có thể gửi yêu cầu hỗ trợ với cùng một từ chỉ ra một vấn đề phổ biến

Tìm kiếm toàn văn không cố gắng tìm từng cụm từ được viết bởi người dùng. Nó đang cố đoán xem người dùng nghĩ gì, để tìm hiểu ý nghĩa sâu xa hơn, có thể nói như vậy

Mục tiêu của nó là tìm các kết quả gần đúng về nghĩa và nhanh chóng đưa ra chúng. Do đó, kết quả sẽ hữu ích hơn nhiều cho người dùng, vì nó sẽ phù hợp hơn

Thông thường, một dịch vụ tìm kiếm có hai thành phần. Công cụ tìm kiếm và lập chỉ mục

• Bộ chỉ mục nhận văn bản làm đầu vào, xử lý văn bản [cắt bỏ các kết thúc, các từ không quan trọng, v.v. ] và lưu trữ mọi thứ trong chỉ mục. Cấu trúc của một chỉ mục như vậy cho phép tìm kiếm rất nhanh;
• Công cụ tìm kiếm, giao diện tìm kiếm chỉ mục, chấp nhận yêu cầu từ máy khách, xử lý cụm từ và tìm kiếm nó trong chỉ mục

Tìm kiếm toàn văn trong Google

Tìm kiếm toàn văn bản trong MySQL hoạt động như thế nào?

Để sử dụng full-text search trong MySQL bạn cần sử dụng full-text indexes và hàm MATCH[]. Chỉ mục toàn văn là FULLTEXT

Mysql hỗ trợ các chỉ mục toàn văn trên các bảng MyISAM. Hỗ trợ InnoDB đã được thêm vào kể từ phiên bản 5. 6. 4

Khi bạn tạo bảng bằng lệnh CREATE TABLE, bạn có thể tạo các chỉ mục này trên cột VARCHAR và TEXT. Bạn cũng có thể thêm các chỉ mục sau này bằng lệnh ALTER TABLE hoặc CREATE INDEX

Tuy nhiên, bạn không nên thêm chỉ mục FULLTEXT ngay khi tạo bảng. Một lượng lớn dữ liệu được tải vào bảng nhanh hơn nhiều nếu không thêm chỉ mục. Đó là lý do tại sao các chỉ mục thường được thêm vào sau

Hàm MATCH[] thực hiện tìm kiếm ngôn ngữ. Nó so sánh một chuỗi với nội dung văn bản. Theo nội dung, ý tôi là sự kết hợp của một hoặc nhiều cột có trong chỉ mục FULLTEXT

Chỉ mục FULLTEXT trong MySQL

Chuỗi tìm kiếm được chỉ định với một đối số trong biểu thức CHỐNG LẠI []. Tìm kiếm không phân biệt chữ hoa chữ thường. Lệnh MATCH[] trả về giá trị liên quan cho mỗi hàng trong một cột. Giá trị liên quan là mức độ giống nhau của chuỗi tìm kiếm và văn bản trong chuỗi

MATCH [column1,column2,column3...] AGAINST [expression [search_modifier]]

Khi bạn sử dụng lệnh MATCH[] trong mệnh đề WHERE, các hàng của cột kết quả sẽ tự động được sắp xếp theo mức độ liên quan

Giá trị liên quan là một số dấu phẩy động không âm.
Mức độ liên quan được tính toán dựa trên.

• Các từ trong một hàng nhất định của một cột;
• Các từ duy nhất trong hàng đó;
• Số từ trong văn bản;
• Các hàng chứa một từ

Full-Text Search không phải là một công cụ phức tạp, nó gần như đơn giản như toán tử LIKE. Nhưng riêng toán tử LIKE không hữu ích bằng FTS. Tôi sẽ nói về điều này trong tiểu mục tiếp theo

Toán tử LIKE vs Tìm kiếm toàn văn bản trong MySQL

Một từ đồng nghĩa với toán tử LIKE là độ chính xác, đối với đầu vào toàn văn bản, nó là tính linh hoạt. Do đó, bạn có thể dễ dàng hiểu được sự phức tạp trong công việc của từng phương pháp. Và bây giờ để biết thêm chi tiết

Nhà điều hành THÍCH

Độ chính xác của toán tử LIKE cao có nghĩa là hiển thị ít kết quả sai hơn. Tỷ lệ thu hồi cao có nghĩa là ít kết quả liên quan bị bỏ qua hơn. Điều này có nghĩa là LIKE chính xác 100% khi tìm kiếm cụm từ

Tuy nhiên, câu lệnh LIKE trong MySQL có thể không hữu ích trong một số trường hợp.
Nếu bạn áp dụng nó cho một cột không được lập chỉ mục, cơ sở dữ liệu sẽ quét toàn bộ để tìm kết quả phù hợp.

Nếu cột được lập chỉ mục, có thể thực hiện đối sánh với các khóa chỉ mục, nhưng kém hiệu quả hơn nhiều so với hầu hết các tìm kiếm chỉ mục.
Trong trường hợp xấu nhất, mẫu LIKE sẽ có các ký tự đại diện ở đầu yêu cầu kiểm tra từng khóa chỉ mục.

Toán tử LIKE tốt hơn khi không có nhiều bản ghi và thực tế không thể sử dụng nó trong các bảng có hàng trăm nghìn hàng vì tìm kiếm được thực hiện trên tất cả các bản ghi và điều này làm giảm hiệu suất

Tìm kiếm toàn văn

Hầu hết các triển khai tìm kiếm toàn văn đều sử dụng chỉ mục, trong đó các khóa là các thuật ngữ riêng lẻ và các giá trị liên quan là các tập bản ghi chứa thuật ngữ đó.
FTS được tối ưu hóa để tính toán giao điểm, kết hợp các tập bản ghi này. Nó thường cung cấp một thuật toán xếp hạng để định lượng mức độ phù hợp của một mục nhất định với các từ khóa tìm kiếm của bạn.

Các tính năng tìm kiếm toàn văn bao gồm

• Tách một khối văn bản phi cấu trúc thành các từ, cụm từ và mã thông báo đặc biệt riêng biệt;
• Kết hợp các biến thể của một từ thành một thuật ngữ chỉ số;
• Đo lường mức độ giống nhau của bản ghi phù hợp với chuỗi truy vấn

Toán tử THÍCH vs TRẬN ĐẤU CHỐNG LẠI

Chúng ta hãy xem nhanh so sánh. Hình thái học có nghĩa là tìm kiếm dựa trên gốc của từ bằng kiến ​​thức ngữ pháp và ngôn ngữ cụ thể

Tìm kiếm với toán tử LIKE

Lợi ích Nhược điểm Độ phức tạp thấp hơn O[n^2] Không hỗ trợ hình thái học Sắp xếp kết quả Không có bộ điều chỉnh Được sử dụng trên mọi loại trườngTìm kiếm trong tất cả các dòng

Tìm kiếm với Full-text Search

Lợi ích Nhược điểmHỗ trợ cho Hình thái họcThiếu khả năng sắp xếpKết quả được sắp xếp theo mức độ liên quanVARCHAR và TEXT chỉ với các chỉ mụcCông cụ sửa đổi kiểu của GoogleQuy trình sử dụng nhiều tài nguyênDừng từHỗ trợ bản địa chỉ dành cho các bảng MyISAMKhả năng tùy chỉnhThêm dữ liệu mất nhiều thời gian hơn với FULLTEXT

Hạn chế tìm kiếm toàn văn

Khi sử dụng tìm kiếm toàn văn, cũng cần nhớ rằng hàm MATCH[] có những hạn chế của nó

1. Tất cả các tham số của hàm MATCH[] phải là các cột của cùng một bảng. Điều này có nghĩa là chúng phải là một phần của cùng một chỉ mục FULLTEXT. Không áp dụng cho CHẾ ĐỘ BOOLEAN;
2. Danh sách các cột trong lệnh MATCH[] phải khớp chính xác với danh sách các cột trong định nghĩa chỉ mục FULLTEXT cho bảng này. Không áp dụng cho CHẾ ĐỘ BOOLEAN;
3. Đối số trong AGAINST[] phải là một chuỗi bất biến

Ví dụ tìm kiếm toàn văn trong MySQL

Trong ví dụ này, bạn sẽ thấy mức độ liên quan của kết quả tìm kiếm toàn văn

Đầu tiên, hãy tạo một lược đồ

CREATE SCHEMA IF NOT EXISTS `mydb`;

Lược đồ của chúng tôi sẽ là một cơ sở dữ liệu về các sản phẩm mỹ phẩm. Đó là, bây giờ chúng tôi đang tạo một bảng danh mục

CREATE TABLE IF NOT EXISTS `mydb`.`category` [
  `id` INT NOT NULL,
  `name_category` VARCHAR[45] NOT NULL,
  FULLTEXT[name_category],
  PRIMARY KEY [`id`]]
ENGINE = InnoDB;

Và bây giờ chúng ta tạo một bảng sản phẩm

CREATE TABLE IF NOT EXISTS `mydb`.`product` [
  `id` INT NOT NULL AUTO_INCREMENT,
  `name_product` VARCHAR[45] NOT NULL,
  `price_product` DECIMAL NOT NULL,
  `amount_product` INT NOT NULL,
  `desc_product` VARCHAR[255] NOT NULL,
  `idcategory` INT NOT NULL,
  FULLTEXT[name_product, desc_product],
  PRIMARY KEY [`id`],
  INDEX `fk_product_category_idx` [`idcategory` ASC],
  CONSTRAINT `fk_product_category`
    FOREIGN KEY [`idcategory`]
    REFERENCES `mydb`.`category` [`id`]
    ON DELETE NO ACTION
    ON UPDATE NO ACTION]
ENGINE = InnoDB;

Bây giờ chúng tôi có các bảng, chúng tôi điền chúng bắt đầu với bảng danh mục

INSERT INTO `mydb`.category [`id`, `name_category`] VALUES [1,'Shampoo'], [2,'Eyeshadow'], [3,'Face lotionproduct'], [4,'Face Primer'], [5,'Lipstick'], [6,'Powder'], [7,'Mascara'], [8,'Blush'], [9,'Highlighter'], [10,'Toner'];

Và cũng điền vào bảng sản phẩm

INSERT INTO product [id, name_product, price_product, amount_product, desc_product, idcategory] VALUES 
[1, 'Shampoo perfumes "Clear"', 130, 100, 'Styling products and environmental pollutants have been filling the hair structure from time to time, making it dull and hard. This shampoo is formulated to effectively remove this impurity while rejuvenating the hair structure.',1],
[2, 'Eyeshadow "Makeup"', 245, 104, 'Contains an all-natural mineral wax with added kaolin and zinc oxide to create lush, healthy lashes. The brush is designed to reduce clumping of the product. Apply in a zigzag motion from root to tip to completely cover each lash. Reapply if desired.',2],
[3, 'Face Lotion "Gigi"', 120, 99, 'The lotion promotes the resorption of inflammation, maturation and drying of abscesses. The lotion has a disinfectant, anti-inflammatory, antibacterial and antifungal effect, and also prevents the development of a secondary infection.',3],
[4, 'Lipstick Set "LP"', 250, 165, 'The properties of lipstick will be appreciated by professional makeup artists and lovers of the perfect make-up. A variety of dazzling shades allow you to choose the right tone for elegant daytime and spicy evening make-up.',5],
[5, 'Highlighter "NAC"', 175, 111, 'The skin seems to glow from the inside.',9],
[6, 'Powder "Pow"', 190, 205, 'With a high concentration of shimmery pigments and a unique holographic shade, the powder enhances facial features and fills the skin with radiance.',6],
[7, 'Toner "Tonn"', 160, 198, 'This is a versatile toner that removes deep impurities, tones the skin and gently removes dead skin cells from the skin!',10],
[8, 'Blush "Gigi"', 135, 57, 'The cosmetic product has a silky texture that lays down on the skin very softly and smoothly, without clogging pores or making the make-up heavier.',8],
[9, 'Highlighter "Lily"', 200, 30, 'It has a light texture that creates a weightless shimmer on the skin. Thanks to the smallest reflective particles, the face acquires a delicate, soft, natural shine.',9],
[10, 'Shampoo "Lux"', 160, 11, 'Dry and damaged hair requires intensive repair. Gentle cleansing and deep nourishment in minutes.',1],
[11, 'Lipstick "Gigi"', 90, 225, 'On your lips, lipstick will retain a flawless look for up to 12 hours, decorating them with a luxurious matte finish and a sophisticated shade.',5];

Cái bàn chúng tôi có

Bảng "sản phẩm"

Xuất các trường từ một bảng bằng từ “dầu gội đầu” cho hai bảng được nối gián tiếp

SELECT * FROM category c JOIN product p ON c.id = p.idcategory
WHERE MATCH[p.name_product, p.desc_product] AGAINST ['shampoo'];

Đầu ra của các trường từ bảng 

Bạn có thể nhận thấy rằng tìm kiếm đã tìm thấy từ này trong phần mô tả sản phẩm và tên sản phẩm [dòng 1] và ở dòng 2, từ này chỉ xuất hiện trong tên sản phẩm chứ không có trong phần mô tả

Sự kết luận

Đây là một đoạn thông tin ngắn về tìm kiếm toàn văn trong MySQL. Hiểu biết cơ bản về tính hữu ích của Tìm kiếm Toàn văn sẽ đến từ việc sử dụng tìm kiếm này trong thực tế. Bây giờ bạn đã biết rằng toán tử LIKE không phải là cách duy nhất để tìm kiếm văn bản trong cơ sở dữ liệu MySQL. Tìm kiếm toàn văn bản trong MySQL cung cấp cho bạn siêu năng lực tìm kiếm như Google phải tìm bất kỳ chuỗi hoặc từ nào trên tất cả các bảng trong cơ sở dữ liệu của bạn

dự án mã

Tham gia Bản tin Arctype

Tham gia cùng hơn 800 nhà phát triển và nhận lời khuyên hữu ích từ các chuyên gia trong ngành về lập trình mỗi tuần

Làm cách nào để tìm kiếm văn bản trong MySQL?

Làm cách nào để tìm từ trong chuỗi trong MySQL?

Làm cách nào để tìm kiếm dữ liệu từ cơ sở dữ liệu trong MySQL?

Cách kích hoạt đầy đủ