MySQL - 如何执行此字符串搜索? [英] MySQL - How can I perform this string search?

问题描述

我有一个表，其中包含 37 个不同类型的列，包括 INT 和 VARCHARS 以及 2 个 LONGTEXT 列.客户端希望通过搜索来搜索表并找到匹配的行.

I have a table which features 37 columns, of various types, INTs and VARCHARS and 2 LONGTEXT columns. The client wants a search to search the table and find the rows that match.

但是，我遇到了这个问题.这是我到目前为止所做的:

However, I'm having trouble with this. Here is what I've done so far:

1) 我最初的想法是做大量的 OR 查询——但是我被推迟了，因为我需要提供大约 30 次的搜索数据，这是大量的重复，我敢肯定；比这更好的方法.

1) My initial view was to do a massive set of OR queries - however I was put off this by the fact I would need to supply the search data ~30 times, which is massive repetition and I'm sure there;s a better way than this.

代码:

SELECT
  MemberId,
  MemNameTitle,
  MemSName,
  MemFName,
  MemPostcode,
  MemEmail
FROM
  MemberData
WHERE
  MemFName LIKE CONCAT('%',?,'%') OR
  MemSName LIKE CONCAT('%',?,'%') OR
  MemAddr LIKE CONCAT('%',?,'%') OR
  MemPostcode LIKE CONCAT('%',?,'%') OR
  MemEmail LIKE CONCAT('%',?,'%') 
 ...etc...

等等.等等.那是一组庞大的 OR 并且非常笨拙.

Etc. Etc. That's a massive set of OR's and really unwieldy.

2)我想我会尝试重新修改它以将所有列放在括号中，然后只询问一次查询，我在 SO 上看到了一段类似的代码，但不确定它是否正常工作，但这是一个灵感，至少:

2) I thought I'd try and rework it to place all the columns in brackets and then only ask the query once, I saw a similar piece of code on SO but not sure that was correctly working, but it was an insprition, at least:

    SELECT
      MemberId,
      MemNameTitle,
      MemSName,
      MemFName,
      MemPostcode,
      MemEmail
    FROM
      MemberData 
    WHERE
      (MemNameTitle OR
       MemFName OR
       MemSName OR
       MemAddr OR
       MemPostcode OR
       MemEmail OR
       MemSkype OR
       MemLinkedIn OR
       MemFacebook OR
       MemEmailTwo ...etc...) LIKE CONCAT('%',?,'%')
     GROUP BY
       MemberId 

此代码执行时没有明显错误，但由于始终不返回结果而失败，如返回 0 个字段.我不明白为什么，从最初的角度来看，

This code executes without apparent error but fails as it always returns no result, as in 0 fields returned. I can't see why, from an initial view,

3) 因此，通过对操作系统的一些研究，我发现使用 IN 关键字进行了重新排列，但是从这里的先前问题 是否可以将 LIKE 和 IN 用于 WHERE 语句? 它似乎不起作用.

3) So, with some research on OS I found a rearrangement using the IN keyword, but from previous questions on here Is it possible to use LIKE and IN for a WHERE statment? it appeared not to work.

我想要的是:

SELECT
  MemberId,
  MemNameTitle,
  MemSName,
  MemFName,
  MemPostcode,
  MemEmail
FROM
  MemberData
WHERE
  MemNameTitle,
  MemFName,
  MemSName,
  MemAddr,
  MemPostcode,
  MemEmail,
  MemSkype,
  MemLinkedIn,
  ...etc ...
  MemFax,
  MemberStatus,
  CommitteeNotes,
  SecondAddr,
  SecondAddrPostcode IN (LIKE CONCAT('%',?,'%')  )

这是粗略的语法，但我希望您能理解我想要的想法，我想使用 LIKE % % 子句搜索相同值的多个字段.字段有多种 TEXT/VARCHAR 类型.

