在 MySQL 中绑定 2 个不同情况下的 3 个表

Question

我没有找到解决方案，所以想请教您。

整个事情是我有 3 张表：城市、地区、机构。

• 区和城市有关系

现在当我想注册一个机构时，我必须选择该机构所属的区，但如果该城市没有任何区，我必须选择城市本身。那么，问题是，如何针对给定情况绑定这些表？

PS 地区和城市必须保持约束力

Answer 1

有2个表，“机构”和“区域”

允许“区域”链接到自身，即area_id，parent_area_id

这样您总是将机构链接到area_id，然后内部逻辑可以确定该区域是否被视为地区或一座城市。

所以你现在有了

institutions (
    id UNSIGNED INT NOT NULL PK AI,
    area_id UNSIGNED INT NOT NULL,
    name VARCHAR NOT NULL
)

，并且

areas (
    id UNSIGNED INT NOT NULL PK AI,
    parent_area_id UNSIGNED INT NOT NULL DEFAULT 0,
    name VARCHAR NOT NULL,
    type ENUM('city','district') NOT NULL DEFAULT 'city' 
)

Areas.type 字段是可选的，但如果你想这样定义它们，那么这可能是在数据库中执行此操作的一种方法（否则只需假设如果parent_area_id = 0那么它是一个城市，否则它是一个 这样，

当选择字段时，您所做的就是

SELECT *
FROM institutions
INNER JOIN areas
ON areas.id = institutions.area_id

您可以 100% 确定机构 area_id 链接到的位置，对于是否转到“地区”或“城市”表没有问号，它肯定会区域表反过来以相同的方式处理地区和城市，并以前端可能将其解释为城市或地区的格式呈现信息。或者，如果您确实想要的话，您可以更进一步

SELECT
    i.*,
    COALESCE(a_parent.id,a_child.id) AS city_id,
    COALESCE(a_parent.name,a_child.name) AS city_name
FROM institutions AS i
INNER JOIN areas AS a_child
ON a_child.id = i.area_id
LEFT JOIN areas AS a_parent
ON a_parent.id = a_child.parent_area_id 

例如，即使该机构与城市中的特定区域相关联，也始终会返回城市名称

Answer 2

有一种肮脏、重复肮脏的方式来做到这一点：让机构有一个SIGNED INT类型的ID字段，负数指向城市的 ID，以及指向该地区的正数。这有助于轻松连接查询。

再次强调：这是肮脏的，我建议您完全避免这种情况（例如，通过创建具有父字段的城市和地区的树表），但如果您无法更改模型，那么这是可行的。

Answer 3

这种模式称为多态关联，用 SQL 很好地解决它并不容易。

uid // common id table with globally unique ids
  id int AUTO_INCREMENT

city
  id UNIQUE FK:uid.id

district
  id UNIQUE FK:uid.id
  city_id FK:city.id

institution
  location_id FK:uid.id 

fruits
  id UNIQUE FK:uid.id

FK 表示外键。您必须手动检查 institution.location_id 是否指向一个地区或城市，而不是水果或其他任何东西。