代码之家 › 专栏 › 技术社区 › Raghav

使用SQL搜索关键字的最佳实践

sql-server sql

Raghav · 技术社区 · 17 年前

我有三张桌子

[USER] --Master user table 
[KEYWORD] --Master keyword table  
[USER_KEYWORD] --[USER]-[KEYWORD] mapping table

下面是我的数据库中的结构

GO
--master user table
CREATE TABLE [USER]
(
    [USERID] INT IDENTITY,
    [NAME] VARCHAR(50)
)
GO

--master keyword table
CREATE TABLE [KEYWORD]
(
    [KEYWORDID] INT IDENTITY,
    [KEYWORD] VARCHAR(50)
)

GO
--x table user_keyword
CREATE TABLE [USER_KEYWORD]
(
    [USERID] INT ,
    [KEYWORDID] INT
)

GO
--Insert data in master user table
INSERT INTO [USER]
SELECT 'TESTUSER1'
UNION ALL
SELECT 'TESTUSER2'
UNION ALL
SELECT 'TESTUSER3'
UNION ALL
SELECT 'TESTUSER4'

GO
--Insert data in master keyword table
INSERT INTO [KEYWORD]
SELECT 'ASP'
UNION ALL
SELECT 'ASP.NET 3.5'
UNION ALL
SELECT 'C#'
UNION ALL
SELECT 'JAVA'
UNION ALL
SELECT 'ASP.NET'
UNION ALL
SELECT 'SQL'
UNION ALL
SELECT 'SQL SERVER'
UNION ALL
SELECT 'SQL SERVER 2005'
UNION ALL
SELECT 'SQL SERVER 2008'

GO

--Insert data in user keyword table

INSERT INTO [USER_KEYWORD]
SELECT 1,1
UNION ALL
SELECT 2,2
UNION ALL
SELECT 3,3
UNION ALL
SELECT 4,4
UNION ALL
SELECT 1,2
UNION ALL
SELECT 2,3
UNION ALL
SELECT 3,4
UNION ALL
SELECT 4,1
UNION ALL
SELECT 2,3
UNION ALL
SELECT 3,4
UNION ALL
SELECT 4,6
UNION ALL
SELECT 3,6
UNION ALL
SELECT 4,6
UNION ALL
SELECT 2,7
UNION ALL
SELECT 3,8
UNION ALL
SELECT 4,9

GO


CREATE PROC TEST_SEARCH_KEYWORDS
@SEARCHKEYWORD VARCHAR(50)
AS
BEGIN

    SELECT K.[KEYWORD],COUNT(UK.[KEYWORDID]) AS [KEWWORDCOUNT] FROM [KEYWORD] K 
    INNER JOIN [USER_KEYWORD] UK
    ON K.[KEYWORDID]=UK.[KEYWORDID]
    WHERE K.[KEYWORD] LIKE (@SEARCHKEYWORD+ '%')
    GROUP BY K.[KEYWORD]

END


--TEST EXAMPLES

EXEC  TEST_SEARCH_KEYWORDS 'ASP'
--0UTPUT
KEYWORD                                            KEWWORDCOUNT
-------------------------------------------------- ------------
ASP                                                2
ASP.NET 3.5                                        2


--TEST EXAMPLES

EXEC  TEST_SEARCH_KEYWORDS 'SQL'
--0UTPUT
KEYWORD                                            KEWWORDCOUNT
-------------------------------------------------- ------------
SQL                                                3
SQL SERVER                                         1
SQL SERVER 2005                                    1
SQL SERVER 2008                                    1

我有一个叫SP的 测试搜索关键字 根据从外部提供的关键字搜索记录。当前我在sp中使用like条件。我想知道这对使用like从数据库中搜索记录很好,因为将来我的记录将继续增加。我不想使用全文搜索因为我还必须处理SQL Server 2000。

2 回复 | 直到 17 年前

Adamski 17 年前

一个(需要存储)解决方案是预先计算并存储一个数字关键字前缀,与实际的关键字一起使用。然后,根据查找条件,您可以对相关前缀列(如果存在)进行直接相等比较,或者返回到执行“相似”比较。

例如,假设强制限制搜索词必须至少包含3个字符。可以创建列prefix3chars、prefix4chars、prefix5chars。假设用户输入搜索词“hello”,您的数据库中包含关键字“hello,world”。您将选择在prefix5chars列中搜索,并与关键字“hello,world”匹配。如果用户使用关键字“hello,w”进行搜索,您将返回到使用“like”进行搜索。

KM. 17 年前

如果你一直像这样搜索:

WHERE K.[KEYWORD] LIKE (@SEARCHKEYWORD+ '%')

然后,您的查询将使用一个索引,并且在表增长时仍然可以很好地执行。但是,如果将其更改为:

WHERE K.[KEYWORD] LIKE ('%'+ @SEARCHKEYWORD+ '%')

然后它将不会,它将表扫描,这将导致不良的性能,因为您的表增长。