MySQL - 选择所有其他列 = 0

Question

假设您有一个包含许多属性字段的表，例如：

id
datetime
q
a
c
d
.
.
.
(etc)

其中 q,a,c,d 等表示不定数量的 int 字段，其名称是生成的（也在运行时创建）

您如何选择限制某些特定字段为特定数字而所有其他字段均为 0？

（例如）a=1，c=5，其他都是0？

---

更新：

我想我要求的是是否有一些“整体”功能或者我可以“WHERE ALL OTHERS = 0”（或类似的东西）的功能。

标准化的问题是，即使在连接之后，我仍然必须检查特定的非零值以及所有其他字段（或标准化字段）是否为 0。

---

具体应用：

化学化合物表示

id
name
C
Fe
Cl
H
.
.
.
(etc - fields are added when new compound insert contains elements not listed)

例行：

name = Hydrochloric acid
C = 0
Fe = 0
Cl = 1
H = 1
.
.
. 
(everything else = 0)

使用简单的 SELECT * FROM table WHERE H=1 AND Cl = 1 的问题是，它还包括其他情况，例如 C Cl H（即使它有 C=1，它也会被选中）因为Cl和H都是1也）

这就是为什么我试图弄清楚如何在所有其他字段均为 0 的情况下选择它！

Answer 1

这不是一个现实的情况。您永远不会拥有无限数量的 int 字段，因为您不能在没有 ALTER 语句的情况下修改表（仅在运行时）。至少，你不应该。

Answer 2

实际上，表中的列数不可能无限 - 您的数量受到确定的限制。在这种情况下，您只需使用：

... where a = 1 and c = 5 and q = 0 and b = 0 and d = 0 and ...

如果您只是意味着数量是确定的，但应用程序使用的列数可能会发生变化，那么查询也需要在运行时生成，使用相同的信息。例如，如果在运行时未使用 d，它将被排除在查询之外。

但这是一个设计非常糟糕的表，您应该考虑更好地规范化它，例如将这些属性转移到一个单独的表（这个新表中每行一个属性）并使用 id< 将它们链接回原始表/code> 字段，例如：

BaseTable:
    id          integer        primary key
    datetime    timestamp
OtherTable:
    id          integer        references BaseTable(id)
    attribute   char(1)
    text        varchar(50)
    primary key (id,attribute)

这种模式将允许每个 id 上有任意属性。

---

根据您关于您根据某些外部因素更改表的评论，我认为这就是我们 DBA 世界中所说的“有趣的”设计决策:-)

我强烈建议您反对这样做，但是，如果您认为合适忽略该建议，您可以在大多数 DBA 实现中获取元数据。

例如，MySQL 有一个 COLUMNS 表 在 INFORMATION_SCHEMA 架构中，您可以使用它来枚举给定表的所有列。使用它，您可以基于任意复杂的表动态构造有效的 SQL 语句。

并不是说这是一个好想法，只是说它是可能的。

Answer 3

您可以构造这样的内容：

WHERE a=1
  AND c=5
  AND d=0
  AND e=0
  ...

或这样：

WHERE (a, c, d, e, ...) =
      (1, 5, 0, 0, ...)

---

没有什么比这样：

WHERE ALL OTHERS = 0

如果您反复更改表结构，则也应该动态更改查询。但这不是一个好的设计。您应该规范化表（可能通过将表拆分为 2 个表并将重复的列更改为一列，以便数据从多列变为一列但多行）。

---

OP编辑后更新

化学化合物-元素的可能标准化：

Compound
--------
CompoundId   Int          Primary Key
Name         Varchar(250) Unique
...other stuff

Element
-------
ElementCode  Char(2)      Primary Key
Name         Varchar(50)  Unique 

Structure
---------
CompoundId   Int 
ElementCode  Char(2) 
Ratio        Int
Primary Key (CompoundId, ElementCode) 
Foreign Key (CompoundId)
  References Compound(CompoundId)
Foreign Key (ElementCode)
  References Element(ElementCode)

使用示例数据：

CompoundId  Name 
------------------------------
 1          Hydrochloric acid
 2          Water
 3          Glucose

ElementCode  Name 
---------------------
 H           Hydrogen
 C           Carbon
 O           Oxygen 
 Cl          Chlorium 

CompoundId  ElementCode  Ratio 
------------------------------
 1           H            1
 1           Cl           1 
 2           H            2
 2           O            1
 3           H            12
 3           C            6
 3           O            6