MySQL使用全文索引

最新推荐文章于 2021-07-21 16:52:06 发布
最新推荐文章于 2021-07-21 16:52:06 发布
阅读量169 收藏
点赞数
分类专栏： MySQL 日常维护 常用命令 文章标签： MySQL 全文索引
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Gjikcheng/article/details/98383049
版权

1. 全文索引

2. 创建全文索引

2.1. 创建测试表

   CREATE TABLE article ( 
                  id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
                  title VARCHAR(200), 
                  body TEXT, 
                  FULLTEXT(title, body) 
              );

2.2. 添加全文索引

  • alter table 方式
ALTER TABLE `article` ADD FULLTEXT INDEX title_idx  (`title`);
ALTER TABLE `article` ADD FULLTEXT body_idx  (`body`);
  • create 方式
CREATE FULLTEXT INDEX title_idx ON `article` (`title`);
--也可以在创建索引的时候指定索引的长度:
CREATE FULLTEXT INDEX body_idx ON `article` (`body`(20));
  • 删除全文索引
DROP INDEX body_idx ON dsg.article ;
DROP INDEX title_idx ON dsg.article ;`
  • alter table 方式
ALTER TABLE dsg.article DROP INDEX title_idx;
ALTER TABLE dsg.article DROP INDEX body_idx;

2.3. 插入数据

insert into dsg.article (title,body) values('serch','大圣');
insert into dsg.article (title,body) values('serch','齐天大圣');
insert into dsg.article (title,body) values('serch','齐天大圣,孙悟空');
insert into dsg.article (title,body) values('serch','齐天大圣孙悟空');
insert into dsg.article (title,body) values('serch','齐天大圣啊!');
insert into dsg.article (title,body) values('serch','齐天大圣,大圣');
insert into dsg.article (title,body) values('serch','齐天大,圣');
insert into dsg.article (title,body) values('serch','good 孙悟空');
insert into dsg.article (title,body) values('serch','hello 孙悟空');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');
insert into dsg.article (title,body) values('serch','hello');

insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');

insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');
insert into dsg.article (title,body) values('serch','good');

3. 使用全文索引

3.1. 使用全文索引的格式： MATCH (columnName) AGAINST (‘string’)

eg:

SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')
  • 当查询多列数据时：

::: alert-info
建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的。
SELECT * FROM student WHERE MATCH(name,address) AGAINST(‘聪 广东’)`
:::
::: alert-info

50%的门坎限制(当查询结果很多，几乎所有记录都有，或者极少的数据，都有可能会返回非所期望的结果)

–>可用IN BOOLEAN MODE即可以避开50%的限制。

此时使用全文索引的格式就变成了：

SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪' IN BOOLEAN MODE)

:::

3.2. . ft_boolean_syntax (+ -><()~*:""&|)使用的例子：

  • + : 用在词的前面，表示一定要包含该词，并且必须在开始位置。
    eg: +Apple 匹配：Apple123, “tommy, Apple”
  • - : 不包含该词，所以不能只用「-yoursql」这样是查不到任何row的，必须搭配其他语法使用。
    eg: MATCH (girl_name) AGAINST (’-林志玲 +张筱雨’)
  • 空(也就是默认情况)，表示可选的，包含该词的顺序较高。
    例子：

apple banana 找至少包含上面词中的一个的记录行
+apple +juice 两个词均在被包含
+apple macintosh 包含词 “apple”，但是如果同时包含 “macintosh”，它的排列将更高一些
+apple -macintosh 包含 “apple” 但不包含 “macintosh”

