mysql全文搜索fulltext
发表于:2024-09-30 作者:千家信息网编辑
千家信息网最后更新 2024年09月30日,mysql> select * from t2;+------+------+----------------------------+| a | b | c |+------+------+----
千家信息网最后更新 2024年09月30日mysql全文搜索fulltextmysql> select * from t2;
+------+------+----------------------------+
| a | b | c |
+------+------+----------------------------+
| 1 | aa | aa11111111111111111111111 |
| 2 | bb | bb222222222222222222222222 |
+------+------+----------------------------+
2 rows in set (0.00 sec)
mysql>
--此处测试字符少于4个并且数据只有2行,搜索不到任何记录
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(3,'bb 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,'dd','dd33333333333333333');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(5,'ee','ee33333333333333333');
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> insert into t2 values(7,'g','gg33333333333333333');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(8,'h','hh43333333333333333');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(9,'ii','ii33333333333333333');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(10,'jj','jj33333333333333333');
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)
--大于4个字符可以搜索出来
mysql> insert into t2 values(10,'kkkkkwq','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.01 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 * from t2 where match(b) against('kkkkk');
+------+-------+---------------------+
| a | b | c |
+------+-------+---------------------+
| 10 | kkkkk | jj33333333333333333 |
| 10 | kkkkk | kkkkk adfsdf |
+------+-------+---------------------+
2 rows in set (0.00 sec)
--默认自然语言,搜索结果按相关度排序,此处查询结果为相关度
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)
--自然语言搜索
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)
--布尔型搜索
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.01 sec)
--布尔型只包括1和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>
--多列搜索同一单词为 match(col1,col2) against('word'),
--一列搜索多个单词,match(col) against('word1 word2')
--布尔型搜索与自然语言搜索区别:1,自然按相关度排序,布尔不是,2,对于布尔型搜索,50%规则不起作用
--布尔型两个功能,against里(即搜索的单词)可有+ -号,+表示有。-表示没有,比如 +word1 -word2表示有单词word1但没有word2
--全文搜索参数,ft_min_word_len和ft_max_word_len,搜索单词的最小长度和最大长度
+------+------+----------------------------+
| a | b | c |
+------+------+----------------------------+
| 1 | aa | aa11111111111111111111111 |
| 2 | bb | bb222222222222222222222222 |
+------+------+----------------------------+
2 rows in set (0.00 sec)
mysql>
--此处测试字符少于4个并且数据只有2行,搜索不到任何记录
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(3,'bb 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,'dd','dd33333333333333333');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(5,'ee','ee33333333333333333');
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> insert into t2 values(7,'g','gg33333333333333333');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(8,'h','hh43333333333333333');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(9,'ii','ii33333333333333333');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values(10,'jj','jj33333333333333333');
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)
--大于4个字符可以搜索出来
mysql> insert into t2 values(10,'kkkkkwq','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.01 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 * from t2 where match(b) against('kkkkk');
+------+-------+---------------------+
| a | b | c |
+------+-------+---------------------+
| 10 | kkkkk | jj33333333333333333 |
| 10 | kkkkk | kkkkk adfsdf |
+------+-------+---------------------+
2 rows in set (0.00 sec)
--默认自然语言,搜索结果按相关度排序,此处查询结果为相关度
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)
--自然语言搜索
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)
--布尔型搜索
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.01 sec)
--布尔型只包括1和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>
--多列搜索同一单词为 match(col1,col2) against('word'),
--一列搜索多个单词,match(col) against('word1 word2')
--布尔型搜索与自然语言搜索区别:1,自然按相关度排序,布尔不是,2,对于布尔型搜索,50%规则不起作用
--布尔型两个功能,against里(即搜索的单词)可有+ -号,+表示有。-表示没有,比如 +word1 -word2表示有单词word1但没有word2
--全文搜索参数,ft_min_word_len和ft_max_word_len,搜索单词的最小长度和最大长度
搜索
布尔
单词
自然
相关度
自然语言
语言
字符
结果
长度
排序
全文
最大
最小
两个
作用
功能
参数
只有
多个
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
软件开发信息管理系统
服务器安全狗认证
服务器怎么删除人物
ar技术教育数据库
短期网络技术加盟项目
漯河协同软件开发有限公司
234中表和数据库的关系是
猪八戒游戏软件开发
互联网科技领域下的业主大会
和谐网络技术有限公司
网络安全考rhce
时间季度表数据库
四级查询显示没有数据库
记事狗 数据库
海康监控服务器报警
无线网络技术的发展概括
网络安全意识心得体会小学
网络安全与执法容易挂科吗
重复行只打印第一行数据库
I排版软件开发
数据库工程师数据流图
最近dota2服务器很卡
河南启辰网络技术
位于用户程序和数据库之间的
广东电商软件开发价格
互联网科技杂志都有哪些
学校开展的网络安全宣传
网络安全技术与应用下载
温州嵌入式软件开发亿连信
docker搭建大数据库