如何在MySQL中用唯一约束防止范围重叠，确保BETWEEN查询仅返回单行？

要确保id_begins和id_ends组成的范围永不重叠，同时保证任意id查询仅返回一行，MySQL可以通过约束层面防重叠+查询层面稳返回来实现：

一、用触发器强制范围不重叠 #

MySQL没有原生的范围不重叠约束，但可以通过BEFORE INSERT和BEFORE UPDATE触发器，在数据插入或更新前检查范围是否与现有数据重叠，从源头上杜绝冲突。

1. 插入前检查触发器 #

DELIMITER //
CREATE TRIGGER check_range_overlap_insert
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
    -- 检查新范围是否与已有范围存在重叠（包含、被包含、部分重叠）
    IF EXISTS (
        SELECT 1 FROM your_table
        WHERE (NEW.id_begins BETWEEN id_begins AND id_ends)
           OR (NEW.id_ends BETWEEN id_begins AND id_ends)
           OR (id_begins BETWEEN NEW.id_begins AND NEW.id_ends)
           OR (id_ends BETWEEN NEW.id_begins AND NEW.id_ends)
    ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该范围与已有数据重叠，无法插入';
    END IF;
END //
DELIMITER ;

2. 更新前检查触发器 #

更新时需要排除当前行本身，避免误判：

DELIMITER //
CREATE TRIGGER check_range_overlap_update
BEFORE UPDATE ON your_table
FOR EACH ROW
BEGIN
    IF EXISTS (
        SELECT 1 FROM your_table
        WHERE id <> NEW.id -- 跳过当前更新的行
          AND (NEW.id_begins BETWEEN id_begins AND id_ends)
          OR (NEW.id_ends BETWEEN id_begins AND id_ends)
          OR (id_begins BETWEEN NEW.id_begins AND NEW.id_ends)
          OR (id_ends BETWEEN NEW.id_begins AND NEW.id_ends)
    ) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '更新后的范围与已有数据重叠，无法保存';
    END IF;
END //
DELIMITER ;

二、查询时确保仅返回一行 #

由于触发器已经保证范围无重叠，任意id最多只会匹配一行数据。为了更稳妥，查询时可以加上LIMIT 1强制返回单行：

SELECT * FROM your_table
WHERE target_id BETWEEN id_begins AND id_ends
LIMIT 1;

额外说明 #

如果业务要求范围是连续无间隔的（比如上一行的id_ends + 1等于下一行的id_begins），可以在触发器中额外添加连续性检查逻辑，但这属于业务层面的可选要求。

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