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 聊聊， 查看更多联系方式