MySQL无法将POINT坐标设为唯一/主键，如何在库层面实现坐标唯一性校验

方案1：生成列+联合唯一索引（优先推荐） #

POINT字段本质存储了经度、纬度两个独立数值，可通过MySQL 5.7+支持的存储生成列自动提取两个维度的数值，再添加联合唯一索引实现约束，无需修改现有业务读写POINT字段的逻辑：

ALTER TABLE 你的业务表名
-- 自动提取POINT的X值（经度），精度可根据业务需求调整DECIMAL参数
ADD COLUMN lon DECIMAL(10,7) GENERATED ALWAYS AS (ST_X(坐标字段名)) STORED,
-- 自动提取POINT的Y值（纬度）
ADD COLUMN lat DECIMAL(10,7) GENERATED ALWAYS AS (ST_Y(坐标字段名)) STORED,
-- 新增联合唯一索引
ADD UNIQUE INDEX idx_unique_lon_lat (lon, lat);

说明：STORED属性会将生成列的值持久化存储，新增/修改POINT字段时数据库会自动同步计算lon、lat的值，约束自动生效，原有空间查询逻辑完全不受影响。

方案2：触发器校验（适合不想新增字段的场景） #

可通过BEFORE INSERT、BEFORE UPDATE触发器，在数据写入前校验坐标唯一性，不符合条件直接中断操作：

插入前校验触发器 #

DELIMITER //
CREATE TRIGGER trg_unique_point_before_insert
BEFORE INSERT ON 你的业务表名
FOR EACH ROW
BEGIN
  DECLARE duplicate_count INT;
  -- 用ST_EQUALS函数精确匹配POINT是否一致
  SELECT COUNT(*) INTO duplicate_count
  FROM 你的业务表名
  WHERE ST_EQUALS(坐标字段名, NEW.坐标字段名);
  IF duplicate_count > 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '禁止插入重复坐标点';
  END IF;
END //
DELIMITER ;

更新前校验触发器 #

DELIMITER //
CREATE TRIGGER trg_unique_point_before_update
BEFORE UPDATE ON 你的业务表名
FOR EACH ROW
BEGIN
  DECLARE duplicate_count INT;
  SELECT COUNT(*) INTO duplicate_count
  FROM 你的业务表名
  WHERE ST_EQUALS(坐标字段名, NEW.坐标字段名)
  -- 排除当前正在更新的行本身
  AND 主键字段名 != NEW.主键字段名;
  IF duplicate_count > 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '禁止更新为重复坐标点';
  END IF;
END //
DELIMITER ;

说明：如果表数据量较大，建议给坐标字段添加普通空间索引加速ST_EQUALS查询，降低触发器校验的性能损耗。

方案3：坐标转换为单数值唯一列（适合偏好单字段唯一索引的场景） #

你提到康托尔配对函数不适用于负值，可先给经纬度添加固定偏移量消除负值，放大对应倍数转换为整数后再配对为单个数值，存储为BIGINT类型后添加唯一索引：

ALTER TABLE 你的业务表名
-- 示例按保留7位小数精度处理，经度偏移180消除负值，纬度偏移90消除负值
ADD COLUMN coord_unique_id BIGINT UNSIGNED
GENERATED ALWAYS AS (
  CAST((ST_X(坐标字段名) + 180) * 10000000 AS UNSIGNED) * 3600000000 +
  CAST((ST_Y(坐标字段名) + 90) * 10000000 AS UNSIGNED)
) STORED,
ADD UNIQUE INDEX idx_unique_coord_id (coord_unique_id);

说明：该方案仅需新增1个字段，索引占用空间比联合索引更小，可根据业务需要的坐标精度调整放大倍数和偏移量。

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