MySQL限制MySQL字段的值(允许一个特定值和多个其他值)？

Question

今天我遇到了一个棘手的问题，我想限制字段中某些值的外观，这是我的表：

CREATE TABLE `test_table` (
  `id` varchar(40) COLLATE utf8_bin NOT NULL,
  `create_time` datetime NOT NULL DEFAULT '2010-01-01 00:00:00',
  `user_id` varchar(40) COLLATE utf8_bin NOT NULL,
  `device_id` varchar(40) COLLATE utf8_bin NOT NULL,
  `is_owner` boolean NOT NULL,
  `user_nickname` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_device` (`device_id`,`is_owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

例如，我们有两个用户Bob和Tom，以及一个设备Echo，user_id、device_id和is_owner之间的关系是：

1. 设备只能有一个所有者，这意味着只允许一对(设备: Echo，is_owner: true)。
2. 设备可以有多个用户--，这就是说允许这对设备的多个外观(设备: Echo，is_owner: false)。

在我的情况下，唯一的约束不会有帮助，请帮助。

Answer 1

如果您使用null而不是false来表示用户不是所有者，那么您可以使用唯一的索引来提供这个限制，因为在MySQL中，唯一索引允许多个空值。显然，您需要使is_owner字段为空，这样才能正常工作。

否则，此控件更好地放置在应用层，而不是数据库中。在数据库中，可以使用触发器检查此条件，并防止引发sql错误设置额外的所有者记录。

Answer 2

解决方案可能是使用mysql触发器而不是约束。Cou可以将触发器绑定到某些事件，并防止或修改发生的操作。通常，如果您想要对具有复杂多样性的数据建模，约束将不会对您有所帮助。下面是一个示例(未测试)如何为您的问题创建MYSQL触发器：

CREATE TRIGGER my_trigger BEFORE INSERT ON test_table 
    FOR EACH ROW
BEGIN
  IF NEW.is_owner = TRUE AND EXISTS (SELECT * FROM test_table WHERE device_id = NEW.device_id AND is_owner = TRUE) THEN
    SIGNAL sqlstate '99999'
        SET message_text = "Can't insert because an other owner exists for that device";
  END IF;
END$$

在本例中，当有人试图创建具有所有权和相同设备id的第二个条目时，将创建一个SQL错误。您可以查看手册以获得更多信息。

Answer 3

对于用户和设备(所有者与否)之间的独特关系，您应该对device_id和user_id使用约束。使用此方案，您不能对“每个设备只有一个所有者”(或者至少我看不到一个所有者)有唯一的限制。