如何在 MySQL 中选择包含多个值的行? [英] How do I select a row in MySQL that contains multiple values?

问题描述

我有一个像这样的 MySQL 表:

I have a MySQL table that looks like this:

Table: Designer
  id: integer  
  name: String
  gallery: string

画廊行可以包含一个字符串值，如 23,36,45

The gallery row can contain a string value like 23,36,45

现在我想做一个类似这样的查询:

Now I want to do a query similar to this:

SELECT * FROM Designer WHERE gallery = '36'

我知道我可以使用 LIKE，但这还不够精确.这可能会返回 36 和 136.

I know I kan use LIKE, but that is not precices enough. That could return both 36 and 136.

我也知道我可以创建另一个链接设计师和画廊的表格.但由于这不会是一个巨大的表，我将外国画廊 ID 密钥添加到画廊行.我现在很懒;)

I also know I could create another table which links designer and gallery. But since this is not going to be a huge table, I'm adding the foreign gallery ID Key to the gallery row. And I'm lazy right now ;)

那么如何选择数字为 36 的行?

So how can I select a row that has the number 36?

更新
好吧，因为我因为糟糕的设计而受到指责(是的，我知道是这样)，我现在明白了.

UPDATE
Ok, since I'm getting nailed for poor design (yes I know it was), I See the obvious now.

一个设计师可以拥有多个画廊，但一个画廊只能属于一个设计师.因此，我只需要将设计者 ID 作为外键添加到图库表中.

A designer can have many galleries, but a gallery can only belong to one designer. Therefore I only need to add designer ID as a foreign key to the gallery table.

简单.但是当它是凌晨 3 点并且你已经工作了 15 个小时时并不总是合乎逻辑;)

Simple. But not always logical when it's 3AM and you've been workign for 15 hours ;)

推荐答案

你真的不应该那样存储你的数据，因为它会使查询效率极低.对于该特定用例，您可以(如果您不关心性能)使用:

You really shouldn't store your data like that since it makes queries horribly inefficient. For that particular use case, you can (if you don't care about performance) use:

select * from designer
    where gallery like '36,%'
       or gallery like '%,36'
       or gallery like '%,36,%'
       or gallery = '36'

这将减轻您对部分匹配的担忧，因为诸如 136 之类的内容不会匹配任何这些条件，但任何位置的 36 都会匹配.

This will alleviate your concerns about partial matches since something like 136 will not match any of those conditions but 36 in any position will match.

然而，尽管您提出了相反的抗议，但您应该做的是重新设计您的架构，例如:

However, despite your protestations to the contrary, what you should do is re-engineer your schema, something like:

designer:
    id             integer primary key
    name           varchar(whatever)
designer_galeries:
    designer_id    integer foreign key references designer(id)
    gallery        string
    primary key    (designer_id,gallery)

那么您的查询速度会非常快.您应该学习的第一件事之一是始终为第三范式设计架构.一旦您了解了权衡取舍(并知道如何减轻问题)，您就可以恢复到其他形式的性能，但除非您的数据库非常复杂，否则很少有必要这样做.

Then your queries will be blindingly fast. One of the first things you should loearn is to always design your schema for third normal form. You can revert to other forms for performance once you understand the trade-offs (and know how to mitigate problems) but it's rarely necessary unless you database is horribly convoluted.

这篇关于如何在 MySQL 中选择包含多个值的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！