MySQL中如何让每个network id对应唯一MAC-IP组合集并避免重复创建

数据库表结构 #

CREATE TABLE ip (
    id INT NOT NULL AUTO_INCREMENT,
    address VARBINARY(16) NOT NULL,  -- IPv4 or IPv6
    PRIMARY KEY (id),
    UNIQUE (address)
);

CREATE TABLE mac (
    id INT NOT NULL AUTO_INCREMENT,
    address VARBINARY(100) NOT NULL,  -- hardware address (usually MAC)
    PRIMARY KEY (id),
    UNIQUE (address)
);

CREATE TABLE network (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE network_configuration (
    network INT NOT NULL,
    mac INT NOT NULL,
    ip INT NOT NULL,
    PRIMARY KEY (network, mac, ip),
    INDEX (mac, ip, network),  -- For searching a network
    FOREIGN KEY (network) REFERENCES network(id),
    FOREIGN KEY (mac) REFERENCES mac(id),
    FOREIGN KEY (ip) REFERENCES ip(id)
);

需求说明 #

记录网络时，若新的MAC/IP组合集与已存在的某个network的组合集完全一致，则直接获取该network的id，不新增network。每个network对应的<mac,ip>组合集唯一。

示例数据 #

INSERT INTO ip (id, address) VALUES (1, X'7f000001');  -- 127.0.0.1
INSERT INTO ip (id, address) VALUES (2, X'c0a80101');  -- 192.168.1.1
INSERT INTO ip (id, address) VALUES (3, X'c0a80102');  -- 192.168.1.2

INSERT INTO mac (id, address) VALUES (1, X'3288a01e0fad');
INSERT INTO mac (id, address) VALUES (2, X'e2e1700bd5fb');
INSERT INTO mac (id, address) VALUES (3, X'8b1095dfe2eb');
INSERT INTO mac (id, address) VALUES (4, X'e4d9432afddb');

INSERT INTO network (id) VALUES (1);
INSERT INTO network (id) VALUES (2);
INSERT INTO network (id) VALUES (3);

INSERT INTO network_configuration (network, mac, ip) VALUES (1, 1, 1);
INSERT INTO network_configuration (network, mac, ip) VALUES (1, 2, 2);
INSERT INTO network_configuration (network, mac, ip) VALUES (2, 3, 1);
INSERT INTO network_configuration (network, mac, ip) VALUES (2, 4, 3);
INSERT INTO network_configuration (network, mac, ip) VALUES (3, 3, 1);

注：网络2和3共享一对MAC/IP，但网络2多一组额外组合，因此二者是不同的网络。

场景问题 #

现有一组MAC/IP组合：8b1095dfe2eb/127.0.0.1 和 e4d9432afddb/192.168.1.2，需要找到对应的network id=2，避免创建新的network。

解决方案 #

核心思路是找到完全匹配目标MAC/IP组合集的network：既要包含所有目标组合，又不能有额外组合。

步骤1：获取目标组合对应的ID #

先查询出目标MAC和IP在表中的主键ID：

-- 获取目标MAC的ID
SELECT id FROM mac WHERE address = X'8b1095dfe2eb'; -- 返回3
SELECT id FROM mac WHERE address = X'e4d9432afddb'; -- 返回4

-- 获取目标IP的ID
SELECT id FROM ip WHERE address = X'7f000001'; -- 返回1
SELECT id FROM ip WHERE address = X'c0a80102'; -- 返回3

步骤2：查询匹配的Network ID #

使用以下SQL直接筛选完全匹配的network：

WITH target_pairs AS (
    SELECT 3 AS mac_id, 1 AS ip_id
    UNION ALL
    SELECT 4 AS mac_id, 3 AS ip_id
)
SELECT nc.network
FROM network_configuration nc
JOIN target_pairs tp ON nc.mac = tp.mac_id AND nc.ip = tp.ip_id
GROUP BY nc.network
-- 确保该network的组合数量与目标组合数一致
HAVING COUNT(*) = (SELECT COUNT(*) FROM target_pairs)
-- 确保该network没有额外的MAC/IP组合
AND NOT EXISTS (
    SELECT 1
    FROM network_configuration nc2
    WHERE nc2.network = nc.network
    AND NOT EXISTS (
        SELECT 1
        FROM target_pairs tp2
        WHERE nc2.mac = tp2.mac_id AND nc2.ip = tp2.ip_id
    )
);

查询逻辑解释 #

1. 筛选包含目标组合的network：通过JOIN关联目标组合，找到所有包含至少一个目标组合的network。
2. 匹配组合数量：用COUNT(*) = 目标组合数排除组合数量不匹配的network（比如示例中的network 3只有1个目标组合）。
3. 排除额外组合：通过嵌套NOT EXISTS子查询，确保该network没有不在目标集合内的其他组合，保证组合集完全一致。

执行该查询会返回2，即目标network的ID。

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