MariaDB中项目/机器二维部分覆盖配置的读取问题解决

背景说明 #

• 使用HeidiSQL操作MariaDB读取配置条目
• ID为0的project和machine是基础配置，可被特定项目/机器的配置覆盖
• 仅定义需要覆盖的特定值，未指定的组合默认使用基础配置
• 项目优先级高于机器

配置表数据 #

示例表结构与数据 #

DROP TABLE IF EXISTS `overwrite`;
CREATE TABLE IF NOT EXISTS `overwrite` (
  `id` int(11) NOT NULL,
  `project_id` int(11) NOT NULL,
  `machine_id` int(11) NOT NULL,
  `key` char(50) NOT NULL DEFAULT '',
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELETE FROM `overwrite`;
INSERT INTO `overwrite` (`id`, `project_id`, `machine_id`, `key`, `value`) VALUES
    (0, 0, 0, 'Uc_max', 5000),
    (1, 1, 0, 'Uc_max', 4000),
    (2, 0, 1, 'Uc_max', 10000),
    (3, 1, 1, 'Uc_max', 15000),
    (4, 0, 2, 'Uc_max', 20000);

现有查询问题 #

当前查询能正确读取前4条数据，但查询project_id=1、machine_id=2时，无法返回应匹配的ID=4条目（因无project1+machine2的覆盖项，应默认使用project0+machine2的配置）。查询project_id=7、machine_id=1时可正确返回ID=2条目。

现有查询语句：

SELECT o.`id`, o.`project_id`, o.`machine_id`, o.`key`, o.`value`
FROM overwrite o
LEFT JOIN (
     -- 获取指定project_id的所有键
    SELECT DISTINCT o1.`key`
    FROM overwrite o1
     WHERE o1.`project_id` = 1
) subquery_project ON o.`key` = subquery_project.`key`
LEFT JOIN (
    -- 获取指定machine_id的所有键
    SELECT DISTINCT o1.`key`
    FROM overwrite o1
    WHERE o1.`machine_id` = 2
) subquery_machine ON o.`key` = subquery_machine.`key`
WHERE o.`project_id` IN (0, 1)
AND o.`machine_id` IN (0, 2)
AND (
    -- 如果是基础项目，仅当没有更特定的项目条目时才包含
    (o.`project_id` = 0 AND subquery_project.`key` IS NULL)
    -- 始终包含特定项目条目
    OR o.`project_id` = 1
)
AND (
    -- 如果是基础机器，仅当没有更特定的机器条目时才包含
    (o.`machine_id` = 0 AND subquery_machine.`key` IS NULL)
    -- 始终包含特定机器条目
    OR o.`machine_id` = 2
)

问题根源：subquery_project.key不为空时，会直接过滤所有基础项目（project_id=0）的条目，但实际需求是针对当前project+machine组合，判断是否存在更高优先级的覆盖项，而非只要该key有项目覆盖就过滤基础配置。

解决方案 #

核心思路：针对目标project_id和machine_id，生成所有可能的配置优先级组合，按优先级从高到低排序后取第一条。优先级规则为：

1. 特定项目+特定机器（project_id=目标值，machine_id=目标值）
2. 特定项目+基础机器（project_id=目标值，machine_id=0）
3. 基础项目+特定机器（project_id=0，machine_id=目标值）
4. 基础项目+基础机器（project_id=0，machine_id=0）

单键查询（以Uc_max为例） #

SELECT o.*
FROM overwrite o
WHERE (o.project_id, o.machine_id) IN (
    (1, 2), (1, 0), (0, 2), (0, 0)
)
AND o.`key` = 'Uc_max'
ORDER BY 
    CASE 
        WHEN o.project_id = 1 AND o.machine_id = 2 THEN 1
        WHEN o.project_id = 1 AND o.machine_id = 0 THEN 2
        WHEN o.project_id = 0 AND o.machine_id = 2 THEN 3
        WHEN o.project_id = 0 AND o.machine_id = 0 THEN 4
    END
LIMIT 1;

所有键批量查询 #

SELECT o.`key`, o.`value`, o.id, o.project_id, o.machine_id
FROM (
    SELECT *,
        CASE 
            WHEN project_id = 1 AND machine_id = 2 THEN 1
            WHEN project_id = 1 AND machine_id = 0 THEN 2
            WHEN project_id = 0 AND machine_id = 2 THEN 3
            WHEN project_id = 0 AND machine_id = 0 THEN 4
        END AS priority
    FROM overwrite
    WHERE (project_id, machine_id) IN ((1,2), (1,0), (0,2), (0,0))
) o
GROUP BY o.`key`
HAVING o.priority = MIN(o.priority);

解释：通过CASE语句定义每个配置组合的优先级，排序后自动选取最高优先级的条目，确保当特定组合不存在时，自动降级到次优先级的配置，完全符合项目优先级高于机器的规则。

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