如何阻止GORM在MySQL默认值为NULL时写入空字符串？

使用Go:Fiber构建RESTful API，通过GORM管理MySQL数据存储，表字段默认值设为NULL，但执行更新操作时，原NULL值会被替换为空字符串。已在结构体字段添加default:null标签，问题仍未解决。

相关代码与配置 #

数据结构 #

type Person struct {
    gorm.Model
    ID         uuid.UUID `gorm:"type:char(36);primary_key"`
    AccountId  uuid.UUID `gorm:"default:null"`
    NamePrefix string    `gorm:"default:null"`
    NameFirst  string    `gorm:"index;default:null"`
    NameMiddle string    `gorm:"default:null"`
    NameLast   string    `gorm:"index;default:null"`
    NameSuffix string    `gorm:"default:null"`
    Notes      string    `gorm:"default:null"`
}

更新记录代码 #

var person models.Person // account container
var pid uuid.UUID        // the person UUID

pid = uuid.MustParse(data["id"]) // Convert string to uuid

// Load existing person record based on the uuid/pid
if err := database.DB.Table("people").Select("*").Where("id = ?", pid).Scan(&person).Error; err != nil {
    if errors.Is(err, gorm.ErrRecordNotFound) {
        code := fiber.StatusNotFound
        return c.Status(code).JSON(fiber.Map{"Code": code, "Status": "Failed", "Message": "Person does not exist. Unable to update."})
    }
    code := fiber.StatusInternalServerError
    return c.Status(code).JSON(fiber.Map{"Code": code, "Status": "Failed", "Message": "An unknown error occurred during person record update."})
}
if _, ok := data["nameprefix"]; ok {
    person.NamePrefix = data["nameprefix"]
}
if _, ok := data["namefirst"]; ok {
    person.NameFirst = data["namefirst"]
}
if _, ok := data["namemiddle"]; ok {
    person.NameMiddle = data["namemiddle"]
}
if _, ok := data["namelast"]; ok {
    person.NameLast = data["namelast"]
}
if _, ok := data["namesuffix"]; ok {
    person.NameSuffix = data["namesuffix"]
}

// Save the updated person
if err := database.DB.Save(&person).Error; err != nil {
    code := fiber.StatusInternalServerError
    return c.Status(code).JSON(fiber.Map{"Code": code, "Status": "Failed", "Message": err})
}

数据库连接配置 #

func ConnectMySQL() {

    var DSNW1 string = os.Getenv("IL_APP_MYSQL_WRITE_DBUSER") + ":" + os.Getenv("IL_APP_MYSQL_WRITE_DBPASS") + "@tcp(" + os.Getenv("IL_APP_MYSQL_WRITE_HOST") + ":" + os.Getenv("IL_APP_MYSQL_WRITE_PORT") + ")/" + os.Getenv("IL_APP_MYSQL_WRITE_DBNAME") + "?parseTime=true"
    var DSNR1 string = os.Getenv("IL_APP_MYSQL_READ_DBUSER") + ":" + os.Getenv("IL_APP_MYSQL_READ_DBPASS") + "@tcp(" + os.Getenv("IL_APP_MYSQL_READ_HOST") + ":" + os.Getenv("IL_APP_MYSQL_READ_PORT") + ")/" + os.Getenv("IL_APP_MYSQL_READ_DBNAME") + "?parseTime=true"
    var datetimePrecision = 2

    connection, err := gorm.Open(mysql.New(mysql.Config{
        DSN:                      DSNW1,              // Primary writer instance
        DefaultStringSize:        256,                // add default size for string fields, by default, will use db type `longtext` for fields without size, not a primary key, no index defined and don't have default values
        DefaultDatetimePrecision: &datetimePrecision, // default datetime precision
    }))

    if err != nil {
        panic("Could not connect to the database.")
    }

    DB = connection

    DB.Use(dbresolver.Register(dbresolver.Config{
        Sources:  []gorm.Dialector{mysql.Open(DSNW1)}, // Writer(s)
        Replicas: []gorm.Dialector{mysql.Open(DSNR1)}, // Read replica(s)
        Policy:   dbresolver.RandomPolicy{},           // Load balancing policy
    }))

    connection.AutoMigrate(&models.Account{})
    connection.AutoMigrate(&models.Person{})
    connection.AutoMigrate(&models.PersonCredentials{})
    connection.AutoMigrate(&models.Client{})
    connection.AutoMigrate(&models.ClientCredentials{})

}

生成的表结构 #

CREATE TABLE `people` (
  `id` char(36) NOT NULL,
  `created_at` datetime(2) DEFAULT NULL,
  `updated_at` datetime(2) DEFAULT NULL,
  `deleted_at` datetime(2) DEFAULT NULL,
  `account_id` varchar(256) DEFAULT NULL,
  `name_prefix` varchar(256) DEFAULT NULL,
  `name_first` varchar(256) DEFAULT NULL,
  `name_middle` varchar(256) DEFAULT NULL,
  `name_last` varchar(256) DEFAULT NULL,
  `name_suffix` varchar(256) DEFAULT NULL,
  `notes` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_people_deleted_at` (`deleted_at`),
  KEY `idx_people_name_first` (`name_first`),
  KEY `idx_people_name_last` (`name_last`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

解决方案 #

1. 将字符串和UUID字段改为指针类型 #

Go中string的零值是空字符串，uuid.UUID的零值是全零UUID，GORM会把这些零值当作有效更新内容写入数据库。改用指针类型后，未赋值的字段为nil，GORM会将其处理为NULL，或忽略更新（取决于操作方式）。

修改后的Person结构体：

type Person struct {
    gorm.Model
    ID         uuid.UUID  `gorm:"type:char(36);primary_key"`
    AccountId  *uuid.UUID `gorm:"default:null"`
    NamePrefix *string    `gorm:"default:null"`
    NameFirst  *string    `gorm:"index;default:null"`
    NameMiddle *string    `gorm:"default:null"`
    NameLast   *string    `gorm:"index;default:null"`
    NameSuffix *string    `gorm:"default:null"`
    Notes      *string    `gorm:"default:null"`
}

更新代码中赋值时需要调整为指针：

if _, ok := data["nameprefix"]; ok {
    val := data["nameprefix"]
    person.NamePrefix = &val
}
// 其他字段同理

2. 使用Select指定要更新的字段，避免全量更新 #

Save方法会更新所有字段，包括未修改的零值字段。改用Select明确指定需要更新的字段，只更新请求中存在的内容，保留原字段的NULL值。

修改后的更新逻辑：

// 收集需要更新的字段和对应值
updates := make(map[string]interface{})
if val, ok := data["nameprefix"]; ok {
    updates["name_prefix"] = val
}
if val, ok := data["namefirst"]; ok {
    updates["name_first"] = val
}
if val, ok := data["namemiddle"]; ok {
    updates["name_middle"] = val
}
if val, ok := data["namelast"]; ok {
    updates["name_last"] = val
}
if val, ok := data["namesuffix"]; ok {
    updates["name_suffix"] = val
}

// 执行更新，只修改指定字段
if err := database.DB.Model(&person).Updates(updates).Error; err != nil {
    code := fiber.StatusInternalServerError
    return c.Status(code).JSON(fiber.Map{"Code": code, "Status": "Failed", "Message": err})
}

3. 明确default:null标签的作用 #

default:null标签仅用于GORM自动建表时设置字段默认值，不会影响更新操作中的零值处理。数据库表字段已满足DEFAULT NULL要求（从提供的表结构可确认），但需配合前面的指针类型或字段选择策略才能实现保留NULL的需求。

内容的提问来源于stack exchange，提问作者wmc