MariaDB查询：用多值逗号分隔钥匙匹配可解锁挂锁

一、基于现有非规范化表的实现 #

你的场景中，Locks列用逗号分隔存储锁型，要实现按锁型数量匹配的逻辑，可借助MariaDB 10.3支持的递归CTE拆分字符串，统计各锁型的出现次数后对比：

完整查询语句 #

WITH RECURSIVE key_locks AS (
    -- 拆分输入的钥匙锁型字符串
    SELECT 
        SUBSTRING_INDEX('KL, OK, OK, CZ, CZ', ',', 1) AS lock_type,
        TRIM(SUBSTRING_INDEX('KL, OK, OK, CZ, CZ', ',', 1)) AS trimmed_lock,
        SUBSTRING('KL, OK, OK, CZ, CZ', LENGTH(SUBSTRING_INDEX('KL, OK, OK, CZ, CZ', ',', 1)) + 2) AS remaining
    UNION ALL
    SELECT 
        SUBSTRING_INDEX(remaining, ',', 1),
        TRIM(SUBSTRING_INDEX(remaining, ',', 1)),
        SUBSTRING(remaining, LENGTH(SUBSTRING_INDEX(remaining, ',', 1)) + 2)
    FROM key_locks
    WHERE remaining != ''
),
key_lock_counts AS (
    -- 统计钥匙中每个锁型的数量
    SELECT trimmed_lock, COUNT(*) AS count
    FROM key_locks
    GROUP BY trimmed_lock
),
padlock_locks AS (
    -- 拆分挂锁表中符合尺寸条件的Locks列
    SELECT 
        p.Name,
        p.Size,
        TRIM(SUBSTRING_INDEX(p.Locks, ',', 1)) AS trimmed_lock,
        SUBSTRING(p.Locks, LENGTH(SUBSTRING_INDEX(p.Locks, ',', 1)) + 2) AS remaining
    FROM Padlocks p
    WHERE p.Size = 'Small'
    UNION ALL
    SELECT 
        pl.Name,
        pl.Size,
        TRIM(SUBSTRING_INDEX(pl.remaining, ',', 1)),
        SUBSTRING(pl.remaining, LENGTH(SUBSTRING_INDEX(pl.remaining, ',', 1)) + 2)
    FROM padlock_locks pl
    WHERE pl.remaining != ''
),
padlock_lock_counts AS (
    -- 统计每个挂锁的各锁型数量
    SELECT 
        Name,
        trimmed_lock,
        COUNT(*) AS count
    FROM padlock_locks
    GROUP BY Name, trimmed_lock
)
-- 筛选符合条件的挂锁
SELECT DISTINCT plc.Name
FROM padlock_lock_counts plc
JOIN key_lock_counts klc ON plc.trimmed_lock = klc.trimmed_lock
WHERE plc.count <= klc.count
-- 确保挂锁没有钥匙不包含的锁型
AND NOT EXISTS (
    SELECT 1
    FROM padlock_lock_counts plc2
    WHERE plc2.Name = plc.Name
    AND NOT EXISTS (
        SELECT 1
        FROM key_lock_counts klc2
        WHERE klc2.trimmed_lock = plc2.trimmed_lock
    )
)
ORDER BY plc.Name;

这个查询会返回A、B、C，完全符合需求：

• 自动过滤尺寸不匹配的挂锁F
• 排除包含钥匙没有的锁型（如RO）的挂锁D
• 拒绝钥匙锁型数量不足的挂锁E（钥匙只有1个KL，挂锁E需要2个）

二、数据库规范化方案（推荐） #

逗号分隔的列会导致查询复杂、性能低下，还容易出现数据格式错误，更合理的做法是规范化数据库结构：

1. 创建规范化表结构 #

-- 挂锁主表：存储基本信息
CREATE TABLE Padlocks (
    PadlockID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Size ENUM('Small', 'Big') NOT NULL
);

-- 锁型字典表：统一管理锁型（可选，但能避免字符串不一致问题）
CREATE TABLE LockTypes (
    LockTypeID INT AUTO_INCREMENT PRIMARY KEY,
    TypeCode VARCHAR(10) NOT NULL UNIQUE
);

-- 关联表：记录挂锁的每个锁型（重复锁型对应多条记录）
CREATE TABLE Padlock_LockTypes (
    Padlock_LockID INT AUTO_INCREMENT PRIMARY KEY,
    PadlockID INT NOT NULL,
    LockTypeID INT NOT NULL,
    FOREIGN KEY (PadlockID) REFERENCES Padlocks(PadlockID),
    FOREIGN KEY (LockTypeID) REFERENCES LockTypes(LockTypeID)
);

2. 插入示例数据 #

比如挂锁A的锁型是KL, OK, CZ, CZ，就向Padlock_LockTypes插入4条记录，分别对应KL、OK、CZ、CZ的LockTypeID。

3. 简化的查询逻辑 #

对于输入的钥匙锁型，先统计各锁型数量，再查询符合条件的挂锁：

-- 创建临时表存储钥匙的锁型统计
CREATE TEMPORARY TABLE KeyLockCounts (
    LockTypeID INT,
    Count INT
);

-- 插入钥匙的锁型数据（示例：KL×1、OK×2、CZ×2）
INSERT INTO KeyLockCounts (LockTypeID, Count)
SELECT lt.LockTypeID, COUNT(*)
FROM (
    SELECT 'KL' AS TypeCode UNION ALL
    SELECT 'OK' UNION ALL
    SELECT 'OK' UNION ALL
    SELECT 'CZ' UNION ALL
    SELECT 'CZ'
) key_types
JOIN LockTypes lt ON key_types.TypeCode = lt.TypeCode
GROUP BY lt.LockTypeID;

-- 查询可解锁的挂锁
SELECT p.Name
FROM Padlocks p
JOIN Padlock_LockTypes plt ON p.PadlockID = plt.PadlockID
JOIN LockTypes lt ON plt.LockTypeID = lt.LockTypeID
WHERE p.Size = 'Small'
GROUP BY p.PadlockID, p.Name
HAVING 
    -- 挂锁所有锁型都存在于钥匙中
    SUM(CASE WHEN EXISTS (SELECT 1 FROM KeyLockCounts klc WHERE klc.LockTypeID = lt.LockTypeID) THEN 0 ELSE 1 END) = 0
    -- 挂锁每个锁型的数量不超过钥匙的对应数量
    AND MAX(CASE WHEN (SELECT klc.Count FROM KeyLockCounts klc WHERE klc.LockTypeID = lt.LockTypeID) >= COUNT(lt.LockTypeID) THEN 1 ELSE 0 END) = 1;

规范化后的结构不仅让查询更简洁，还能提升性能、降低数据出错概率，是长期维护的最优方案。

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