如何在SQL中校验CHAR类型坐标格式？CHECK约束使用求助

要让CHAR类型的坐标字段符合XX.XXXXX,YYY.YYYYY格式（X为-90_{90的纬度，Y为-180}180的经度），需要同时校验格式合法性和数值范围，以下是针对主流数据库的正确写法，以及你之前代码的问题分析：

你之前的代码问题 #

1. 正则匹配方式错误：直接用coordenates = '正则表达式'是把字段值和正则字符串直接比较，而非用数据库的正则匹配函数/操作符，导致格式校验完全失效。
2. 正则语法错误：正则里的+量词多余（比如[0-9]{2}+），且没有考虑纬度可以是1位整数（如5.12345）的情况。
3. CHAR长度不足：最长合法值如-90.00000,-180.00000共20个字符，CHAR(18)会截断数据，导致校验失败。
4. 缺少格式校验：第二个尝试只校验了数值范围，但没限制格式，非法格式的字符串会在CAST时报错，而非被CHECK拦截。

正确的CHECK约束写法 #

1. MySQL 8.0+（支持CHECK约束生效） #

CREATE TABLE your_table (
  coordenates CHAR(20) CHECK (
    -- 校验格式：正负可选，纬度1-2位整数+5位小数，经度1-3位整数+5位小数
    coordenates REGEXP '^[-]?[0-9]{1,2}(\\.[0-9]{5})?,[-]?[0-9]{1,3}(\\.[0-9]{5})$'
    -- 校验纬度范围：-90 到 90
    AND CAST(SUBSTRING_INDEX(coordenates, ',', 1) AS DECIMAL(7,5)) BETWEEN -90 AND 90
    -- 校验经度范围：-180 到 180
    AND CAST(SUBSTRING_INDEX(coordenates, ',', -1) AS DECIMAL(8,5)) BETWEEN -180 AND 180
  )
);

2. PostgreSQL #

CREATE TABLE your_table (
  coordenates CHAR(20) CHECK (
    -- 正则匹配用~操作符，无需双反斜杠转义
    coordenates ~ '^[-]?[0-9]{1,2}(\.[0-9]{5}),[-]?[0-9]{1,3}(\.[0-9]{5})$'
    -- 截取纬度部分并校验范围
    AND CAST(SUBSTRING(coordenates FROM 1 FOR POSITION(',' IN coordenates)-1) AS DECIMAL(7,5)) BETWEEN -90 AND 90
    -- 截取经度部分并校验范围
    AND CAST(SUBSTRING(coordenates FROM POSITION(',' IN coordenates)+1) AS DECIMAL(8,5)) BETWEEN -180 AND 180
  )
);

3. SQL Server #

CREATE TABLE your_table (
  coordenates CHAR(20) CHECK (
    -- 用PATINDEX匹配正则格式
    PATINDEX('^[-]?[0-9]{1,2}(\.[0-9]{5}),[-]?[0-9]{1,3}(\.[0-9]{5})$', coordenates) > 0
    -- 截取纬度并校验范围
    AND CAST(SUBSTRING(coordenates, 1, CHARINDEX(',', coordenates)-1) AS DECIMAL(7,5)) BETWEEN -90 AND 90
    -- 截取经度并校验范围
    AND CAST(SUBSTRING(coordenates, CHARINDEX(',', coordenates)+1, LEN(coordenates)) AS DECIMAL(8,5)) BETWEEN -180 AND 180
  )
);

关键说明 #

• 如果你允许坐标的小数部分可选（比如90,180这种整数格式），可以把正则里的(\.[0-9]{5})改成(\.[0-9]{5})?。
• CHAR类型的长度要根据最长合法值调整，避免数据被截断导致校验失败。
• 部分旧版本数据库（如MySQL 5.x）会忽略CHECK约束，建议升级到8.0+，或者用触发器替代。

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