SQLServer 创建全文索引（两种方法）

柔光的暖阳◎ 2023-10-05 08:26 13阅读 0赞

#### 1 全文索引向导添加 ####

（1）选择表格右击-->全文索引-->定义全文索引

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70][]

（2）选择索引

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 1][]

（3）选择索引列

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 2][]

（4）选择跟踪（自动）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 3][]

（5）选择目录、索引文件组合非索引字表

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 4][]

（6）定义填充计划（可选）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 5][]

（7）全文索引向导说明

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 6][]

（8）创建全文索引成功

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 7][]

#### 2 SQL语句添加 ####

（1）打开索引支持

--打开索引支持
    execute SP_FULLTEXT_DATABASE 'Enable';

（2） 建立全文目录

--建立全文目录
    execute sp_fulltext_catalog 'users_directory','create';

（3） 建立全文索引

--为users表建立全文索引数据元,PK__users__F3DBC5737F60ED59是该表中由主键所建立的唯一索引
    execute sp_fulltext_table 'users','create','users_directory','PK__users__F3DBC5737F60ED59';

（4）设置全文索引列

--设置全文索引列名username
    execute sp_fulltext_column 'users','username','add';

（5）激活全文索引

--激活全文索引
    execute sp_fulltext_table 'users','activate';

（6） 填充全文索引目录

--填充全文索引目录
    execute sp_fulltext_catalog 'users_directory','start_full';

#### 3 测试全文索引是否创建成功 ####

select * from users where contains(username,'%12%');

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200814234150946.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200814234315381.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200814234354187.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200814234452313.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200814234640337.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200814234742452.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200814234825768.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/2020081423492627.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4,size_16,color_FFFFFF,t_70