MySQL多表多列前缀验证：如何校验ID是否以'1a'开头

问题背景 #

现有两张表：

• Student表：包含ID、Clg_ID字段
• College表：包含ID、Un_ID、Teacher_ID字段

需要验证所有ID类字段是否均以'1a'开头，满足以下要求：

• 若存在任意字段前缀不符合'1a'，返回错误信息
• 若并非所有字段前缀都符合'1a'（即存在不符合项），返回错误信息

你之前尝试的SQL仅处理了Student表的ID字段，未覆盖所有需要验证的字段，因此无法得到正确结果：

SELECT 
  CASE 
    WHEN COUNT(*) = COUNT(CASE WHEN SUBSTRING(LTRIM(ID), 1, LENGTH(@prefix)) = @prefix THEN 1 END)
    THEN 'No Error'
    ELSE 'Error while validating prefixes'
  END AS StartsWithPrefix
FROM Student;

原SQL的问题 #

1. 仅检查了Student表的ID字段，遗漏了Student.Clg_ID以及College表的ID、Un_ID、Teacher_ID字段
2. 未定义@prefix变量，执行时会因变量不存在报错
3. 逻辑仅针对单表单字段，无法覆盖所有需要验证的ID类字段

正确解决方案 #

方案1：返回整体验证结果 #

该查询会统一检查所有ID类字段，直接返回是否存在前缀不符合的情况：

-- 定义前缀变量
SET @prefix = '1a';
SET @prefix_len = LENGTH(@prefix);

SELECT 
    CASE 
        WHEN COUNT(*) = 0 THEN 'No Error'
        ELSE 'Error while validating prefixes'
    END AS StartsWithPrefix
FROM (
    -- 收集所有需要验证的ID类字段
    SELECT ID AS check_field FROM Student
    UNION ALL
    SELECT Clg_ID AS check_field FROM Student
    UNION ALL
    SELECT ID AS check_field FROM College
    UNION ALL
    SELECT Un_ID AS check_field FROM College
    UNION ALL
    SELECT Teacher_ID AS check_field FROM College
) AS all_id_fields
WHERE 
    LTRIM(check_field) IS NOT NULL -- 排除空值（若业务允许空值可删除此条件）
    AND SUBSTRING(LTRIM(check_field), 1, @prefix_len) != @prefix;

逻辑说明：

• 子查询通过UNION ALL合并所有需要验证的字段到一个结果集
• 筛选出前缀不符合'1a'的记录
• 如果筛选结果行数为0，说明所有字段都符合要求，返回No Error；否则返回错误信息

方案2：返回详细错误记录 #

如果需要知道具体哪些字段不符合要求，可以使用以下查询返回详细信息：

SET @prefix = '1a';
SET @prefix_len = LENGTH(@prefix);

SELECT 
    source_table AS 来源表,
    field_name AS 字段名,
    field_value AS 字段值,
    '前缀不符合''1a''要求' AS 错误信息
FROM (
    SELECT 'Student' AS source_table, 'ID' AS field_name, ID AS field_value FROM Student
    UNION ALL
    SELECT 'Student' AS source_table, 'Clg_ID' AS field_name, Clg_ID AS field_value FROM Student
    UNION ALL
    SELECT 'College' AS source_table, 'ID' AS field_name, ID AS field_value FROM College
    UNION ALL
    SELECT 'College' AS source_table, 'Un_ID' AS field_name, Un_ID AS field_value FROM College
    UNION ALL
    SELECT 'College' AS source_table, 'Teacher_ID' AS field_name, Teacher_ID AS field_value FROM College
) AS all_id_fields
WHERE 
    LTRIM(field_value) IS NOT NULL
    AND SUBSTRING(LTRIM(field_value), 1, @prefix_len) != @prefix;

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