请教一个数据库或代码的唯一性设计问题

[jov1]

请教一个设计思路问题，要实现一个层级配置功能，

前置需求

有 1 个基础表简称 b (a, b, c, d, e)有很多列数据。现在需要依据这个配置做一个层级配置。

规则如下，使用 a, b, c 3 个列 做一个规则配置，

• 必须依次往下配置，a 必填，b 和 c 非必填。不会有(null, null, "x" )或 （"1", null ,"3"）这样的数据,可以出现('a', null, null) 、(a, b, null) {a, b, null}这样数据

• 配置的数据项不能存在重复的级别配置，举例

  • 如果存在("1", "2", "3")，那么任何("1" ,? , ?) 类型的都不可以（？包含 null ）,只能是不同与现有数据级别的值，比如可以是("1","2","4")、("1","2","5")、("1","3","3")这样的数据

  • 如果存在("1", "2", null)，那么任何("1" , "2" , ?) 类型的都不可以（？包含 null ）,只能是不同与现有数据级别的值，比如可以是("1","3",null)、("1","1",null)、("2","1", null)这样的数据

  • 如果存在("1", null, null)，那么任何("1" , ? , ?) 类型的都不可以（？包含 null ）,只能是不同与现有数据级别的值，比如可以是("2",null, null)、("3", null, null) 、("4", "2", null 这样的数据

  • 如果配置了 a=1 ，bc 为 null ，后面所有 a=1 后续无法再配置，只能再配置 a!=1 的。

  • 如果配置了 a=2,b=3 ，后面所有 a=2 ，b=3 的后续无法再配置，只能再配置符合 非(a=2 且 b=3)的。

总和来说，就是 只能存在同级别不重复的数据，取决于谁先谁后，先配置了层级大小决定了后续能添加的维度大小。不存在交叉数据的维度。

目前想法

目前的想法，是数据库设计一个 b 如 t （ a,b,c ） 3 个字段

 select CONCAT_WS('_',ifnull(a,''), ifnull(b,''), ifnull(c,'')) as uniqueKey
 from t
 where LOCATE(#{uniqueKey,jdbcType=VARCHAR}, t.uniqueKey) > 0 or LOCATE(t.uniqueKey, #{detail.uniqueKey,jdbcType=VARCHAR}) > 0

• 然后每次新增配置使用类似这样的判断，将几个配置拼接，然后传入要新增配置的 uniqueKey （也按照 sql 逻辑拼接传入)
• 然后判断是否相互包含，如果包含证明存在通用级别配置，
• 但是这样有个问题就是，就是如果存在数据(1,null, null)对应 1__，传入(1,2,null)对应 1_2_，这样查询就不会包含。
• 尝试将拼接符号换位空白，CONCAT_WS('_' 这里替换成 CONCAT_WS('') ,这样使用包含可以临时解决这个问题，但是如果存在数据(1,2,2),传入(1,2,23)也会被认为是存在相同的数据，目前卡在这里了，
• 目前是 mysql 和 springboot ，大家有没有什么好的设计或方案呢，表的设计或其他判断逻辑方面的。

[Rickkkkkkk]

如此复杂的规则为啥会让数据库的唯一键来做...

写入收口然后写入之前校验呗

你用数据做会有个大麻烦, 后续规则改了你咋办?

[xhawk]

先大致理下你的想法:
数据表有很多列，其实就是很多的参数(列就是参数)，然后每次可以选择某几个参数做配置，但是配置的参数不能出现重复配置。

我的建议是 列只存储是否选择了这个参数，比如 0 和 1 ，单独加一列 配置，配置列可以用 json 存储。

程序判断逻辑:是否必填逻辑判断 0 ，1 是否重复需要把设计同样参数的相加判断重复。

最后，可以再抽象下，是否可能用树形来处理。

[fuyufjh]

忍不住直接丢给 GPT-4 了，以下是它的回答：

你的需求是实现一个层级配置功能，其中层级关系由三个字段（ a, b, c ）定义，并且需要确保配置的数据项在同一层级上不重复。根据你的描述，这里是一个典型的树形结构，其中 a 是第一层，b 是第二层，c 是第三层。

为了解决你目前面临的问题，可以考虑以下几个设计思路：

### 1. 使用路径枚举模型（ Materialized Path ）

在这种模型中，你可以为每个节点存储一个路径字符串，该字符串反映了从根到该节点的完整路径。例如：

- "1" -> "1/"
- "1,2" -> "1/2/"
- "1,2,3" -> "1/2/3/"

在查询时，你可以使用路径来检查是否存在重复的层级配置。例如，要检查是否存在与"1/2/3/"相同或更高层级的配置，只需要查询路径以"1/2/3/"开头的记录。

### 2. 使用左右值模型（ Nested Set ）

这种模型适用于存储层级数据，每个节点都有一个左值和一个右值，通过这两个值可以快速查询节点的子节点和父节点。但是，这种模型在插入和删除操作时需要更新大量记录，可能不适合频繁变动的配置。

### 3. 使用 Adjacency List 模型并增加唯一性约束

这是最简单的层级数据模型，每个节点都有一个指向父节点的外键。在你的情况下，可以在表中添加唯一性约束来确保同一层级的配置不会重复。

例如，你可以在数据库表中创建一个唯一索引：

```sql
CREATE UNIQUE INDEX idx_unique_config ON t(a, b, c);
```

这样，数据库将自动保证不会插入重复的层级配置。

### 4. 使用闭包表模型（ Closure Table ）

闭包表存储每个节点与其每个祖先之间的关系。这种模型查询快速，但是需要维护一个额外的表来存储关系。

### 5. 使用触发器或应用层逻辑

你可以在数据库层使用触发器来阻止违反层级规则的数据插入，或者在应用层实现逻辑来确保数据的一致性。

### 结合你的需求

你的需求似乎最适合使用路径枚举模型，因为它可以很容易地通过字符串比较来检查层级关系。你可以这样设计：

1. 在表中添加一个额外的字段，比如 `path`。
2. 当插入新记录时，根据 a, b, c 的值生成路径字符串。
3. 使用这个路径字符串来检查是否有重复的层级配置。

例如：

```sql
-- 假设你要插入新记录
SET @new_a = '1';
SET @new_b = '2';
SET @new_c = NULL;

-- 生成路径字符串
SET @new_path = CONCAT_WS('/', @new_a, IFNULL(@new_b, ''), IFNULL(@new_c, ''));

-- 检查是否存在重复的层级配置
SELECT COUNT(*)
FROM t
WHERE path = @new_path OR path LIKE CONCAT(@new_path, '/%');

-- 如果计数为 0 ，则可以插入新记录
```

这种方法可以避免你当前面临的问题，并且可以很容易地扩展到更多层级。

[jov1]

@Rickkkkkkk
@xhawk
@fuyufjh 嗯，感觉大家提供意见和参考，之前也问了 gpt ，给出的方案是类似 path 级别这样的，但是测试几个场景后还是会存在我说的那种错误判断包含的情况，试着在这基础上不断调整，感觉可以满足，这样写可以避免这种情况(1,2,2),传入(1,2,23)
select CONCAT_WS(',',ifnull(a,''), ifnull(b,''), ifnull(c,'')) as uniqueKey
from t
where t.uniqueKey = #{uniqueKey,jdbcType=VARCHAR}
or t.uniqueKey like CONCAT(#{uniqueKey,jdbcType=VARCHAR}, ',%')
or #{uniqueKey,jdbcType=VARCHAR} like CONCAT(t.uniqueKey, ',%')