向表中添加新的字段(列)
ALTER TABLE Document ADD(title VARCHAR(100) NOT NULL);
将第一行的title改为"home page":
UPDATE Document SET title = 'home page' WHERE url = 'index.html';
全文搜索:
首先创建数据库表单需要将类型设置为MyISAM。
CREATE TABLE fulltext_sample(copy TEXT,FULLTEXT(copy)) TYPE=MyISAM;
插入文本行:
INSERT INTO fulltext_sample VALUES
('It appears good from here'),
('The fureture and the past'),
('I love you'),
('All you need is love'),
('A good teacher');
使用全文搜索时需要使用MATCH和AGIANST关键字,MATCH指的是匹配那个字段,AGAINST则指明匹配的内容:
SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST('love');
结果将所有含有love字符的项给选择出来:
+----------------------+
| copy |
+----------------------+
| I love you |
| All you need is love |
+----------------------+
需要注意的是,全文搜索只匹配整个单词,如果是部分单词是没有结果的,例如:
SELECT * FROM fulltext_sample WHERE MATCH(copy) AGAINST('lov');
得到的结果是空的。
可以通过计算关联度来看匹配词与搜索内容的匹配度:
SELECT copy, MATCH(copy) AGAINST('love') AS relevance
FROM fulltext_sample WHERE MATCH(copy) AGAINST('love');
结果显示的匹配度为:
+----------------------+------------------+
| copy | relevance |
+----------------------+------------------+
| I love you | 0.40085528270084 |
| All you need is love | 0.40085528270084 |
+----------------------+------------------+
显示与所有内容的匹配度(关联度):
SELECT copy, MATCH(copy) AGAINST('love') AS relevance
FROM fulltext_sample;
结果为:
+---------------------------+------------------+
| copy | relevance |
+---------------------------+------------------+
| It appears good from here | 0 |
| The fureture and the past | 0 |
| I love you | 0.40085528270084 |
| All you need is love | 0.40085528270084 |
| A good teacher | 0 |
+---------------------------+------------------+
也可以匹配多个单词搜素:
SELECT copy, MATCH(copy) AGAINST('love,good') AS relevance
FROM fulltext_sample WHERE MATCH(copy) AGAINST('love,good');
结果为:
+---------------------------+------------------+
| copy | relevance |
+---------------------------+------------------+
| I love you | 0.40085528270084 |
| All you need is love | 0.40085528270084 |
| It appears good from here | 0.39634908797834 |
| A good teacher | 0.39634908797834 |
+---------------------------+------------------+
数据库学习Day4——全文搜索
最新推荐文章于 2024-08-22 19:03:44 发布