-- 创建表的适合添加全文索引 create table t_article ( id int primary key auto_increment, title varchar(255) , content varchar(1000) , writing_date date, FULLTEXT (id, title,content,writing_date);-- 创建全文检索 ); 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'); -- 修改表结构添加全文索引 -- 添加全文索引 -- 使用全文索引 检索 内容中包含了 ’you‘
时间: 2024-03-03 19:49:54 浏览: 14
这段代码创建了一个名为 `t_article` 的表,包含 `id`、`title`、`content` 和 `writing_date` 四个字段。通过在 `FULLTEXT` 子句中指定这些字段,创建了一个全文索引。
```
CREATE TABLE t_article (
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255),
content VARCHAR(1000),
writing_date DATE,
FULLTEXT (id, title, content, writing_date)
);
INSERT INTO t_article VALUES
(null, "Yesterday Once More", "When I was young I listen to the radio", '2021-10-01'),
(null, "Right Here Waiting", "Oceans apart, day after day, and I slowly go insane", '2021-10-02'),
(null, "My Heart Will Go On", "every night in my dreams, i see you, i feel you", '2021-10-03'),
(null, "Everything I Do", "Look into my eyes, You will see what you mean to me", '2021-10-04'),
(null, "Called To Say I Love You", "say love you no new year's day, to celebrate", '2021-10-05'),
(null, "Nothing's Gonna Change My Love For You", "if i had to live my life without you near me", '2021-10-06'),
(null, "Everybody", "We're gonna bring the flavor show U how.", '2021-10-07');
```
如果要在已经存在的表上添加全文索引,可以使用 `ALTER TABLE` 语句:
```
ALTER TABLE t_article ADD FULLTEXT (id, title, content, writing_date);
```
要使用全文索引查询包含特定关键词的记录,可以使用 `MATCH AGAINST` 函数:
```
SELECT * FROM t_article WHERE MATCH (content) AGAINST ('you');
```
这将返回所有 `content` 列中包含关键词 `'you'` 的记录。可以通过指定 `IN BOOLEAN MODE` 来进一步控制匹配方式。例如:
```
SELECT * FROM t_article WHERE MATCH (content) AGAINST ('you' IN BOOLEAN MODE);
```
这将返回所有 `content` 列中包含单词 `'you'` 的记录。