英文:
Setting UUID field to NULL with gorm
问题
我正在使用的数据库是PostgreSQL，ORM是gorm。我有一个包含assigned_id字段的数据库表，该字段的类型是uuid。
我想知道如何将assigned_id设置为NULL。
我尝试在模型中将该字段设置为nil，还尝试使用gorm.Expr("NULL")、nil或sql.NullString，但这些方法都不起作用。
也许有人能够回答我的问题。
英文:
I'm having a database table which contains a assigned_id field which has the type uuid.
The database is postgres and my ORM is gorm. I was wondering how its possible to set that assigned_id to NULL?
I've tried setting the field to nil in the model, as well as using gorm.Expr("NULL"), nil or sql.NullString.
None of those methods work.
Maybe someone will be able to answer that question for me.
答案1
得分: 3
你可以声明一个自定义类型来处理uuid（或任何其他非默认类型）。我已经提供了实现，并且通过了我的测试示例。注意：我已经声明了一个自定义类型来处理空字符串，但为了简洁起见，我将其省略了。
此外，我没有对这段代码进行完整的测试覆盖，所以请将其作为一个起点。
type NullUUID struct {
UUID uuid.UUID
Valid bool
}
func (nd *NullUUID) Scan(value interface{}) (err error) {
var s uuid.UUID
if err := s.Scan(value); err != nil {
return err
}
// 如果为空，则将Valid设置为false
if reflect.TypeOf(value) == nil {
*nd = NullUUID{Valid: false}
} else {
if err != nil {
return err
}
*nd = NullUUID{s, true}
}
return nil
}
func (nd NullUUID) Value() (driver.Value, error) {
if !nd.Valid {
return nil, nil
}
return nd.Value, nil
}
func (nd NullUUID) MarshalJSON() ([]byte, error) {
if !nd.Valid {
return []byte("null"), nil
}
return json.Marshal(nd.UUID.String())
}
func (nd *NullUUID) UnmarshalJSON(b []byte) error {
var str string
err := json.Unmarshal(b, &str)
if err != nil {
nd.Valid = false
return err
}
id, err := uuid.Parse(str)
if err != nil {
nd.Valid = false
return err
}
nd.UUID = id
nd.Valid = true
return err
}
这段代码还处理了编组和解组，因此它可以打印值或"null"，但你可以根据需要进行编辑。
这是我测试过的示例：
id | text
----+------
| sda
// 行结果：
main.Row{Id:main.NullUUID{UUID:uuid.UUID{0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, Valid:false}, Text:main.NullString{String:"sda", Valid:true}}
// 行编组：
{"id":null,"text":"sda"}
// 行解组：
main.Row{Id:main.NullUUID{UUID:uuid.UUID{0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, Valid:false}, Text:main.NullString{String:"", Valid:false}} // 在我的空字符串解组中似乎存在一个错误，但我认为这与本问题无关
以防万一，我还测试了非空的uuid，以下是结果：
// 行编组
{"id":"889f2163-ee59-4632-b238-3ac9574f111e","text":null}
// 行解组
main.Row{Id:main.NullUUID{UUID:uuid.UUID{0x88, 0x9f, 0x21, 0x63, 0xee, 0x59, 0x46, 0x32, 0xb2, 0x38, 0x3a, 0xc9, 0x57, 0x4f, 0x11, 0x1e}, Valid:true}, Text:main.NullString{String:"", Valid:true}} // 再次，在我的空字符串解组中似乎存在一个错误，但我认为这与本问题无关
// 打印的UUID字符串
889f2163-ee59-4632-b238-3ac9574f111e
<details>
<summary>英文:</summary>
So you can declare a custom type to handle uuid (or any other non-default type). I've provided the implementation and it works for me with examples. NOTE: I've already declared a custom type to handle null string but I've left it out for brevity.
Also I did not do a complete test coverage of this code, so please take it as a starting point.
type NullUUID struct {
UUID uuid.UUID
Valid bool
}
func (nd *NullUUID) Scan(value interface{}) (err error) {
var s uuid.UUID
if err := s.Scan(value); err != nil {
return err
}
// if nil then make Valid false
if reflect.TypeOf(value) == nil {
*nd = NullUUID{Valid: false}
} else {
if err != nil {
return err
}
*nd = NullUUID{s, true}
}
return nil
}
func (nd NullUUID) Value() (driver.Value, error) {
if !nd.Valid {
return nil, nil
}
return nd.Value, nil
}
func (nd NullUUID) MarshalJSON() ([]byte, error) {
if !nd.Valid {
return []byte("null"), nil
}
return json.Marshal(nd.UUID.String())
}
func (nd *NullUUID) UnmarshalJSON(b []byte) error {
var str string
err := json.Unmarshal(b, &str)
if err != nil {
nd.Valid = false
return err
}
id, err := uuid.Parse(str)
if err != nil {
nd.Valid = false
return err
}
nd.UUID = id
nd.Valid = true
return err
}
This also handles marshalling and unmarshalling so it either prints the value or "null", but you can edit this is required.
Here is my tested example:
id | text
----+------
| sda
// Row result:
main.Row{Id:main.NullUUID{UUID:uuid.UUID{0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, Valid:false}, Text:main.NullString{String:"sda", Valid:true}}
// Row marshalled:
{"id":null,"text":"sda"}
// Row unmarshalled:
main.Row{Id:main.NullUUID{UUID:uuid.UUID{0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0}, Valid:false}, Text:main.NullString{String:"", Valid:false}} // seems to be a bug in my null string unmarshalling but I don't think it's relevant here
Just in case, I also tested non null uuid and here is the result:
// Row marshalled
{"id":"889f2163-ee59-4632-b238-3ac9574f111e","text":null}
// Row unmarshalled
main.Row{Id:main.NullUUID{UUID:uuid.UUID{0x88, 0x9f, 0x21, 0x63, 0xee, 0x59, 0x46, 0x32, 0xb2, 0x38, 0x3a, 0xc9, 0x57, 0x4f, 0x11, 0x1e}, Valid:true}, Text:main.NullString{String:"", Valid:true}} // again seems to be a bug in my null string unmarshalling but I don't think it's relevant here
// Printed UUID string
889f2163-ee59-4632-b238-3ac9574f111e
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论