php - MySQL 唯一键场景

Question

我有一个由以下列组成的表格:

表名称: 用户

列: {id、fk1、fk2、用户名}

id:为主键

fk1 和 fk2 是来自其他表的外键

有2种情况:

1) fk1 + 用户名 = 唯一值

2) fk2 + 用户名 = 唯一值

例如:

fk1 = 国家/地区表中国家/地区的id

fk2 = 状态表中状态的id

在有州的国家(例如美国)

我想要的是用户的用户名仅在一个州内是唯一的。来自不同州的两个用户可以具有相同的用户名，但不能处于同一州。

在没有州的国家(例如西类牙)

来自同一国家/地区的两个用户不能拥有相同的用户名。来自不同国家/地区的两个用户可以拥有相同的用户名。

因此，如果注册的用户来自有州的国家/地区，我需要为列 fk2 和 用户名 创建唯一索引。 [确保每个州的唯一性]

如果注册的用户来自没有州的国家/地区，我需要为 fk1 和 用户名 列创建唯一索引。 [确保每个国家/地区的唯一性]

到目前为止，我所做的是为来自州的用户创建一个表，为来自没有州的国家的用户创建一个表。如下所示:

--表名称:User_States

列:{id、fk2、用户名}

fk2 + 用户名的唯一索引

--表名称:用户

列:{id、fk1、用户名}

fk1 + 用户名的唯一索引

此解决方案可以通过在将数据插入每个表之前过滤用户(来自州或非州的用户)来工作。但我只想要一张包含所有用户的表，而不是两个，因为当我想检查电子邮件是否已由用户注册(无论是否在州)时，我必须执行 2 个查询(每个表一个)。或者，如果我想提取所有用户名(如“darklord”)的列表，无论其位置如何，我必须再次执行 2 个查询。

有办法实现吗？如果是， table 会是什么样子？当用户来自没有州的国家/地区时，我的问题就开始了，因为 fk2 列将为空。我知道唯一索引允许 Null 值，但只能有一个 Null 值。

还有一个一般性问题:如何检查大表上的单个查询是否比 2 个较小表上的 2 个查询更快/更慢？

PS:我使用的存储引擎是InnoDB

提前致谢。

Answer 1

有类似这样的作品吗？

create table #Something
(
    SomeDataField varchar(10) not null,
    CountryCode char(3) not null,
    StateCode char(2) null,
    CONSTRAINT UniqueCountryState UNIQUE NONCLUSTERED
    (
        SomeDataField, CountryCode, StateCode
    )
)

insert #Something
select 'Value1', 'USA', 'NY' union all
select 'Value1', 'CAN', null union all
select 'Value1', 'MEX', null union all
select 'Value1', 'USA', 'AK'

现在尝试再次插入这些值中的任何一个，但它将失败，因为该组合已存在。

insert #Something
select 'Value1', 'USA', 'NY'