Laravel5 软删除(SoftDeletes)的 deleted_at 改造

正常来说数据库最好不要做真正的硬删除操作,所以可以改用柔和的软删除。也就是在数据库增加一个字段,比如 is_deleted,如果是 0 的时候表示正常,1 表示已被删除。

使用软删除可能碰到的问题:如果表中有 UNIQUE 索引的字段,比如 User 表的 username,被软删除后,username 已经被占用了,新的数据不能使用该 username。解决方法是,定义联合索引唯一约束,即 UNIQUE KEY username_INDEX (username) 改成 UNIQUE KEY username_INDEX (username, is_deleted)。

看上去问题好像解决了,但是新的问题:如果 username 被第二次软删除,is_deleted 为 1 的也被占用了。进一步的解决方法是把 is_deleted 定义为,当 0 值时表示未删除,非 0 表示被删除并且是一个自增 ID 或者时间戳或者 UUID,那么就能保证唯一性。

Laravel 框架已经提供了很好的软删除功能:《Soft Deleting

To enable soft deletes for a model, use the Illuminate\Database\Eloquent\SoftDeletes trait on the model and add the deleted_at column to your $dates property:

使用步骤

  1. 在表中增加字段 deleted_at
  2. 在 Model 中使用 trait SoftDeletes
  3. 在 Model 中添加 deleted_at 到 $dates 成员变量
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php

namespace App;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;

class Flight extends Model
{
use SoftDeletes;

/**
* The attributes that should be mutated to dates.
*
* @var array
*/
protected $dates = ['deleted_at'];
}

Laravel 的软删除就是用 delete_at 字段作为删除的标记,类型是 Timestamp,默认是 NULL 的话表示未删除。

但是,使用 Laravel 自带的软删除是存在文章开头提到的问题的。因为虽然可以定义联合唯一索引 UNIQUE:(username, deleted_at),但是 deleted_at 默认值是 NULL,MySQL 下唯一值是不算 NULL 的,即唯一值列出现多个 NULL 值都不会报错。

我的改进方法是把 deleted_at 改成存储 BIGINT 类型的 Unix 时间戳,默认值 0。

SoftDeletes:app/Models/SoftDeletesEx.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
<?php
namespace App\Models;

use Illuminate\Database\Eloquent\SoftDeletes;

trait SoftDeletesEx {
use SoftDeletes;

/**
* Boot the soft deleting trait for a model.
*
* @return void
*/
public static function bootSoftDeletes() {
static::addGlobalScope(new SoftDeletingScopeEx());
}

/**
* Perform the actual delete query on this model instance.
*
* @return void
*/
protected function runSoftDelete() {
$query = $this->newQueryWithoutScopes()->where($this->getKeyName(), $this->getKey());

$this->{$this->getDeletedAtColumn()} = $time = \DB::Raw('UNIX_TIMESTAMP(NOW())');

$query->update([
$this->getDeletedAtColumn() => $time
]);
}

/**
* Restore a soft-deleted model instance.
*
* @return bool|null
*/
public function restore() {
// If the restoring event does not return false, we will proceed with this
// restore operation. Otherwise, we bail out so the developer will stop
// the restore totally. We will clear the deleted timestamp and save.
if ($this->fireModelEvent('restoring') === false) {
return false;
}

$this->{$this->getDeletedAtColumn()} = 0;

// Once we have saved the model, we will fire the "restored" event so this
// developer will do anything they need to after a restore operation is
// totally finished. Then we will return the result of the save call.
$this->exists = true;

$result = $this->save();

$this->fireModelEvent('restored', false);

return $result;
}

/**
* Determine if the model instance has been soft-deleted.
*
* @return bool
*/
public function trashed() {
return ! ($this->{$this->getDeletedAtColumn()} === 0);
}
}

app/Models/SoftDeletingScopeEx.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
<?php
namespace App\Models;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletingScope;

class SoftDeletingScopeEx extends SoftDeletingScope {

/**
* Apply the scope to a given Eloquent query builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model) {
$builder->where($model->getQualifiedDeletedAtColumn(), 0);
}

/**
* Extend the query builder with the needed functions.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @return void
*/
public function extend(Builder $builder) {
foreach ($this->extensions as $extension) {
$this->{"add{$extension}"}($builder);
}

$builder->onDelete(function (Builder $builder) {
$column = $this->getDeletedAtColumn($builder);

return $builder->update([
$column => \DB::Raw('UNIX_TIMESTAMP(NOW())')
]);
});
}

/**
* Add the restore extension to the builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @return void
*/
protected function addRestore(Builder $builder) {
$builder->macro('restore', function (Builder $builder) {
$builder->withTrashed();

return $builder->update([
$builder->getModel()
->getDeletedAtColumn() => 0
]);
});
}

/**
* Add the without-trashed extension to the builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @return void
*/
protected function addWithoutTrashed(Builder $builder) {
$builder->macro('withoutTrashed', function (Builder $builder) {
$model = $builder->getModel();

$builder->withoutGlobalScope($this)
->where($model->getQualifiedDeletedAtColumn(), 0);

return $builder;
});
}

/**
* Add the only-trashed extension to the builder.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @return void
*/
protected function addOnlyTrashed(Builder $builder) {
$builder->macro('onlyTrashed', function (Builder $builder) {
$model = $builder->getModel();

$builder->withoutGlobalScope($this)
->where($model->getQualifiedDeletedAtColumn(), '<>', 0);

return $builder;
});
}
}

我把代码放在 GitHub:https://github.com/broly8/SoftDeletesEx