MYSQL搜索字段方法 [英] MYSQL search fields method

问题描述

请帮助，我对我的情况非常困惑。

我开始创建搜索功能，但我不确定最好的方法关于它。
在前端用户将能够在文本字段中输入单词，然后这些将搜索MYSQL数据库，如下所示：

< img src =https://i.stack.imgur.com/j5FxY.jpgalt =enter image description here>

，因此他们搜索Adult '，并且列出name列中的每个具有Adult的item_id（Primary Key）。或者他们输入黑色，并列出颜色中的每个item_id和黑色。或者他们进入成人蓝色，并且每个项目都有成人或蓝色。我相信你会得到这个想法。

我已经读了多个方法，但我不能弄清楚哪个是最好的：

使用 MANY TO ONE 表格：这似乎可以工作，但是有超过500个唯一的项目，所以将是成千上万的行。对于item_id 1，我必须为Adult创建一行，为Denim创建一行，为Pants创建一行，为black创建一行，为red创建一行，为blue创建一行。我可能只是硬编码的一切。

使用 FIND_IN_SET ：这会工作吗？我必须用逗号（如 Adult，Denim，Pants 和 EXPLODE ）来存储值以分隔值吗？我将尝试这种方法，但我继续读，在一个字段存储多个值是非常糟糕的做法。

或正则表达式我在寻找？

什么是存储值的最好方法，什么是最好的方法来检索它们？我不是要求确切的代码，只是使用的方法。任何建议是赞赏的！

解决方案

因此，如果我们假设列 name 和颜色是我们需要搜索的唯一的列，我会做以下（天真的解决方案，但会正常工作，如果你的DB没有数百万行和

首先，创建视图

  CREATE VIEW SearchHere AS 
 SELECT item_id，CONCAT（name，''，colors）AS FullDescription 
 FROM table 
  

我不知道屏幕截图中的表名，所以我使用 table 作为名称。 / p>

现在，如果用户搜索成人红裤，您可以发出查询

SELECT item_id
FROM SearchHere
WHERE FullDescription LIKE'％adult％'
AND FullDescription LIKE'％red％'
AND FullDescription LIKE'％pants％'


当然，您需要生成查询但这不是一个问题。您可以使用 AND 或或玩，并在wildcrad符号％和搜索项。也许你还想以更复杂的方式来做视图，例如，只要 CONCAT 就可以做更多。

Please help, I'm very confused about my situation.

I'm beginning to create a search function but I'm not sure the best way to go about it. On the front-end users will be able to enter words in a text-field, then these will search the MYSQL database, something like below:

so they search for 'Adult' and every item_id (Primary Key) with 'Adult' in column 'name' is listed. Or they enter 'black' and every item_id with 'black' in 'colors' is listed. or they enter 'Adult Blue' and every item with either 'Adult' or Blue would come up. I'm sure you get the idea.

I've read up on multiple methods, but I can't figure out which is best:

Using a MANY TO ONE table: This seems like it would work, but there are over 500 unique items, so that would be thousands and thousands of rows. For item_id 1 I would have to make a row for 'Adult', a row for 'Denim', a row for 'Pants', a row for 'black', a row for 'red', a row for 'blue'. I might as well just hard code everything.

Using FIND_IN_SET: Is this going to work? Would I have to store the values with commas like Adult,Denim,Pants and also EXPLODE to separate the values? I was going to try this method but I keep reading that storing multiple values in a field is very bad practice.

Or are Regular Expressions what I'm looking for?

What is the best way to store the values, and what is the best way to retrieve them? I'm not asking for exact code, just the methods to use. Any advice is appreciated!

解决方案

So, if we suppose that columns name and colors are the only columns we need to search through, I'd do the following (naive solution but will work fine if your DB doesn't have millions of rows and you don't have thousands of customers searching at once).

First, create a view

CREATE VIEW SearchHere AS
SELECT item_id, CONCAT(name, ' ', colors) AS FullDescription
FROM table

I don't know the name of the table in your screenshot, so I used table as its name.

Now, if a user searches for adult red pants you could issue a query

SELECT item_id
FROM SearchHere
WHERE FullDescription LIKE '%adult%'
AND FullDescription LIKE '%red%'
AND FullDescription LIKE '%pants%'

Of course, you'd need to generate the query on the fly but that's not an issue. You could play with using AND or OR and placing spaces in between the wildcrad symbol % and the search term. Probably you would also want to do the view in a more sophisticated way, e.g., do more tha just CONCAT.

这篇关于MYSQL搜索字段方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！