mysql - 由多个列和 NULL 值唯一

Question

我正在使用 symfony 1.4(和 Doctrine)进行开发，并且有一个 MySQL 数据库表，在多个列上有一个唯一索引。首先，到目前为止表的 YAML 定义:

Campaign:
  actAs:
    Sluggable:
      fields: [name]
      canUpdate: true
      uniqueBy: [merchant_id, deleted_at]
    Timestampable: ~
    SoftDelete: ~
  columns:
    merchant_id:      { type: integer, notnull: true }
    name:             { type: string(255), notnull: true, notblank: true }
    start_date:       { type: date, notnull: true, notblank: true }
    end_date:         { type: date, notnull: true, notblank: true }
  indexes:
    unique_name:  { fields: [name, merchant_id, deleted_at], type: unique }
  relations:
    Merchant: { local: merchant_id, foreign: id }

如您所见，我必须处理属于商家的事件。事件知道其商家并有名称(以及开始日期和结束日期)。事件的名称应该是唯一的——不是全局唯一的，而是针对特定商家的。到这里为止，我需要一个关于事件名称和相应商家的唯一索引。但是，由于表“充当 SoftDelete”并且用户应该能够使用“软删除”事件的名称创建新事件，因此 deleted_at 列也必须是唯一索引的一部分。您会看到，事件名称的唯一性仅涉及相应商家的未删除事件。

现在来到实际问题:由于列 deleted_at 对于所有未删除的营销事件都是 NULL，并且唯一索引中的 NULL 值始终被视为唯一的，因此所有营销事件允许有非唯一的名字——在真正意义上。我知道，这适用于 MyISAM 和 InnoDB 表，但不适用于 BDB 表。但是，如果您明白我的意思，那么切换到 BDB 并不是我最喜欢的选择。

现在进入实际问题:除了将 MySQL 引擎更改为 BDB 之外，还有哪些其他可能的选择？解决方法是重命名软删除的事件，例如name = 'DELETED AT ' + deleted_at + ': ' + name。一方面，这样做的好处是，所有软删除的广告事件即使在恢复(将 deleted_at 重置为 NULL)的情况下也应该具有唯一的名称。 deleted_at 列将不再必须是唯一索引的一部分，因此，所有事件(未删除、软删除以及恢复一次)都将有一个唯一的名称——关于相应商户。但是，另一方面，我认为这不是最优雅的解决方案。您对此有何看法和专业知识？

非常感谢你，我很高兴你的贡献。
弗林施。

Answer 1

我认为你可以保留你的基本结构，你只需要一种方法让 deleted_at NOT NULL。这意味着你需要给它一个默认值。一个好的默认值是 0，或 0000-00-00 00:00:00。

我的建议是添加一个新列来标记行是否被逻辑删除。您可以将其称为“IS_DELETED”。然后为 deleted_at 添加默认值并使其非空，并将 is_deleted 包含在您的唯一索引中。

下面是这种方法的一个非常简单的例子:

mysql> create table merchant(
    -> id int unsigned not null auto_increment,
    -> name varchar(50) not null, 
    -> is_deleted tinyint not null default 0, 
    -> deleted_at datetime not null default 0,
    -> primary key (id),
    -> unique key name_and_deleted_at (name,is_deleted,deleted_at)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> -- successful inserts
mysql> insert into merchant (name,is_deleted) values ('foo',0);
Query OK, 1 row affected (0.00 sec)

mysql> insert into merchant (name,is_deleted) values ('bar',0);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> -- insert failure due to duplicate name
mysql> insert into merchant (name,is_deleted) values ('foo',0);
ERROR 1062 (23000): Duplicate entry 'foo-0-0000-00-00 00:00:00' for key 'name_and_deleted_at'
mysql> -- logical delete
mysql> update merchant set is_deleted = true, deleted_at = now() where name = 'foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> -- now the insert succeeds
mysql> insert into merchant (name,is_deleted) values ('foo',0);
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> -- show data
mysql> select id,name,is_deleted,deleted_at 
    -> from merchant
    -> order by id;
+----+------+------------+---------------------+
| id | name | is_deleted | deleted_at          |
+----+------+------------+---------------------+
|  1 | foo  |          1 | 2010-11-05 13:54:17 |
|  2 | bar  |          0 | 0000-00-00 00:00:00 |
|  4 | foo  |          0 | 0000-00-00 00:00:00 |
+----+------+------------+---------------------+
3 rows in set (0.00 sec)