a lot of
Both Internet applications provide full-text search capabilities, users can use a word or word piece to locate the matching record as a query item. In the background, these programs use the LIKE statement in a Select query to perform such queries, although this method is feasible, but for full-text findings, this is an extremely low method, especially when processing a lot of data. .
MySQL provides this problem with a solution based on built-in full-text search. Here, the developer only needs to simply log out the field that requires a full-text search, then use a special mysql method to run search in those fields, which not only improves performance and efficiency (because MySQL has indexed these fields to optimize the search) And achieve a higher quality search because MySQL uses natural language to intelligently rating the results to the resulting project.
This article will tell you how to perform full-text search in MySQL.
1, set basic form
From the creation example form, use the following SQL commands:
Mysql> CREATE TABLE REVIEWS (ID INT (5) Primary Key NOT NULL Auto_Increment, Data Text);
The above command created a simple music collection database (mainly the text), then add some records to this table:
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` reviews` ( `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 the correct entry of the data:
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 tran ... | - ----------------------------------- --------- 3 rows in set (0.00 sec) a lot
Both Internet applications provide full-text search capabilities, users can use a word or word piece to locate the matching record as a query item. In the background, these programs use the LIKE statement in a Select query to perform such queries, although this method is feasible, but for full-text findings, this is an extremely low method, especially when processing a lot of data. .
MySQL provides this problem with a solution based on built-in full-text search. Here, the developer only needs to simply log out the field that requires a full-text search, then use a special mysql method to run search in those fields, which not only improves performance and efficiency (because MySQL has indexed these fields to optimize the search) And achieve a higher quality search because MySQL uses natural language to intelligently rating the results to the resulting project.
This article will tell you how to perform full-text search in MySQL.
1, set basic form
From the creation example form, use the following SQL commands:
Mysql> CREATE TABLE REVIEWS (ID INT (5) Primary Key NOT NULL Auto_Increment, Data Text);
The above command created a simple music collection database (mainly the text), then add some records to this table:
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` reviews` ( `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 the correct entry of the data:
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 tran ... | - ----------------------------------- --------- 3 ROWS IN SET (0.00 sec)
Here, Match () compares the text of the parameters to its field to the parameters passing to the AgAinst (), if there is a match, then return in normal way. Note You can pass more than one field to see () to view - just use commas to split field list.
When MySQL has received a full-text search request, it scores each record internally, and the records that do not match are divided into zero, and "more relevant" records are relatively higher than "less relevant" records. fraction. Correlation is determined by a series of divided standards of MySQL to see Mysql's user manual to get more information.
If you want to see how each record is scored, you only need to return the match () method as part of the result set, as shown below:
MySQL> SELECT ID, MATCH (DATA) AGAINST ('Rock') from reviews; ---- -------------------------------------------------------------------------------------------------------------------------------------------------- ----- | ID | Match (Data) Against ('Rock') | ---- ---------------------------------------------------------------------------------------------------------------------------------- ------- | 1 | 0 || 2 | 0 || 3 | 1.3862514533815 | ---- --------------------- ----------
3 rows in set (0.00 sec)
4, use logical search modifiers (Boolean search modifiers)
You can also use the logical search modifier to make more precise search, which is implemented by adding a special in Boolean Mode modifier in the AgAinst statement, in the following examples, will look up "Single" but no "Madonna" record of:
MySQL> Select ID from Reviews WHERE Match (DATA) AGAINST (' SINGLE -MADONNA' IN BOOLEAN MODE); ---- | ID | ---- | 1 | ----
Here, Match () compares the text of the parameters to its field to the parameters passing to the AgAinst (), if there is a match, then return in normal way. Note You can pass more than one field to see () to view - just use commas to split field list.
When MySQL has received a full-text search request, it scores each record internally, and the records that do not match are divided into zero, and "more relevant" records are relatively higher than "less relevant" records. fraction. Correlation is determined by a series of divided standards of MySQL to see Mysql's user manual to get more information. If you want to see how each record is scored, you only need to return the match () method as part of the result set, as shown below:
MySQL> SELECT ID, MATCH (DATA) AGAINST ('Rock') from reviews; ---- -------------------------------------------------------------------------------------------------------------------------------------------------- ----- | ID | Match (Data) Against ('Rock') | ---- ---------------------------------------------------------------------------------------------------------------------------------- ------- | 1 | 0 || 2 | 0 || 3 | 1.3862514533815 | ---- --------------------- ----------
3 rows in set (0.00 sec)
4, use logical search modifiers (Boolean search modifiers)
You can also use the logical search modifier to make more precise search, which is implemented by adding a special in Boolean Mode modifier in the AgAinst statement, in the following examples, will look up "Single" but no "Madonna" record of:
MySQL> Select ID from Reviews WHERE Match (DATA) AGAINST (' SINGLE -MADONNA' IN BOOLEAN MODE); ---- | ID | ---- | 1 | ----
