如何在MySQL/MongoDB层面保证startDate与endDate日期间隔无重叠

核心约束逻辑：同一productID下，任意两个价格区间不可重叠，区间重叠的判断条件为：新记录.startDate < 已有记录.endDate AND 新记录.endDate > 已有记录.startDate

MySQL 实现方案 #

方案1：触发器实现（兼容所有MySQL版本） #

在INSERT、UPDATE操作前触发校验，发现重叠区间直接终止操作。
示例触发器代码：

-- 插入前校验触发器
DELIMITER //
CREATE TRIGGER check_price_interval_before_insert
BEFORE INSERT ON product_price
FOR EACH ROW
BEGIN
  IF EXISTS (
    SELECT 1 FROM product_price 
    WHERE productID = NEW.productID
    AND NEW.startDate < endDate
    AND NEW.endDate > startDate
  ) THEN
    SIGNAL SQLSTATE '45000' 
    SET MESSAGE_TEXT = '当前产品的价格区间与已有区间重叠，无法插入';
  END IF;
END //
DELIMITER ;

-- 更新前校验触发器逻辑和上面一致，只需修改触发器名、触发时机为BEFORE UPDATE即可

方案2：空间唯一索引实现（MySQL 5.7及以上支持） #

将日期区间转换为LINESTRING空间类型，利用空间唯一索引自动拦截重叠区间，性能比触发器更优。

-- 新增存储区间的空间字段
ALTER TABLE product_price ADD COLUMN date_range LINESTRING NOT NULL;
-- 批量计算存量数据的空间值（也可搭配触发器自动为新增/更新数据赋值）
UPDATE product_price SET date_range = LINESTRING(
  POINT(TO_DAYS(startDate), 0),
  POINT(TO_DAYS(endDate), 0)
);
-- 创建联合空间唯一索引
CREATE SPATIAL INDEX idx_product_date_range ON product_price(productID, date_range);

后续插入重叠区间时会直接触发唯一约束错误。

MongoDB 实现方案 #

方案1：原子写入操作（无并发冲突） #

把区间重叠判断逻辑写到查询条件里，用原子性写入操作保证不会插入重叠数据，适合绝大多数业务场景。
示例插入代码：

// 待插入的新价格记录
const newPrice = {
  productID: 1001,
  startDate: ISODate("2020-01-06T00:00:00Z"),
  endDate: ISODate("2020-01-07T00:00:00Z"),
  price: 99
};

// 原子写入：只有不存在重叠区间时才会执行插入
const result = db.productPrice.updateOne(
  {
    productID: newPrice.productID,
    $nor: [
      { startDate: { $lt: newPrice.endDate }, endDate: { $gt: newPrice.startDate } }
    ]
  },
  { $setOnInsert: newPrice },
  { upsert: true }
);

// 若result.upsertedCount为0则说明存在重叠区间，插入失败

方案2：Schema 校验（强制全局约束） #

创建集合时指定验证规则，所有写入操作都会自动校验，避免业务代码绕过约束。

db.createCollection("productPrice", {
  validator: {
    $expr: {
      $eq: [
        {
          $size: {
            $filter: {
              input: "$productPrice",
              cond: {
                $and: [
                  { $eq: ["$$this.productID", "$productID"] },
                  { $lt: ["$$this.startDate", "$endDate"] },
                  { $gt: ["$$this.endDate", "$startDate"] }
                ]
              }
            }
          }
        },
        0
      ]
    }
  },
  validationLevel: "strict",
  validationAction: "error"
})

内容的提问来源于stack exchange，提问作者Ravish