使用mySQL将密钥链接到多个条目

I'm creating a search function for my website, and i've therefore taken keywords from the main table, and summarized these as keys in a secondary table linking to the main table via id.
My setup looks like this:
-----------------------------------------------------
| ITEMS    |
-----------------------------------------------------
| Id            | Name           | Price                |
-----------------------------------------------------
| 1             | CPU              | 199.95            |
| 2             | GPU              | 249.95            |
| 3             | GPU              | 225.95            |
-----------------------------------------------------

-----------------------------------------------------
| KEYS      |
-----------------------------------------------------
| Id            | item_name   | item_id           |
-----------------------------------------------------
| 1             | CPU              | 1                     |
| 2             | GPU              | 2                     |
| 3             | GPU              | 3                     |
-----------------------------------------------------
Bear in mind my table is more complex, generating many keys for each row.
My problem is that if there are a lot of duplicate keywords, the keywords table becomes very large. My question is therefore:
Is it possible to somehow group the id's in mySQL for each unique item_name so if someone searches for GPU the out put will be 2 and 3?
I'm thinking you can separate the values in a string using eg. a comma like so:
-----------------------------------------------------
| 2             | GPU              | 2,3                  |
-----------------------------------------------------
However I feel it gets complicated when for instance an item in items is deleted and I need to update my keys table and maybe remove an Id=5 from the string "1,3,5,7,10,15"...
Is there a smart way of linking same name keys to multiple entries? Or will I always need a regular expression, and maybe even PHP, to separate the values?

我正在为我的网站创建一个搜索功能,因此我从主表中获取了关键字,并将这些关键字汇总到通过id链接到主表的辅助表中。我的设置如下所示:-------------------------------------------- --------- |项目| -------------------------------------------------- --- | Id |名称|价格| -------------------------------------------------- --- | 1 | CPU | 199.95 | | 2 | GPU | 249.95 | | 3 | GPU | 225.95 | -------------------------------------------------- --- ----------------------------------------------- ------ | KEYS | -------------------------------------------------- --- | Id | item_name | item_id | -------------------------------------------------- --- | 1 | CPU | 1 | | 2 | GPU | 2 | | 3 | GPU | 3 | -------------------------------------------------- ---请记住,我的表格更复杂,每行都会生成许多键。我的问题是,如果有很多重复的关键字,关键字表变得非常大。因此我的问题是:是否有可能以某种方式将mySQL中的id分组为每个唯一的item_name,所以如果有人搜索GPU,那么输出将是2和3?我想你可以使用eg分隔字符串中的值。像这样的逗号:--------------------------------------------- -------- | 2 | GPU | 2,3 | -------------------------------------------------- ---但是当我删除项目中的项目并且我需要更新我的密钥表并且可能从字符串“1,3,5,7,10,15”中删除Id = 5时,我觉得它变得复杂。 ..是否有一种智能的方法将相同的名称键链接到多个条目?或者我是否总是需要一个正则表达式,甚至PHP,来分隔值?

1 个解决方案

#1

Do not use CSV in a database table, but use a join instead to select the data.

不要在数据库表中使用CSV,而是使用连接来选择数据。

You should be able to generate the output like so:

您应该能够像这样生成输出:

select count(*) as cnt
      ,k.item_name
      ,group_concat(k.item_id) as ids
from keys k
inner join items i ON (i.id = k.id)
where i.price = 199.95

The group_concat will generate the CSV on the fly, so you don't have to store CSV in the database. If you need more than one row of output, you can use a group by clause.

group_concat将动态生成CSV,因此您不必将CSV存储在数据库中。如果需要多行输出,则可以使用group by子句。

See: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Note that this has nothing to do with regex.

请注意,这与正则表达式无关。

#1