gorm soft delete 的唯一索引问题

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

参考

• https://gorm.io/docs/delete.html#Delete-Flag
  
• https://github.com/go-gorm/soft_delete

关于作者 🌱

我是来自山东烟台的一名开发者，有感兴趣的话题，或者软件开发需求，欢迎加微信 zhongwei 聊聊，或者关注我的个人公众号“大象工具”， 查看更多联系方式