How to get better search results in MySQL

MySQL databaseA built-in full-text search solution is provided to address this problem. Here, developers only need to simply mark the fields that require full-text search, and then use special MySQL methods to run search on those fields, this not only improves the performance and efficiency, because the MySQL database indexes these fields to optimize the search), but also achieves higher quality search, because MySQL uses natural language to intelligently rate the results to remove irrelevant items.

This article describes how to perform full-text search in MySQL.

1. Set the basic table

Use the following SQL command to create an example table:

Reference content is as follows:
Mysql> create table reviews (id INT (5)
Primary key not null AUTO_INCREMENT, data TEXT );
 

The above command creates a simple music album database that mainly contains text in the entire segment), and then adds some records to the table:

Reference content is as follows:
Mysql> insert into 'Reviews '('id', 'data') VALUES
(1, 'gingerboy has a new single out called Throwing Rocks.
It \'s great! '); Mysql> insert into 'Reviews' ('id', 'data ')
VALUES (2, 'Hello all, I really like the new Madonna single.
One of the hottest tracks currently playing...
I \ 've been listening to it all day'); mysql> INSERT INTO 'review'
('Id', 'data') VALUES (3, 'Have you heard the new band Hotter
Than Hell? They have five members and they burn their
Instruments when they play in concerts.
These guys totally rock! Like, awesome, dude! ');
 

Verify that the data is entered correctly:

Reference content is as follows:
Mysql> SELECT * FROM reviews;
+ ---- + -------------------------------------------- +
| Id | data |
+ ---- + -------------------------------------------- +
| 1 | Gingerboy has a new single out called... |
| 2 | Hello all, I really like the new Madon... |
| 3 | Have you heard the new band Hotter Than... |
3 rows in set (0.00 sec)
2. Define full-text search fields

Next, define the fields to be indexed as full-text search

Reference content is as follows:
Mysql> alter table reviews add fulltext index (data );
Query OK, 3 rows affected (0.21 sec)
Records: 3 Duplicates: 0 Warnings: 0
Run the show indexes command to check whether the index has been added:

Reference content is as follows:
Mysql> show indexes from reviews;
+ --------- + --------------- + -------- + ------ + ------------ + --------- +
| Table | Column_name | Packed | Null | Index_type | Comment |
---------- + --------------- + -------- + ------ + ------------ + --------- +
| Reviews | id | NULL | BTREE |
Reviews | data | NULL | YES | FULLTEXT |
+ --------- + --------------- + -------- + ------ + ------------ + --------- +
Searching in a MySQL database is one of the most important functions in a MySQL database. using full-text search brings great convenience to database management.