代码之家 › 专栏 › 技术社区 › Flinsch

由多个列和空值唯一

symfony-1.4 mysql

1

Flinsch · 技术社区 · 14 年前

我正在使用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 }

deleted_at 列也必须是唯一索引的一部分。你看,一个活动名称的唯一性只关系到相应商家的活动没有被删除。

实际问题 :作为列对于所有未删除的活动为空,并且唯一索引中的空值始终被视为唯一的,所有活动都允许具有非唯一的名称——从真正的意义上讲。我知道,这适用于MyISAM和InnoDB表,但不适用于BDB表。不过,如果你知道我的意思,我不喜欢换成BDB。

实际问题 :除了将MySQL引擎更改为BDB之外,还有哪些其他可能的选项?解决方法可以是重命名软删除的活动,例如。 name = 'DELETED AT ' + deleted_at + ': ' + name 已删除 返回空值)。这个列将不再是唯一索引的一部分,因此,所有活动(不删除、软删除以及还原一次)都将有一个唯一的名称——关于相应的商家。但是,另一方面,我不认为这是最优雅的解决方案。你对此有什么看法和专长?

我非常感谢你,对你的贡献感到高兴。
弗林希。

1 回复 | 直到 14 年前

1

Ike Walker 14 年前

我认为你可以保持你的基本结构,你只需要一种方法,使删除的不为空。这意味着你需要给它一个默认值。使用的良好默认值是0或0000-00-00 00:00:00。

我的建议是添加一个新列来标记行是否在逻辑上被删除。你可以称之为“已删除”。然后添加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)