MySQL 如何使sql搜索查询更强大？

How to make a sql search query more powerful?

提问by Amr Elgarhy

I wrote this sql query to search in a table:

我写了这个 sql 查询来在表中搜索：

SELECT * FROM TableName WHERE Name LIKE '%spa%'

The table contain these row for example:

该表包含这些行，例如：

1. Space Company.
2. Spa resort.
3. Spa hotel.
4. Spare Parts.
5. WithoutTheKeyword.

1. 太空公司。
2. 温泉度假村。
3. 温泉酒店。
4. 备件。
5. 没有关键字。

I want to know how to edit this query so it return the results sorted like this:

我想知道如何编辑这个查询，以便它返回这样排序的结果：

2 Spa resort

3 Spa hotel

1 Space Company

4 Spare Parts

2 温泉度假村

3 温泉酒店

1 太空公司

4 备件

Means the items which contain the exact word first then the like ones.

表示首先包含确切单词的项目，然后是类似的项目。

回答by Booji Boy

something like

就像是

Select * from TableName where Name Like 'Spa%'
ORDER BY case when soundex(name) = soundex('Spa') then '1' else soundex(name) end

should work ok.

应该可以正常工作。

actually this will work better

实际上这会更好

Select * from TableName where Name Like 'Spa%'
ORDER BY DIFFERENCE(name, 'Spa') desc;

FWIW I did some quick tests and if 'Name' is in a NONCLUSTERED INDEX SQL will use the index and doesn't do a table scan. Also, LIKE seems to use less resources than charindex (which returns less desirable results). Tested on sql 2000.

FWIW 我做了一些快速测试，如果“名称”在非聚集索引中，SQL 将使用索引而不进行表扫描。此外，LIKE 似乎比 charindex 使用更少的资源（返回不太理想的结果）。在 sql 2000 上测试。

回答by dkretz

You realize, I presume, that your schema just about eliminates any usefulness of indexes for these kinds of queries?

我猜你意识到你的模式几乎消除了索引对这类查询的任何用处？

A big problem is your "LIKE '%spa%'". Any "LIKE" key starting with a wildcard is an automatic table scan.

一个大问题是您的“LIKE '%spa%'”。任何以通配符开头的“LIKE”键都是自动表扫描。

---

---

编辑：我读到你的问题，说有一个字段，名称，字段值类似于“1 Space Company”、“2 Spa Resort”等，其中有一个数字后跟单词。并且您需要在搜索键前面使用通配符才能跳过数字部分。（这是为了澄清我的第一条评论。）我猜对了吗？

回答by Christopher Klein

Going off the top example, at least in MSSQL2005 changing the CLUSTERED to NONCLUSTERED will make it do a table scan. CLUSTERED gives you an index seek. Looks like it matches the conditions of the question.

离开上面的例子，至少在 MSSQL2005 中，将 CLUSTERED 更改为 NONCLUSTERED 将使其执行表扫描。CLUSTERED 为您提供索引查找。看起来它符合问题的条件。

CREATE TABLE tblTest(ID INT, colname VARCHAR(20) )
CREATE CLUSTERED INDEX tstidx1_tblTest ON tblTest(colname);
INSERT tblTest SELECT 1,'Space Company'
INSERT tblTest SELECT 2,'Spa Resort'
INSERT tblTest SELECT 3,'Spa Hotel'
INSERT tblTest SELECT 4,'Spare Parts'
INSERT tblTest SELECT 5,'WithoutTheKeyword'

SELECT * FROM tblTest WHERE colname LIKE 'Spa%'
ORDER BY DIFFERENCE(colname,'Spa') DESC;

DROP TABLE tblTest

回答by Tom H

You basically need to define (precisely) what your ranking function really is. What if you have a row that is "The Spa." or "spa.com"? Once you have that defined, you need to put that logic into your ORDER BY clause. For example:

您基本上需要（准确地）定义您的排名函数到底是什么。如果您有一排是“The Spa”怎么办。还是“spa.com”？定义之后，您需要将该逻辑放入 ORDER BY 子句中。例如：

SELECT
    name
FROM
    Some_Table
WHERE
    name LIKE '%spa%'
ORDER BY
    CASE
        WHEN name LIKE '% ' + @search_word + ' %' THEN 1   -- Notice the spaces
        ELSE 2
    END,
    name

Alternatively, you could write a ranking function and use that:

或者，您可以编写一个排名函数并使用它：

SELECT
    name
FROM
    Some_Table
WHERE
    name LIKE '%' + @search_word + '%'
ORDER BY
    dbo.GetNameMatchRank(name, @search_word)

Performance on very large result sets may not be too great, so this approach depends on your expected search result sizes.

在非常大的结果集上的性能可能不会太好，因此这种方法取决于您预期的搜索结果大小。

回答by MrTelly

The following should do the necessary, but it's inefficient, doing two full table selects and it also relies on your exact match being delimited by spaces. I think FullText indexing would help, but that has overheads of its own.

以下应该做必要的，但效率低下，做两个全表选择，它也依赖于你的精确匹配被空格分隔。我认为 FullText 索引会有所帮助，但这有其自身的开销。

select distinct * from
(
Select * from TableName 
   where CHARINDEX('spa ', Name) > 0
   or CHARINDEX(' spa', Name) > 0
Union
Select * from TableName 
  where Name Like '%spa%'
)

回答by Nathan Koop

This should work:

这应该有效：

Select * from TableName where Name Like '%spa%'
ORDER BY Name