如何在SQL中实现基于指定值列表的分号分隔值验证？

需求说明：验证字段值仅包含'Apples'、'Oranges'、'Bananas'中的单个值，或是这些值通过分号分隔的合法组合；包含其他值（如'Eggs'）的记录验证失败。

测试表与数据：

CREATE TABLE Test1(A varchar (50));

INSERT INTO Test1 VALUES ('Apples;Oranges;Bananas');
INSERT INTO Test1 VALUES ('Apples');
INSERT INTO Test1 VALUES ('Apples;Eggs');

以下是不同数据库环境下的实现方案：

1. MySQL（8.0.16+ 支持CHECK约束） #

验证查询 #

使用正则表达式匹配合法格式：

SELECT 
    A,
    CASE 
        WHEN A REGEXP '^(Apples|Oranges|Bananas)(;(Apples|Oranges|Bananas))*$' 
        THEN '验证通过' 
        ELSE '验证失败' 
    END AS 验证结果
FROM Test1;

正则规则说明：

• ^ 匹配字符串起始位置
• (Apples|Oranges|Bananas) 匹配第一个合法值
• (;(Apples|Oranges|Bananas))* 匹配零次或多次「分号+合法值」的组合
• $ 匹配字符串结束位置

插入时强制约束 #

添加CHECK约束确保插入数据合法：

ALTER TABLE Test1
ADD CONSTRAINT chk_valid_values 
CHECK (A REGEXP '^(Apples|Oranges|Bananas)(;(Apples|Oranges|Bananas))*$');

2. SQL Server（2016+ 支持STRING_SPLIT） #

验证查询 #

拆分字符串后检查所有元素是否在允许列表内：

SELECT 
    t.A,
    CASE 
        WHEN NOT EXISTS (
            SELECT 1 
            FROM STRING_SPLIT(t.A, ';') s
            WHERE s.value NOT IN ('Apples', 'Oranges', 'Bananas')
        ) THEN '验证通过'
        ELSE '验证失败'
    END AS 验证结果
FROM Test1 t;

逻辑说明：通过STRING_SPLIT将字段按分号拆分成行，若不存在不在允许列表中的值，则验证通过。

插入时强制约束 #

添加CHECK约束：

ALTER TABLE Test1
ADD CONSTRAINT chk_valid_values
CHECK (
    NOT EXISTS (
        SELECT 1 
        FROM STRING_SPLIT(A, ';') s
        WHERE s.value NOT IN ('Apples', 'Oranges', 'Bananas')
    )
);

3. PostgreSQL #

验证查询 #

将字符串转为数组后检查是否为允许值数组的子集：

SELECT 
    A,
    CASE 
        WHEN STRING_TO_ARRAY(A, ';') <@ ARRAY['Apples', 'Oranges', 'Bananas']
        THEN '验证通过'
        ELSE '验证失败'
    END AS 验证结果
FROM Test1;

操作符说明：<@ 表示左侧数组的所有元素都包含在右侧数组中（即左侧是右侧的子集）。

插入时强制约束 #

添加CHECK约束：

ALTER TABLE Test1
ADD CONSTRAINT chk_valid_values
CHECK (STRING_TO_ARRAY(A, ';') <@ ARRAY['Apples', 'Oranges', 'Bananas']);

以上方案执行后，前两条测试数据会返回「验证通过」，第三条包含'Eggs'的记录会返回「验证失败」。

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