Deep understanding _mysql based on Mysql full-text indexing

This article simply narrates the application instance of the Full-text index, the MySQL demo version 5.5.24.
Q: What are the applications for Full-text indexing?
A: Full-text indexing is the key technology to achieve large data search at present.
For more detailed introduction please own Baidu, this article no longer elaborated.
--------------------------------------------------------------------------------
One, how to set up?

You can set up Full-text indexing by clicking {Full-text Search} at the end of the diagram, and different versions of MySQL may have different names.

Second, set the conditions
1. The table's storage engine is MyISAM, and the default storage engine InnoDB does not support Full-text indexing (the new version MYSQL5.6 InnoDB supports Full-text indexing)
2. Field type: char, varchar, and text

Third, the configuration
Add in My.ini configuration file
# mysql full-text indexing query keywords Minimum length limit
[Mysqld]
Ft_min_word_len = 1
Restart MySQL after saving, execute SQL statement

Copy Code code as follows:

Show VARIABLES

See if the Ft_min_word_len is set to succeed, if it is not set up successfully, make sure
1. Make sure My.ini is properly configured, and be careful not to mistake My.ini position
2. Confirm that MySQL has been restarted, do not restart the computer
Other related configuration please own Baidu.
Note: After you reset the configuration, the index you have set requires that you reset the build index

Four, SQL syntax
Generate temp Table First

CREATE TABLE IF not EXISTS ' temp ' (
 ' id ' int (one) not null auto_increment,
 ' char ' char (=) NOT null,
 ' varchar ' VARCHAR is not NULL,
 ' text ' is 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 ', ' A bc I know 1 ', ' A bc I know 1 23 ') ;

Search ' char ' field ' a ' value

SELECT * from ' temp ' WHERE MATCH (' char ') against (' a ')

But you will find the query without results?!
at this time you may think: "Ah what's going on, I clearly follow the steps to do ah, is there a leak or wrong?"
You do not worry, do the procedure is such, error is always there, calm down, worry is not solve the problem.

If a keyword appears in 50% of the data, the word is treated as an invalid word.
If you want to remove 50% now please use in BOOLEAN mode to search

SELECT * from ' temp ' WHERE MATCH (' char ') against (' a ' in BOOLEAN MODE)

This allows you to query the results, but we do not recommend them.
Full-text indexing of the search model of the introduction of its own Baidu.

We're going to add a few useless data, we've lifted the 50% limit.

INSERT into ' temp ' (
' id ',
' char ',
' varchar ', '
text '
)
VALUES (
NULL, ' 7 ', ' 7 ', ' 7 '
) ), (
null, ' 7 ', ' 7 ', ' 7 '
), (
null, ' A,BC, I, know, 1,23 ', ' A,BC, I, know, 1,23 ', ' A,BC, I, know, 1,23 '
), (
NULL, ' x ', ' x ', ' X '
);

When you execute the following SQL statements you can query the data

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 (' know ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' 1 ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' 23 ');

The following SQL search does not have data

SELECT * from ' temp ' WHERE MATCH (' char ') against (' B ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' C ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' know ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' Tao ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' 2 ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' 3 ');

If you search multiple words, separate them with spaces or commas

SELECT * from ' temp ' WHERE MATCH (' char ') against (' A X ');
SELECT * from ' temp ' WHERE MATCH (' char ') against (' a,x ');

The above SQL can query to three data

Five, participle
See here you should find the value in our field is also participle, can not directly insert the original data.
Full-Text indexing application process:
1. Receive data-data participle-warehousing
2. Receive data-data participle-query
Now there is an important question: how to the data participle?
Data segmentation generally we will use some mature free word system, of course, if you have the ability to do the word segmentation system, here we recommend the use of SCWS participle plug-ins.
First Download
1.php_scws.dll Note the corresponding version
2.XDB dictionary File
3. Rule Set File
Download Address

Install Scws
1. First build a folder, the location is not limited, but it is best not to Chinese path.
2. Extract {rule set file}, throw xdb, three INI files to D:\SCWS
3. Copy Php_scws.dll to the Ext folder in your PHP directory
4. Add the following lines at the end of the php.ini:
[SCWS]
;
; Note Check that the Extension_dir setting in php.ini is correct, or set Extension_dir to null.
; The Php_scws.dll is then specified as an absolute path.
;
Extension = Php_scws.dll
Scws.default.charset = UTF8
Scws.default.fpath = "D:\scws"
5. Reboot your server
Test

$STR = "Test Chinese participle";
$so = Scws_new ();
$so->send_text ($STR);
$temp = $so->get_result ();
$so->close ();
Var_dump ($temp);

If the installation is unsuccessful, please refer to the official documentation
--------------------------------------------------------------------------------
So we can use Full-text indexing technology.