mysql核心知识之全文索引的使用

最新推荐文章于 2024-07-25 16:44:49 发布
最新推荐文章于 2024-07-25 16:44:49 发布
阅读量1.9k 收藏 1
点赞数
分类专栏： mysql 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43432438/article/details/124934303
版权

什么是全文索引？

全文索引是将存储在数据库中的文章或者句子等任意内容信息查找出来的索引，单位是词。全文索引也是目前搜索引擎使用的一种关键技术。指定为 fulltex

创建练习表的sql：

create table command (
id int(5) unsigned primary key  auto_increment,
name varchar(10),
instruction varchar(60)
)engine=MyISAM;

插入数据sql：

insert into command values('1','ls','list directory contents');
insert into command values('2','wc','print newline, word, and byte counts for each file');
insert into command values('3','cut','remove sections from each line of files');
insert into command values('4','sort','sort lines of text files');
insert into command values('5','find','search for files in a directory hierarchy');
insert into command values('6','cp','复制文件或者文件夹');
insert into command values('7','top','display Linux processes');
insert into command values('8','mv','修改文件名，移动');
insert into command values('9','停止词','is,not,me,yes,no ...');

添加全文索引：
创建表的时候创建全文索引

mysql> create table command (
id int(5) unsigned primary key  auto_increment,
name varchar(10),
instruction varchar(60),
fulltext(instruction)
)engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> desc command;
+-------------+-----------------+------+-----+---------+----------------+
| Field       | Type            | Null | Key | Default | Extra          |
+-------------+-----------------+------+-----+---------+----------------+
| id          | int(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | varchar(10)     | YES  |     | NULL    |                |
| instruction | varchar(60)     | YES  | MUL | NULL    |                |
+-------------+-----------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

mysql> show index from command;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| command |          0 | PRIMARY     |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| command |          1 | instruction |            1 | instruction | NULL      | NULL        | NULL     | NULL   | YES  | FULLTEXT   |         |               |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.04 sec)

通过alter添加
alter table command add fulltext(instruction);