3.3. > :提高该字的相关性，查询的结果会排在比较靠前的位置。`

3.4. < :降低相关性，查询的结果会排在比较靠后的位置。

4. 进行查询

  • 不使用<>
mysql> select * from article where match(body) against('齐天大圣' in boolean mode);
+----+-------+------------------------+
| id | title | body                   |
+----+-------+------------------------+
|  6 | serch | 齐天大圣               |
|  7 | serch | 齐天大圣,孙悟空        |
| 10 | serch | 齐天大圣,大圣          |
+----+-------+------------------------+
3 rows in set (0.00 sec)

完全匹配比较靠前.

  • 使用>
mysql> select * from article where match(body) against('齐天大圣 >孙悟空' in boolean mode);
+----+-------+------------------------+
| id | title | body                   |
+----+-------+------------------------+
|  7 | serch | 齐天大圣,孙悟空        |
| 12 | serch | good 孙悟空            |
| 13 | serch | hello 孙悟空           |
|  6 | serch | 齐天大圣               |
| 10 | serch | 齐天大圣,大圣          |
+----+-------+------------------------+
5 rows in set (0.00 sec)

发现孙悟空优先级比较高,先显示.

  • 使用<
 mysql> select * from article where match(body) against('齐天大圣 <孙悟空');
+----+-------+------------------------+
| id | title | body                   |
+----+-------+------------------------+
|  7 | serch | 齐天大圣,孙悟空        |
|  6 | serch | 齐天大圣               |
| 10 | serch | 齐天大圣,大圣          |
| 12 | serch | good 孙悟空            |
| 13 | serch | hello 孙悟空           |
+----+-------+------------------------+
5 rows in set (0.00 sec)

单独使用< 也是优先级较高.

  • 同时使用><

mysql> select * from article where match(body) against('大圣 >齐天大圣,大圣 <孙悟空 <齐天大圣啊');
+----+-------+------------------------+
| id | title | body                   |
+----+-------+------------------------+
|  7 | serch | 齐天大圣,孙悟空        |
|  9 | serch | 齐天大圣啊!            |
|  6 | serch | 齐天大圣               |
| 10 | serch | 齐天大圣,大圣          |
| 12 | serch | good 孙悟空            |
| 13 | serch | hello 孙悟空           |
+----+-------+------------------------+
6 rows in set (0.00 sec)

总结:
::: alert-info

  1. 只要使用 ><的总比没用的 靠前；

  2. 使用 >的一定比 <的排的靠前 (这就符合相关性提高和降低)；

  3. 使用同一类的，使用的越早，排的越前。
    :::

4.1. 使用() 调整优先级

eg: +aaa +(>bbb <ccc)
找到有aaa和bbb和ccc，aaa和bbb，或者aaa和ccc(因为bbb，ccc前面没有+，所以表示可有可无)，

然后 aaa&bbb > aaa&bbb&ccc > aaa&ccc

4.2. . ~ :将其相关性由正转负，表示拥有该字会降低相关性，但不像「-」将之排除，只是排在较后面。

eg:   +apple ~macintosh   先匹配apple，但如果同时包含macintosh，就排名会靠后。

4.3. * :通配符，这个只能接在字符串后面。

MATCH (girl_name) AGAINST ('+*ABC*')   #错误，不能放前面
MATCH (girl_name) AGAINST ('+张筱雨*')  #正确

4.4. . " " :整体匹配，用双引号将一段句子包起来表示要完全相符，不可拆字。

eg: “tommy huang” 可以匹配 tommy huang xxxxx 但是不能匹配 tommy is huang。

4.5. 扩展查询

 select * from article where match(body) against('good' with query expansion);
+-----+-------+------------------------+
| id  | title | body                   |
+-----+-------+------------------------+
|  12 | serch | good 孙悟空            |
|   7 | serch | 齐天大圣,孙悟空        |
|  13 | serch | hello 孙悟空           |
|  66 | serch | good                   |
|  67 | serch | good                   |
|  68 | serch | good                   |
|  69 | serch | good                   |
|  70 | serch | good                   |
|  71 | serch | good                   |
|  72 | serch | good                   |

结果里包含孙悟空.

左佳明
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL创建全文索引分享
12-16
MySQL全文索引是一种提高数据库查询性能的技术，尤其适用于大规模文本数据的检索。它通过分词技术和特定的算法，分析文本中的关键词频率和重要性，从而快速定位到匹配的记录。在MySQL中，全文索引主要应用于MYISAM...
mysql fulltext 分页_在MYSQL中运用全文索引(FULLTEXT index)
weixin_34326484的博客
01-19 282
在MYSQL中使用全文索引(FULLTEXT index)MYSQL的一个很有用的特性是使用全文索引(FULLTEXT index)查找文本的能力.目前只有使用MyISAM类型表的时候有效(MyISAM是默认的表类型,如果你不知道使用的是什么类型的表,那很可能就是 MyISAM).全文索引可以建立在TEXT,CHAR或者VARCHAR类型的字段,或者字段组合上.我们将建立一个简单的表用来解释各种特...
Msql修改like全模糊匹配方案-全文索引
funyule
07-21 395
Msql修改like全模糊匹配方案-全文索引 作者 版本 时间 曾鸿雁 1.0 2021-7-21 背景 当前存在表： CREATE TABLE `user` ( `uid` bigint NOT NULL AUTO_INCREMENT COMMENT '用户编号', `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '真实姓名', `mobile`
DSL
江黎
04-20 320
# 查看索引 GET /_cat/indices?v # 查看index/type中的数据 GET /forum/article/_search # 查看index/type的映射 GET /forum/_mapping/article # 通过_id查询 GET /forum/article/1 # 查看分词结果 GET /forum/_analyze { "field": "articl...
MySQL全文索引应用简明教程
12-16
MySQL全文索引是一种高效检索文本数据的机制，它在处理大量文本数据的查询时能显著提升性能。全文索引在数据库设计中起着至关重要的作用，尤其对于那些需要执行复杂文本搜索的应用程序。 首先，创建全文索引需要...
MySQL索引的创建与使用
02-24
表示索引为全文索引。SPATIAL:可选。表示索引为空间索引。INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。索引名:可选。给创建的索引取一个新名称。字段名1:指定索引对应的字段的名称，该...
ERROR 3009 (HY000) 解决办法
aming
03-27 8886
ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 43. Created with MySQL 5 错误是由于你曾经升级过数据库，升级完后没有使用 mysql_upgrade升级数据结构造成的。 解决办法： 使用mysql_upgrade命令 root@localhost ~]# mysql_...
MySQL 的数据库错误日志设置
aming
04-29 7422
1. 数据库错误日志设置 概述 这篇文章主要讨论怎样配置MySQL 的诊断日志信息.还有对于设置错误信息的字符集和语言设置. 这些错误日志包含了MySQL 启动和关闭的次数.也包含了错误,警告,和注释的相关诊断信息. MySQL 在运行时,如果你的MySQL 中的表需要自动检查或者修复.这些信息都会写入到error log 里面. 在某些的操作系统,错误日志还包含MySQL 非正常的退出的堆栈信息...
MySQL 的 DNS 解析 和host cache
aming
08-03 3887
1. DNS 解析 和host cache | 概述 MySQL 服务将会维护内存中的host cache ,host cache 包含的信息为:IP,地址,主机名,和错误信息. host cache 的内容是公开的,可以用select 语句进行查询,以便诊断当前的连接问题. 1.1. 启用host cache 有以下好处. 通过查找缓存找到IP 和主机名的对应结果,可以避免每个clinet ...
使用systemd 管理MySQL
aming
08-03 2710
1. 使用systemd 管理MySQL 如果使用通用二进制安装发行版安装MySQL ,可以参考安装章节配置systemd 对mysql 的支持. 如果使用源码版本进行安装请添加编译项-DWITH_SYSTEMD=1 :::alert-warning 注意: 在systemd 类型的平台上安装MySQL ,mysql_safe 脚本是不需要的.所有的自动重启和诊断systemd 方式都已经做了. ...
MySQL 开启大页内存
aming
08-03 1599
1. 开启大页内存 概述: 有些硬件或操作系统支持大于默认值的内存页(通常为4KB),这个时取决于底层硬件和操作系统.执行大页内存可以检查设备缓冲区的丢失.也可以改进性能. MySQL 使用Innodb 可以使用大页内存为缓冲池和额外的内存池分配内存. MySQL 可以使用大页内存最大为4M ,在Solaris 下可以允许使用256m 内存的页面. MySQL 支持Linux 下的大页...
MySQL 链接参数说明
aming
03-27 1062
1. 连接参数 1.1. activate_all_roles_on_login Property Value Command-Line Format --activate-all-roles-on-login Introduced 8.0.2 System Variable [activate_all_roles_on_login](server-administrati...
MySQL 的统计直方图
aming
08-03 723
1. 统计直方图 1.1. 概览 MySQL8.0实现了统计直方图。利用直方图，用户可以对一张表的一列做数据分布的统计，特别是针对没有索引的字段。这可以帮助查询优化器找到更优的执行计划。统计直方图的主要使用场景是用来计算字段选择性，即过滤效率。 可以通过以下方式来创建或者删除直方图： -- 创建直方图统计 ANALYZE TABLE dept_emp UPDATE HISTOGRAM ON to_...
MySQL 查询慢的sql 的记录日志设置
aming
08-03 508
1. 查询慢的sql 的记录日志设置 1.1. 概述 sql 语句慢速查询日志将会记录到查询非常慢的sql . 这些sql 语句的执行时间都是超过long_query_time , 且达到指定行数(min_examined_row_limit) . long_query_time 最小值和默认值 分别为0 到 10 ,可以指定值为微秒级别, case1: 记录到文件则时间为微秒. case2: 对...
KVM MYSQL 8.0.13 单机 FOR OEL7.5 源码编译安装文档
aming
11-09 386
2. 安装流程 2.1. 确定平台版本和机器位数。 确定当前MYSQL版本对应各种操作系统 平台是否支持。 https://www.mysql.com/support/supportedplatforms/database.html 2.2. 下载对应版本 2.2.1. 下载二进制安装包  官方网站  http://dev.mysql.com/downloads/mirrors.html. ...
KVM MYSQL 8.0.13 单机 FOR OEL7.5 通常安装文档
aming
11-09 357
2. 安装流程 2.1. 确定平台版本和机器位数。 确定当前MYSQL版本对应各种操作系统 平台是否支持。 https://www.mysql.com/support/supportedplatforms/database.html 2.2. 下载对应版本 2.2.1. 下载二进制安装包  官方网站  http://dev.mysql.com/downloads/mirrors.html. ...
MySQL的服务端程序说明
aming
08-03 288
1. 服务端程序 1.1. mysqld 程序 mysqld 为服务damon 程序。 检查所有MySQL 的程序选项 mysqld --verbose --help 1.2. mysqld_safe mysqld_safe 脚本添加乐一些安全措施,例如发生错误时重启服务器,并将信息记录到错误日志中. mysqld_safe 尝试启动一个名为mysqld 的可执行文件. 使用–mysqld...
KVM MYSQL 8.0.13 单机 FOR OEL7.5 docker安装文档
aming
11-09 229
2. 安装流程 2.1. 确定平台版本和机器位数。 确定当前MYSQL版本对应各种操作系统 平台是否支持。 https://www.mysql.com/support/supportedplatforms/database.html 2.2. 下载对应版本 2.2.1. 下载二进制安装包  官方网站  http://dev.mysql.com/downloads/mirrors.html. ...
mysql 全文索引使用
最新发布
12-12
MySQL的全文索引可以用于在文本数据上进行高效的全文搜索。以下是MySQL全文索引的使用方法： 1. 创建全文索引 在创建表时，可以通过FULLTEXT关键字来创建全文索引。例如： ```sql CREATE TABLE articles ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT(title, body) ) ENGINE=InnoDB; ``` 这将在articles表的title和body列上创建一个全文索引。 2. 修改表结构添加全文索引 如果表已经存在，可以使用ALTER TABLE语句来添加全文索引。例如： ```sql ALTER TABLE articles ADD FULLTEXT(title, body); ``` 这将在articles表的title和body列上添加一个全文索引。 3. 使用全文索引进行搜索 可以使用MATCH AGAINST语句来在全文索引上进行搜索。例如： ```sql SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('数据库 MySQL' IN BOOLEAN MODE); ``` 这将返回所有包含“数据库”和“MySQL”这两个词的文章。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值