在MySQL中创建表约束

Question

我有一个表格，其中有一些列a、b、c，每个列都有另外一列(称为x、y、z)，分别依赖于a、b、c。当a、b、c中有任何一个值时，x、y、z的值将为1，如果a、b、c为null，则x、y、z会包含null。

例如，假设存储在a中的值为2，x是依赖它的列，那么x的值将为1。如果a的值为null，则x的值将为null。

那么，在创建表格时，是否有一种方式可以声明这个约束条件呢？

请提出除触发器之外的其他建议。

- Sashi Kant

---

大家谢谢你们的回复，但请建议除触发器之外的任何东西，因为我已经阅读过我们可以在创建表时放置一些约束。 - Sashi Kant

不可以。与大多数其他DBMS（Postgre、Oracle、SQL Server等）不同，MySQL不支持检查约束。 - Álvaro González

Answer 1

如果 x、y 和 z 的目的是简化某些查询，则您可以考虑使用视图来完成，而不是将它们作为表中的列。例如：

create view myview as
  select a, b, c,
  if (isnull(a), null, 1) as x,
  if (isnull(b), null, 1) as y,
  if (isnull(c), null, 1) as z
  from mytable;

然后，基于这个视图而不是直接基于表进行其他查询。

- mikej

---

嘿，感谢您的回复，但在那个表中有许多插入发生的地方，这是一个庞大的应用程序，我们有两个不同的平台... - Sashi Kant

请您能否详细说明这种方法，或者提供相关的参考资料。 - Sashi Kant

嗨。哪一部分不清楚？MySQL视图文档在这里。 - mikej

Answer 2

您要寻找的约束条件是检查约束。

CREATE TABLE test
(
    a varchar(10),
    b varchar(10),
    c varchar(10),
    x integer,
    y integer,
    z integer,
    CONSTRAINT chk_X_Nulls CHECK ((a is null and x is null) or (a is not null and x = 1)),
    CONSTRAINT chk_Y_Nulls CHECK ((b is null and y is null) or (b is not null and y = 1)),
    CONSTRAINT chk_Z_Nulls CHECK ((c is null and z is null) or (c is not null and z = 1))
);

很遗憾，MySQL中没有实现这个功能。自2004年以来就有一个未解决的问题报告，所以不要指望很快能看到它。

其他人已经回答了你可以使用触发器或视图来实现所需的结果，对于MySQL来说这些都是正确的答案。

您也可以使用一些简单的技巧部分约束数据：

• 将x、y、z的数据类型设置为enum('1')。这将防止插入除null和'1'之外的值，但不能确保值是正确的。
• 如果a、b、c具有有限的可能值范围，则可以创建对其他表的外键约束，并使用a、b、c的每个可能值填充这些表
• 您可以创建一个事件来定期更新x、y、z（例如每小时一次或每天一次）。如果x、y、z的值错误，则可以更正。

您可以在PostGreSQL上这里看到检查约束的实现。

如果您需要进一步的建议，请解释为什么触发器不适用于您的任务。

- dwurf

Answer 3

MySQL本身不处理CONSTRAINTS，但是您可以使用BEFORE INSERT和BEFORE UPDATE事件上的TRIGGER来实现类似的行为。然而，您将不得不依赖于一些其他表级约束（NOT NULL）来使其工作，如SO上的这个问题所述。

在您非常特定的情况下，看起来您想要使用触发器来计算触发器中x, y, z值的值，而不是使用它来防止插入具有“不适当”值的数据 - 但是您的问题并没有明确表达这一点，因此这取决于您真正想要什么。

- Romain

Answer 4

是的，您可以使用触发器来实现这个功能。

从触发器语法章节中可以看到：

如果BEFORE触发器失败，则不会执行相应行上的操作

尽管您所描述的情况暗示着数据未经过规范化处理。

- symcbean

Answer 5

除了使用约束条件，您还可以通过不存储x、y、z列并使用视图来实现类似的结果：

CREATE VIEW myView AS
SELECT
    a, b, c,
    ( a = a ) AS x,
    ( b = b ) AS y,
    ( c = c ) AS z
FROM myTable

- ypercubeᵀᴹ