这段代码创建了一个名为 `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'` 的记录。