最全MySQL8.0实战教程 17 MySQL的索引 17.3 操作【8 全文索引 - 操作】

野性酷女 2024-03-26 18:28 26阅读 0赞

#### 最全MySQL8.0实战教程 ####

#### 文章目录 ####

*   *   *  最全MySQL8.0实战教程
         *  17 MySQL的索引
         *   *  17.3 操作【8 全文索引 - 操作】
             *   *  17.3.13 操作 - 数据准备
                 *  17.3.14 操作 - 创建索引
                 *  17.3.15 操作 - 使用索引

[【黑马程序员MySQL知识精讲+mysql实战案例\_零基础mysql数据库入门到高级全套教程】 ][MySQL_mysql_mysql_]

#### 17 MySQL的索引 ####

##### 17.3 操作【8 全文索引 - 操作】 #####

###### 17.3.13 操作 - 数据准备 ######

-- 创建表的时候添加全文索引
    create table t_article (
         id int primary key auto_increment ,
         title varchar(255) ,
         content varchar(1000) ,
         writing_date date -- , 
         -- fulltext (content) -- 创建全文检索【这里不直接进行创建，而是在插入完毕数据后再进行创建，这样效率更高】
    );

![在这里插入图片描述][b82dd4ee576740f2b8098d99a34f66b4.png_pic_center]

insert into t_article values(null,"Yesterday Once More","When I was young I listen to the radio",'2021-10-01');
    insert into t_article values(null,"Right Here Waiting","Oceans apart, day after day,and I slowly go insane",'2021-10-02'); 
    insert into t_article values(null,"My Heart Will Go On","every night in my dreams,i see you, i feel you",'2021-10-03');
    insert into t_article values(null,"Everything I Do","eLook into my eyes,You will see what you mean to me",'2021-10-04');
    insert into t_article values(null,"Called To Say I Love You","say love you no new year's day, to celebrate",'2021-10-05');
    insert into t_article values(null,"Nothing's Gonna Change My Love For You","if i had to live my life without you near me",'2021-10-06');
    insert into t_article values(null,"Everybody","We're gonna bring the flavor show U how.",'2021-10-07');

![在这里插入图片描述][c6cb5046c6434654bb107704b5d0525b.png_pic_center]

###### 17.3.14 操作 - 创建索引 ######

-- 修改表结构添加全文索引
    alter table t_article add fulltext index_content(content)
     
    -- 直接添加全文索引
    create fulltext index index_content on t_article(content);

![在这里插入图片描述][ab5ef26c4ef54d85ae8f0391bfb27fe0.png_pic_center]

查看设计表

![在这里插入图片描述][7f71bbc3fd6c4813ae57f75226f0a38f.png_pic_center]

###### 17.3.15 操作 - 使用索引 ######

使用全文索引

和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字，格式:

match (col1,col2,...)  against(expr [search_modifier])

select * from t_article where match(content) against('yo'); -- 没有结果 单词数需要大于等于3 
    select * from t_article where match(content) against('you'); -- 有结果

![在这里插入图片描述][5e6b2f61f8f94ea7963a3e8850b83a7e.png_pic_center]

![在这里插入图片描述][735fd565e7844a1abc8ed6842f45322e.png_pic_center]

[MySQL_mysql_mysql_]: https://www.bilibili.com/video/BV1iF411z7Pu
[b82dd4ee576740f2b8098d99a34f66b4.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/f65db393d8cf441da2ad0366cc025f36.png
[c6cb5046c6434654bb107704b5d0525b.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ad6298736ff84261afbf89e120d3689d.png
[ab5ef26c4ef54d85ae8f0391bfb27fe0.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/7eb5e0c048024f42aad8cd0ce2fe5e71.png
[7f71bbc3fd6c4813ae57f75226f0a38f.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c94b6be9615542e7833742c1318ec4c3.png
[5e6b2f61f8f94ea7963a3e8850b83a7e.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/45fd7164b65147d8a0f3b0ab21604c38.png
[735fd565e7844a1abc8ed6842f45322e.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/d98ee1a8281a49589c208bc5c0bafa1b.png