mysql - 对多个 NULL 有约束的 SQL 表

Question

我正在使用 MYSQL 并且我有一个设备表。每个设备都有一个名为 mac_address(这不是 pk)的 ID，以及一个名为 deleted_date 的列。

如果一个设备不活跃，它的deleted_date等于一个时间戳，然后添加一个具有相同mac_address<的新设备应该没问题。如果它处于事件状态，它的 deleted_date 等于 NULL，那么添加具有相同 mac_address 的新设备应该是不可行的。

我发现有可能有两个仍处于事件状态的设备具有相同的 mac_address，这是错误的。经过一些研究，我发现我无法将 NULL 与 NULL 进行比较，因为它们不算同一个值。

如果有解决方法，我将不胜感激。

• 具有相同 mac_address 且 deleted_date 为 NULL 的两台设备:错误
• 具有相同 mac_address 的两台设备，一台 deleted_date 为 NULL，另一台的值为:OK
• 具有相同 mac_address 和 deleted_date 的两个设备都有一个值:OK

现在这是表创建和约束的相关部分:

'CREATE TABLE `iot_devices` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) DEFAULT NULL,
  `device_name` varchar(100) NOT NULL,
  `delete_date` timestamp NULL DEFAULT NULL,
  `mac_address` varchar(12) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `idx_device_id` (`device_id`),
  UNIQUE KEY `idx_mac_address_delete_date` (`mac_address`,`delete_date`),
)

Answer 1

您不能单独对 INSERT 语句执行此操作，但可以使用触发器等。

drop trigger if exists t;
delimiter $$

create trigger t before insert on t
for each row
begin
        declare vcount int default 0;
        select ifnull(count(*),0) into vcount from t where mac_address = new.mac_address and delete_date is null; 
       if vcount <> 0 then
            SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'An error occurred';
        end if;
end $$
delimiter ;

请注意，您可能必须在更新时执行类似的操作。