gorm soft delete 的唯一索引问题

发布时间: 2022-11-07 17:11:06 作者: 大象笔记

bug

今天测试我的添加、修改接口时,发现一个联合唯一索引不生效。

有问题的表结构:

CREATE TABLE `card` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ux_name_del` (`name`, `deleted_at`) /*!80000 INVISIBLE */,
  KEY `ix_del` (`deleted_at`)
) ENGINE=InnoDB CHARSET=utf8mb4

很显然,gorm 的软删除字段 deleted_at 默认为 NULL 造成了这个 bug。 因为 MySQL 的 unique 索引对 NULL 失效。

解决方法

使用 Unix 时间戳(unsign int, 秒为单位,例如:1667799587)替代 datetime 类型,默认值设置为 0.

`deleted_at` int unsigned DEFAULT '0',

同时使用 gorm 的 soft_delete 替换原来的 DeletedAt 类型。

import (
	"time"
	"gorm.io/plugin/soft_delete"
)

type Card struct {
	ID        uint
	CreatedAt time.Time
	UpdatedAt time.Time
	DeletedAt soft_delete.DeletedAt
	Name      string
}

安装 soft_delete

> go get gorm.io/plugin/soft_delete
go: downloading gorm.io/plugin/soft_delete v1.2.0
go: downloading gorm.io/gorm v1.23.0
go: upgraded github.com/jinzhu/now v1.1.2 => v1.1.4
go: upgraded gorm.io/gorm v1.21.16 => v1.23.0
go: added gorm.io/plugin/soft_delete v1.2.0

> go build
# gorm.io/driver/mysql
../../golang/pkg/mod/gorm.io/driver/mysql@v1.1.2/migrator.go:224:38: cannot use column (variable of type Column) as type gorm.ColumnType in argument to append:
        Column does not implement gorm.ColumnType (missing AutoIncrement method)

> go get gorm.io/driver/mysql
go: downloading gorm.io/driver/mysql v1.4.3
go: downloading gorm.io/gorm v1.23.8
go: downloading github.com/jinzhu/now v1.1.5
go: upgraded github.com/jinzhu/now v1.1.4 => v1.1.5
go: upgraded gorm.io/driver/mysql v1.1.2 => v1.4.3
go: upgraded gorm.io/gorm v1.23.0 => v1.23.8

> go build

参考

我是一名山东烟台的开发者,联系作者