mysql> show create table command;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                              |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| command | CREATE TABLE `command` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `instruction` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> alter table command add fulltext(instruction);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table command;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                           |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| command | CREATE TABLE `command` (
  `id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `instruction` varchar(60) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `instruction` (`instruction`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> show index from command;
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| command |          0 | PRIMARY     |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
| command |          1 | instruction |            1 | instruction | NULL      | NULL        | NULL     | NULL   | YES  | FULLTEXT   |         |               |
+---------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.04 sec)

使用全文索引：

select * from 表名 where match (字段名) against (‘检索内容’);

mysql> select * from command;
+----+--------+----------------------------------------------------+
| id | name   | instruction                                        |
+----+--------+----------------------------------------------------+
|  1 | ls     | list directory contents                            |
|  2 | wc     | print newline, word, and byte counts for each file |
|  3 | cut    | remove sections from each line of files            |
|  4 | sort   | sort lines of text files                           |
|  5 | find   | search for files in a directory hierarchy          |
|  6 | cp     | 复制文件或者文件夹                                 |
|  7 | top    | display Linux processes                            |
|  8 | mv     | 修改文件名，移动                                   |
|  9 | 停止词 | is,not,me,yes,no ...                               |
+----+--------+----------------------------------------------------+
9 rows in set (0.04 sec)

mysql> select * from command where match(instruction) against('directory');
+----+------+-------------------------------------------+
| id | name | instruction                               |
+----+------+-------------------------------------------+
|  1 | ls   | list directory contents                   |
|  5 | find | search for files in a directory hierarchy |
+----+------+-------------------------------------------+
2 rows in set (0.04 sec)

查看匹配度：

mysql> select id,match(instruction) against('directory') from command;
+----+-----------------------------------------+
| id | match(instruction) against('directory') |
+----+-----------------------------------------+
|  1 |                      1.2109839916229248 |
|  2 |                                       0 |
|  3 |                                       0 |
|  4 |                                       0 |
|  5 |                      1.1976701021194458 |
|  6 |                                       0 |
|  7 |                                       0 |
|  8 |                                       0 |
|  9 |                                       0 |
+----+-----------------------------------------+
9 rows in set (0.04 sec)

停止词：

is,not,me,yes,no …

mysql> select * from command;
+----+--------+----------------------------------------------------+
| id | name   | instruction                                        |
+----+--------+----------------------------------------------------+
|  1 | ls     | list directory contents                            |
|  2 | wc     | print newline, word, and byte counts for each file |
|  3 | cut    | remove sections from each line of files            |
|  4 | sort   | sort lines of text files                           |
|  5 | find   | search for files in a directory hierarchy          |
|  6 | cp     | 复制文件或者文件夹                                 |
|  7 | top    | display Linux processes                            |
|  8 | mv     | 修改文件名，移动                                   |
|  9 | 停止词 | is,not,me,yes,no ...                               |
+----+--------+----------------------------------------------------+
9 rows in set (0.03 sec)

mysql> select * from command where match(instruction) against('is');
Empty set

mysql> select * from command where match(instruction) against('me');
Empty set
	
mysql> select * from command where match(instruction) against('no');
Empty set

mysql> select * from command where match(instruction) against('not');
Empty set

mysql> select * from command where match(instruction) against('of');
Empty set

mysql> select * from command where match(instruction) against('and');
Empty set

mysql> select * from command where match(instruction) against('each');
Empty set

in boolean mode 模式：

in boolean mode：意思是指定全文检索模式为布尔全文检索（简单可以理解为是检索方式）

select * from 表名 where match (字段名) against (‘检索内容’ in boolean mode);

‘+’一定包含
‘-’一定不包含
‘ ’或
‘’通配符 ：若单词以 * 前面的字串开始，则匹配
使用通配符时，只能放在词的后边，不能放前边。

用法可以参考这篇博文：in boolean mode 符号含义

mysql> select * from command;
+----+--------+----------------------------------------------------+
| id | name   | instruction                                        |
+----+--------+----------------------------------------------------+
|  1 | ls     | list directory contents                            |
|  2 | wc     | print newline, word, and byte counts for each file |
|  3 | cut    | remove sections from each line of files            |
|  4 | sort   | sort lines of text files                           |
|  5 | find   | search for files in a directory hierarchy          |
|  6 | cp     | 复制文件或者文件夹                                 |
|  7 | top    | display Linux processes                            |
|  8 | mv     | 修改文件名，移动                                   |
|  9 | 停止词 | is,not,me,yes,no ...                               |
+----+--------+----------------------------------------------------+
9 rows in set (0.04 sec)

mysql> select * from command where match(instruction) against('dir*' in boolean mode);
+----+------+-------------------------------------------+
| id | name | instruction                               |
+----+------+-------------------------------------------+
|  1 | ls   | list directory contents                   |
|  5 | find | search for files in a directory hierarchy |
+----+------+-------------------------------------------+
2 rows in set (0.05 sec)

mysql> select * from command where match(instruction) against('dir');
Empty set

mysql> select * from command where match(instruction) against('dir* -hie*' in boolean mode);
+----+------+-------------------------+
| id | name | instruction             |
+----+------+-------------------------+
|  1 | ls   | list directory contents |
+----+------+-------------------------+
1 row in set (0.03 sec)

mysql> select * from command where match(instruction) against('dir* linux' in boolean mode);
+----+------+-------------------------------------------+
| id | name | instruction                               |
+----+------+-------------------------------------------+
|  1 | ls   | list directory contents                   |
|  5 | find | search for files in a directory hierarchy |
|  7 | top  | display Linux processes                   |
+----+------+-------------------------------------------+
3 rows in set (0.04 sec)

删除全文索引：

alter table 表名 drop index 字段名;

mysql> alter table command drop index instruction;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc command;
+-------------+-----------------+------+-----+---------+----------------+
| Field       | Type            | Null | Key | Default | Extra          |
+-------------+-----------------+------+-----+---------+----------------+
| id          | int(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | varchar(10)     | YES  |     | NULL    |                |
| instruction | varchar(60)     | YES  |     | NULL    |                |
+-------------+-----------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

mysql> show index from command;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| command |          0 | PRIMARY  |            1 | id          | A         |           0 | NULL     | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.04 sec)

注意点总结：

1、一般情况下创建全文索引的字段数据类型为 char、varchar、text 。其它字段类型不可以

mysql> alter table command add num int comment '号码';
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql> desc command;
+-------------+-----------------+------+-----+---------+----------------+
| Field       | Type            | Null | Key | Default | Extra          |
+-------------+-----------------+------+-----+---------+----------------+
| id          | int(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name        | varchar(10)     | YES  |     | NULL    |                |
| instruction | varchar(60)     | YES  | MUL | NULL    |                |
| num         | int(11)         | YES  |     | NULL    |                |
+-------------+-----------------+------+-----+---------+----------------+
4 rows in set (0.04 sec)

mysql> alter table command add fulltext(num);
1283 - Column 'num' cannot be part of FULLTEXT index

​
2、全文索引不针对非常频繁的词做索引。比如is，no，not，you，me，yes这些，我们称之为停止词
​
3、对英文检索时忽略大小写

爱卿呀
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL使用全文索引(fulltext index) 及中文全文索引使用
weixin_38289885的博客
05-07 7520
mysql 全文索引
5分钟掌握Mysql索引使用和失效.zip
07-27
4. **不合适的索引类型**：对于全文搜索或范围查询，普通B-Tree索引可能不够，需要使用专门的全文索引或索引类型。 5. **索引选择性低**：如果索引列的值过于重复，索引的选择性就低，数据库可能仍会选择全表扫描。...
mysql全文索引
weixin_41864357的博客
10-23 1785
mysql全文索引
数据库：全文索引实现技巧，架构师是这样实现的
Salted fish in hand, I have the world.
07-14 920
全文检索并没有你想象中的难，创建全文检索的核心思路是：初始化索引环境，为表字段创建索引，通过分词处理搜索文本，匹配索引中的词汇与记录行，最后根据匹配结果构建并返回相关数据。 肖哥弹架构 跟大家“弹弹” 数据库设计，需要代码关注 欢迎 点赞，点赞，点赞。 关注公号Solomon肖哥弹架构获取更多精彩内容 流程 整体思...
MySql全文索引
weixin_30483697的博客
03-03 219
使用索引是数据库性能优化的必备技能之一。在MySQL数据库中，有四种索引：聚集索引(主键索引)、普通索引、唯一索引以及我们这里将要介绍的全文索引(FULLTEXT INDEX)。 全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用「分词技术「等多种算法智能分析出文本文字中关键字词的频率及重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。在这里，我们就不追根...
Mysql全文索引的使用
clown_silence的博客
11-10 193
前言 在MySQL 5.6版本以前,只有MyISAM存储引擎支持全文引擎.在5.6版本中,InnoDB加入了对全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL内置了ngram全文解析器,用来支持亚洲语种的分词. 在学习之前,请确认自己的MySQL版本大于5.7.6.我的版本为5.7.20.同时文中的所有操作都基于InnoDB存储引擎. 什么是全文索引? 如果有搞过lucene,solr,es之类的,理解起来会方便许多. 日常我们使用MySQL查询时,大部分的查询都是定值或者范围查询.类似
mysql全文索引使用
哈士奇
04-29 4万+
一、前言       在以前的博客中小编介绍过mysql的执行流程，索引优化等。正好前一段时间项目有一个新的需求，就重新调研了一下mysql的全文索引，并对mysql的全文索引进行了压测，看看性能怎么样。以判断是否使用。——可想而知，性能不是很好。 下面小编就向大家再说说mysql的全文检索。    &nbs......
MySQL索引的各种类型
09-08
总之，MySQL索引的种类繁多，包括B-Tree索引、哈希索引以及全文索引等，每种都有其特定的用途和优势。理解这些索引的工作原理和应用场景，可以帮助我们更好地设计数据库，提升查询效率，从而优化整个系统的性能。
详解mysql权限和索引
09-09
全文索引适用于文本搜索，它创建了一个类似搜索引擎的结构，允许使用`MATCH AGAINST`进行更复杂的文本匹配，例如`SELECT * FROM table WHERE MATCH(column) AGAINST('keyword')`。全文索引仅能在`CHAR`、`VARCHAR`和...
mysql进阶知识整合
09-16
MySQL进阶知识整合涵盖了多个核心领域，包括事务管理、索引、SQL优化、视图、存储过程、锁、逻辑存储引擎和事务原理等。下面将逐一详细阐述这些知识点。 1. **事务**：事务是数据库操作的基本单位，确保一组操作的...
MySQL面试知识点追命连环问（二）事务、索引及SQL优化
12-14
MySQL是世界上最流行的关系型数据库管理系统之一，其在面试中...了解并掌握这些MySQL的核心知识点，不仅有助于应对面试，还能提升你在实际工作中的数据库管理能力。在实践中不断学习和磨炼，才能成为真正的数据库专家。
mysql 全文索引的使用
u014685642的博客
01-10 498
表格引擎设置：MyISAM 设置方法：打开Navicat 右键表单——》 表格设计——》 引擎 打开mysql 配置文件，笔者使用版本是8.0，安装目录并未找到 通过下面方式查找 打开mysql的工具类，然后右键，查看上图目标，找到my.ini 如下设置，用于设置索引最小颗粒度，其实就是搜索单词最小长度 [client] ft_min_word_len = 1 [mysqld] ft_min_word_len=1 innodb_ft_min_token_size=1 ng
MySQL 之全文索引
热门推荐
潜心做事的博客
04-14 11万+
最近在复习数据库索引部分，看到了 fulltext，也即全文索引，虽然全文索引在平时的业务中用到的不多，但是感觉它有点儿意思，所以花了点时间研究一下，特此记录。 引入 概念 通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。 你可能会说，用 like...
MySql 全文索引
最新发布
Cike___的博客
07-25 1704
Mysql 的全文索引主要用于全文字段的检索场景，支持 char、varchar、text 几个字段加全文索引，仅支持 InNoDB 与 MyISAM 引擎。
Mysql 使用全文索引
t0m的专栏
07-13 850
Mysql数据量达到百万级别以后，查询性能很低，尤其是在模糊查询方面，采用like %xxx%形式，速度难以接受，200数据，查询需要大约10秒，当然根据like的内容，如果采用limit形式，不使用order by，关键词出现频率高的查询效率会好一些，但是较为低的查询效率很是问题。 MySQL5.6之前 fulltext索引只支持MyISAM的表类型，5.6开始在InnoDB类型中开始
Mysql全文索引
weixin_33858249的博客
06-10 280
简介 　　1).MySQL中的全文索引是FultLeXT类型的索引。 　　2).全文索引只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR、TEXT列创建。 　　3).在MySQL 5.7.6中，MySQL提供了支持中文、日文和韩文（CJK）的内置全文ngram解析器，以及用于日文的可安装MeCab全文解析器插件 　　4).当创建表时，可以在CREATE TABLE语句中给出FU...
使用MySQL进行全文索引
ldw220817的专栏
12-23 1497
使用MYSQL进行全文索引 1. 背景 需要对数据库中某张表里的若干字段进行全文索引。 2. 分析 可以采用三种方法： 1. mysql全文索引 2. lucene进行索引 3. 将mysql数据库转为mongodb 最后决定使用mysql全文索引，省得编码了。 3. 步骤 基本步骤如下： 1. 需要修改数据库引擎，从innodb转为myiam 2. 建立全文索引
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值