Mysql full text search fulltext
Mysql > select * from T2
+-+
| | a | b | c | |
+-+
| | 1 | aa | aa11111111111111111111111 |
| | 2 | bb | bb222222222222222222222222 |
+-+
2 rows in set (0.00 sec)
Mysql >
-- here the test characters are less than 4 and the data is only 2 lines. No records can be found.
Mysql > select match (c) against ('bb') from T2
+-- +
| | match (c) against ('bb') | |
+-- +
| | 0 |
| | 0 |
+-- +
2 rows in set (0.00 sec)
Mysql > insert into T2 values (3djnemeb cc','cc33333333333333333')
Query OK, 1 row affected (0.00 sec)
Mysql > commit
Query OK, 0 rows affected (0.00 sec)
Mysql > select match (b) against ('bb') from T2
+-- +
| | match (b) against ('bb') | |
+-- +
| | 0 |
| | 0 |
| | 0 |
+-- +
3 rows in set (0.00 sec)
Mysql >
Mysql >
Mysql >
Mysql > insert into T2 values (4 recordings DDD pencil DD333333333333333333333')
Query OK, 1 row affected (0.00 sec)
Mysql > insert into T2 values (5pm)
Query OK, 1 row affected (0.00 sec)
Mysql > insert into T2 values (6)
Query OK, 1 row affected (0.00 sec)
Mysql > insert into T2 values (7 recordings glittering 3333333333333333')
Query OK, 1 row affected (0.00 sec)
Mysql > insert into T2 values (8 recordings hr 43333333333333333')
Query OK, 1 row affected (0.00 sec)
Mysql > insert into T2 values (9 recordings ii1 3333333333333333')
Query OK, 1 row affected (0.00 sec)
Mysql > insert into T2 values (10 minicrary jjcards, jj3333333333333333333')
Query OK, 1 row affected (0.00 sec)
Mysql >
Mysql >
Mysql > commit
Query OK, 0 rows affected (0.00 sec)
Mysql > select match (b) against ('bb') from T2
+-- +
| | match (b) against ('bb') | |
+-- +
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
+-- +
10 rows in set (0.00 sec)
-- more than 4 characters can be searched
Mysql > insert into T2 values (10) KKKKK adfsdf')
Query OK, 1 row affected (0.00 sec)
Mysql > select match (c) against ('kkkkk') from T2
+-+
| | match (c) against ('kkkkk') | |
+-+
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 0 |
| | 2.2508163452148438 | |
+-+
11 rows in set (0.00 sec)
Mysql > select * from T2 where match (c) against ('kkkkk')
+-+
| | a | b | c | |
+-+
| | 10 | kkkkkwq | kkkkk adfsdf |
+-+
1 row in set (0.00 sec)
Mysql > select * from T2 where match (b) against ('kkkkk')
Empty set (0.01sec)
Mysql > update T2 set baked kkkkk' where aq10
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
Mysql > select * from T2 where match (b) against ('kkkkk')
+-+
| | a | b | c | |
+-+
| | 10 | kkkkk | jj33333333333333333 |
| | 10 | kkkkk | kkkkk adfsdf |
+-+
2 rows in set (0.00 sec)
-- default natural language. Search results are sorted by relevance. Here, the query results are relevant.
Mysql > select b match (b) against ('kkkkk') from T2
+-- +
| | b | match (b) against ('kkkkk') |
+-- +
| | aa | 0 | |
| | bb | 0 | |
| | bb cc | 0 | |
| | dd | 0 | |
| | ee | 0 | |
| | ff | 0 | |
| | g | 0 | |
| | h | 0 |
| | ii | 0 | |
| | kkkkk | 1.486977219581604 | |
| | kkkkk | 1.486977219581604 | |
+-- +
11 rows in set (0.00 sec)
Mysql > select * from T2 where match (b) against ('kkkkk')
+-+
| | a | b | c | |
+-+
| | 10 | kkkkk | jj33333333333333333 |
| | 10 | kkkkk | kkkkk adfsdf |
+-+
2 rows in set (0.00 sec)
-- Natural language search
Mysql > select * from T2 where match (b) against ('kkkkk' in natural language mode)
+-+
| | a | b | c | |
+-+
| | 10 | kkkkk | jj33333333333333333 |
| | 10 | kkkkk | kkkkk adfsdf |
+-+
2 rows in set (0.00 sec)
-- Boolean search
Mysql > select * from T2 where match (b) against ('kkkkk' in boolean mode)
+-+
| | a | b | c | |
+-+
| | 10 | kkkkk | jj33333333333333333 |
| | 10 | kkkkk | kkkkk adfsdf |
+-+
2 rows in set (0.01sec)
-- Boolean only includes 1 and 0
Mysql > select b match (b) against ('kkkkk' in boolean mode) from T2
+-+
| | b | match (b) against ('kkkkk' in boolean mode) |
+-+
| | aa | 0 | |
| | bb | 0 | |
| | bb cc | 0 | |
| | dd | 0 | |
| | ee | 0 | |
| | ff | 0 | |
| | g | 0 | |
| | h | 0 |
| | ii | 0 | |
| | kkkkk | 1 | |
| | kkkkk | 1 | |
+-+
11 rows in set (0.00 sec)
Mysql >
-- Multi-column search for the same word match (col1,col2) against ('word')
Search for multiple words in a column, match (col) against ('word1 word2')
-- Boolean search is different from natural language search: 1, naturally sorted by relevance, Boolean is not, 2, for Boolean search, the 50% rule does not work
-- Boolean two functions, against (that is, the search word) can have a +-sign, + indicates yes. -means no. For example, + word1-word2 means there is a word word1 but no word2.
-- full-text search parameters, ft_min_word_len and ft_max_word_len, minimum and maximum length of search words