This is crudy syntax but I hope you get the idea I want to get, I want to search many fields for the same value using a LIKE % % clause. Fields are variously TEXT/VARCHAR types.

4) 然后我查看了 MySQL 全文搜索，但这很快就变得无用了，因为这仅适用于 TEXT 类型而不是 VARCHAR 类型搜索.在每次搜索之前，我都考虑过将每个 VARCHAR 列更改为 TEXT 列，但我认为这也是相对处理器密集型的，并且对于许多人必须想做的搜索来说似乎不合逻辑?

4) I then looked into MySQL full text searches but this quickly became useless as this is only applied to TEXT type rather than VARCHAR type searching. I considered before each search changing each VARCHAR column to a TEXT column but figured that was also be relatively processor intensive and seemed illogical for a search that many people must want to do?

所以，我没有想法了..... 你能帮我这样搜索吗?或者建议为什么我在尝试 2 中的代码总是返回零行?

So, I'm out of ideas..... Can you help me search this way? Or suggest why my code in attempt 2 always returns Zero rows?

干杯

5) 我一直在考虑重新排列 IN 子句语句并想出了这个:

5) I have been looking at rearranging the IN clause statement and came up with this:

    SELECT *(lazy typing!) WHERE
     CONCAT('%',?,'%') IN
 (MemNameTitle, 
MemFName, 
MemSName, 
MemAddr, 
MemPostcode, 
MemEmail, 
MemSkype, 
...etc...
    CommitteeNotes,
 SecondAddr,
 SecondAddrPostcode) 
GROUP BY MemberId 

然而这会返回一个结果，但结果总是表的最后一行.这不起作用.

However this returns a result, but the result is always the last row of the table. This doesn't work.

从 Ravinder 开始，对所有字段使用 CONCAT_WS - 这在我的情况下有效，尽管我觉得CONCAT 有点难看，但很好.

From Ravinder, using CONCAT_WS for all the fields - this works in my case, although something in my mind does find CONCATs somewhat ugly, but oh well.

SELECT * FROM MemberData WHERE
 CONCAT_WS('<*!*>', 
MemNameTitle, MemFName, MemSName, 
MemAddr, MemPostcode, MemEmail, 
MemSkype, MemLinkedIn,
...etc...
MemberStatus, CommitteeNotes, SecondAddr,
 SecondAddrPostcode)
 LIKE CONCAT('%',?,'%') 
GROUP BY MemberId ";

表格最终会有几千行，我有点担心，因为这个查询会为每次搜索为表格中的每一行连接 24 列，这很容易变得非常昂贵和低效(即缓慢)，所以如果有人有任何方法

The table will eventually have a few thousand rows, and I am a little worried that as this query will concat 24 columns for each row on the table for each search, that this could easily become quite expensive and inefficient (ie slow), so if anyone has any ways of either

i) 不使用 CONCAT 列进行搜索或

i) searching without CONCAT columns or

ii) 使这个解决方案更快/更有效

ii) making this solution faster/ more efficient

请分享！！

推荐答案

有一个变通的解决方案.但我觉得这太粗糙了，性能可能没有那么好.

There is a workaround solution. But I feel this is too crude and performance may not be that good.

where
 concat_ws( "<*!*>", col1, col2, col3, ... ) like concat( '%', ?, '%' )

在这里，我使用 '<*!*>' 作为分隔符示例.
您必须使用模式字符串作为分隔符，您确定，

Here, I used '<*!*>' just as an example separator.
You have to use a pattern string as separator which, you are sure that,

1. 不是占位符值的一部分或
2. 当有 2 列或更多列时，它不是生成字符串的一部分串联

参考文档:

MySQL:CONCAT_WS(分隔符,str1,str2,...)
它不会跳过空列值，而是跳过 NULL.

MySQL: CONCAT_WS(separator,str1,str2,...)
It won't skip empty column values but NULLs.

这篇关于MySQL - 如何执行此字符串搜索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！