mysql全文搜寻fulltext

发布时间：2022-03-29 11:54 所属栏目：115 来源：互联网

导读：mysql select * from t2; +------+------+----------------------------+ | a | b | c | +------+------+----------------------------+ | 1 | aa | aa11111111111111111111111 | | 2 | bb | bb222222222222222222222222 | +------+------+-----------------

mysql> select * from t2;
+------+------+----------------------------+
| a    | b    | c                          |
+------+------+----------------------------+
|    1 | aa   | aa11111111111111111111111 |
|    2 | bb   | bb222222222222222222222222 |
+------+------+----------------------------+
2 rows in set (0.00 sec)

--此处测试字符少于4个并且数据只有2行，搜索不到任何记录
mysql> select match(c) against('bb') from t2;
+------------------------+
| match(c) against('bb') |
+------------------------+
|                      0 |
|                      0 |
+------------------------+
2 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql> insert into t2 values(4,'dd','dd33333333333333333');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values(6,'ff','ff33333333333333333');
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> commit;
Query OK, 0 rows 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> update t2 set b='kkkkk' where a=10;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0

--默认自然语言，搜索结果按相关度排序，此处查询结果为相关度
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' in natural language mode);
+------+-------+---------------------+
| a    | b     | c                   |
+------+-------+---------------------+
|   10 | kkkkk | jj33333333333333333 |
|   10 | kkkkk | kkkkk adfsdf        |
+------+-------+---------------------+
2 rows in set (0.00 sec)

（编辑：ASP站长网）