In-depth understanding of mysql-based full-text index

This article briefly describes the full-text index application example, MYSQL demo version 5.5.24.
Q: When is full-text index applicable?
A: Full-text indexing is currently A key technology for big data search.
For more details, refer to Baidu.
--------------------------------------------------------------------------------
I. How to set it?

Click {full-text search} at the end to set the full-text index. Different MYSQL versions may have different names.

Ii. Set conditions
1. The table storage engine is MyISAM. The default storage engine InnoDB does not support full-text indexing (the new version of MYSQL5.6 InnoDB supports full-text indexing)
2. Field Types: char, varchar, and text

Iii. Configuration
Add in my. ini configuration file
# MySQL full-text index query keyword minimum length limit
[Mysqld]
Ft_min_word_len = 1
Restart MYSQL after saving and execute the SQL statement.
Copy codeThe Code is as follows:
SHOW VARIABLES

Check whether ft_min_word_len is set successfully. If it is not set successfully, make sure that
1. confirm that my. ini is correctly configured. Be sure not to make a mistake in the position of my. ini.
2. Check that the mysql instance has been restarted.
For other configurations, please use Baidu.
Note: After the configuration is reset, You need to reset the configured index to generate the index.
 
Iv. SQL syntax
First generate the temp table
Copy codeThe Code is as follows:
Create table if not exists 'temp '(
'Id' int (11) not null AUTO_INCREMENT,
'Char 'char (50) not null,
'Varchar 'varchar (50) not null,
'Text' text not null,
Primary key ('id '),
Fulltext key 'Char '('Char '),
Fulltext key 'varchar '('varchar '),
Fulltext key 'text' ('text ')
) ENGINE = MyISAM default charset = utf8 AUTO_INCREMENT = 2;
Insert into 'temp '('id', 'Char', 'varchar ', 'text') VALUES
(1, 'a bc I know 1 23', 'a bc I know 1 23', 'a bc I know 1 23 ');

Search for the 'Char 'field 'A' Value
Copy codeThe Code is as follows:
SELECT * FROM 'temp 'where match ('Char') AGAINST ('A ')

But you will find no query results ?!
At this time, you may think: Oh, what's the problem? I did it according to the steps. Did you miss it or make a mistake?
Don't worry. The program is like this. There are always errors. Just calm down and you can't solve the problem.

If a keyword appears in 50% of the data, it will be treated as an invalid word.
If you want to remove 50%, please use in boolean mode to search now
Copy codeThe Code is as follows:
SELECT * FROM 'temp 'where match ('Char') AGAINST ('A' in boolean mode)

In this way, the results can be queried, but we do not recommend this function.
Full-text index search mode description Baidu.

We have added several useless data items and removed the 50% limit.
Copy codeThe Code is as follows:
Insert into 'temp '(
'Id ',
'Char ',
'Varchar ',
'Text'
)
VALUES (
NULL, '7', '7', '7'
),(
NULL, '7', '7', '7'
),(
NULL, 'a, bc, me, know'
),(
NULL, 'x', 'x', 'X'
);

Then you can run the following SQL statement to query the data.
Copy codeThe Code is as follows:
SELECT * FROM 'temp 'where match ('Char') AGAINST ('A ');
SELECT * FROM 'temp 'where match ('Char') AGAINST ('bc ');
SELECT * FROM 'temp 'where match ('Char') AGAINST ('I ');
SELECT * FROM 'temp 'where match ('Char') AGAINST ('knows ');
SELECT * FROM 'temp 'where match ('Char') AGAINST ('1 ');
SELECT * FROM 'temp 'where match ('Char') AGAINST ('23 ');

No data found in the following SQL statements
Copy codeThe Code is as follows:
SELECT * FROM 'temp 'where match ('Char') AGAINST ('B ');
SELECT * FROM 'temp 'where match ('Char') AGAINST ('C ');
SELECT * FROM 'temp 'where match ('Char') AGAINST ('zhi ');
SELECT * FROM 'temp 'where match ('Char') AGAINST ('and ');
SELECT * FROM 'temp 'where match ('Char') AGAINST ('2 ');
SELECT * FROM 'temp 'where match ('Char') AGAINST ('3 ');

If you search for multiple words, separate them with spaces or commas.
Copy codeThe Code is as follows:
SELECT * FROM 'temp 'where match ('Char') AGAINST ('a x ');
SELECT * FROM 'temp 'where match ('Char') AGAINST ('a, x ');

All the preceding SQL statements can query three data items.

5. Word Segmentation
Here, you should find that the values in our fields are also word segmentation, and raw data cannot be inserted directly.
Full-text index application process:
1. receive data-data word segmentation-warehouse receiving
2. receive data-data word segmentation-Query
Now there is an important question: how to split data?
We generally use mature and free Word Segmentation systems for data word segmentation. If you have the ability, you can also use your own word segmentation system. Here we recommend that you use the SCWS word segmentation plug-in.
First download
1. Pay attention to the corresponding version of php_scws.dll
2. XDB dictionary file
3. Rule Set File

 
Install scws
1. Create a folder without restrictions, but it is best not to use a Chinese path.
2. Decompress {rule set file} and throw all xdb and three INI files to D: \ scws.
3. Copy php_scws.dll to the EXT folder under your PHP Directory.
4. Add the following lines to the end of php. ini:
[Scws]

Check whether the set value of extension_dir in php. ini is correct. Otherwise, set extension_dir to null,
And then specify php_scws.dll as the absolute path.

Extension = php_scws.dll
Scws. default. charset = utf8
Scws. default. fpath = "D: \ scws"
5. Restart your server
Test
Copy codeThe Code is as follows:
$ Str = "test Chinese word segmentation ";
$ So = scws_new ();
$ So-> send_text ($ str );
$ Temp = $ so-> get_result ();
$ So-> close ();
Var_dump ($ temp );

If the installation is unsuccessful, refer to the official instructions.
--------------------------------------------------------------------------------
In this way, we can use the full-text index technology.