SQL - FULLTEXT INDEX
问题描述:
我目前正试图添加一个FULLTEXT索引,以便能够在包含关键词的叙述栏中搜索关键词。我已经尝试了许多不同的教程,虽然我完全复制它们,但我无法在“WHERE”部分中使用'CONTAINS'功能时复制结果。SQL - FULLTEXT INDEX
下面是我使用一些示例数据的脚本示例。有人能够向我解释为什么这不起作用,因为我正在认真努力地看到错误可能在哪里。
CREATE TABLE dbo.AAAA_FullTextTest5
(ID INT Identity(1, 1) NOT NULL
,LOCATION VARCHAR (255)
,NARRATIVE VARCHAR(MAX)
)
ALTER TABLE dbo.AAAA_FullTextTest5 add constraint PK_FullTextTest5 PRIMARY KEY (ID)
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('USA Texas', 'WRIGHT BROTHERS')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Worldwide', 'CROWN PROS Builders LTD')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Devon United Kingdom', 'J PJH ROOFING LTD')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('UK', 'Shoreham Port Authority and Shoreham Port Developments & The Port of Shoreham Trustees ')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('EUROPE AND RUSSIA', 'ZHY SERVICES')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Austrialia', 'BrotherLimited')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('ASIA', 'DRILLING SERVICES LTD')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('JAPAN', '*S WASTE LTD')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Melbourne', 'TRC Service Company')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('GERMANY & HOLLAND', 'Samson Group')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Frankfurt', 'SCAFFOLDING LTD')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Swindon, England', 'Scaffold Access Ltd')
CREATE FULLTEXT CATALOG FullTextTest5
CREATE FULLTEXT INDEX ON dbo.AAAA_FullTextTest5 (
Location language 1033
,Narrative language 1033)
KEY INDEX PK_FullTextTest5 ON FullTextTest5
WITH CHANGE_TRACKING AUTO
SELECT * FROM dbo.AAAA_FullTextTest5
WHERE CONTAINS(NARRATIVE, 'Access')
答
基于这里的说明:https://msdn.microsoft.com/en-us/library/ms142497.aspx,下面会出现在我的本地安装SQL Server 2008 R2中的正常工作,以及我的SQL Server的本地安装2012:
CREATE TABLE dbo.AAAA_FullTextTest5
(ID INT Identity(1, 1) NOT NULL
,LOCATION VARCHAR (255)
,NARRATIVE VARCHAR(MAX)
)
GO
CREATE UNIQUE INDEX UI_AAAA_FullTextTest5 ON dbo.AAAA_FullTextTest5(ID)
GO
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('USA Texas', 'WRIGHT BROTHERS')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Worldwide', 'CROWN PROS Builders LTD')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Devon United Kingdom', 'J PJH ROOFING LTD')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('UK', 'Shoreham Port Authority and Shoreham Port Developments & The Port of Shoreham Trustees ')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('EUROPE AND RUSSIA', 'ZHY SERVICES')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Austrialia', 'BrotherLimited')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('ASIA', 'DRILLING SERVICES LTD')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('JAPAN', '*S WASTE LTD')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Melbourne', 'TRC Service Company')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('GERMANY & HOLLAND', 'Samson Group')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Frankfurt', 'SCAFFOLDING LTD')
INSERT INTO dbo.AAAA_FullTextTest5 (LOCATION, NARRATIVE) VALUES ('Swindon, England', 'Scaffold Access Ltd')
GO
CREATE FULLTEXT CATALOG AAAA_FTCat;
GO
CREATE FULLTEXT INDEX ON AAAA_FullTextTest5
(
NARRATIVE --Full-text index column name
Language 2057 --2057 is the LCID for British English
)
KEY INDEX UI_AAAA_FullTextTest5 ON AAAA_FTCat --Unique index
WITH CHANGE_TRACKING AUTO --Population type;
GO
随着代替上述设置,然后我就可以查询:
SELECT * FROM AAAA_FullTextTest5
WHERE CONTAINS(NARRATIVE, 'Access')
而得到这样的结果集:
ID LOCATION NARRATIVE
12 Swindon, England Scaffold Access Ltd
哪个dbms? (这是产品特定的功能....) – jarlh 2015-03-25 12:29:50
你能澄清它为什么不起作用吗?任何错误,或只是不是你期望的结果(如果是的话)? – 2015-03-25 12:47:27
运行时不会出现错误。它只是没有结果被返回,我不明白为什么基于至少有一个结果与单词'访问'在其中。 – bwsutton 2015-03-25 13:49:26