mysql - 由于缓存 ID，创建/删除失败？

Question

我在 Visual Studio Code (VSCode) 中有以下代码（为 Go 单元测试设置）。它基本上由4个单元测试组成：

1. TestGormCreateDB：重新创建 MySQL 数据库用户表
2. TestCreateMoon：将用户“Moon”记录添加到用户表
3. TestCreateDaltrey：将用户“Daltrey”添加到用户表
4. TestDeleteDaltrey：从用户表中删除用户“Daltrey”

如果你有 MySQL，你可以在 VSCode 中通过用你的值替换 mySQLHost、mySQLUser、mySQLPass 和 mySQLDBName 来运行它（必须首先在 MySQL 中创建一个空数据库）。然后在 VSCode 中，您可以手动运行 TestGormCreateDB、TestCreateMoon、TestCreateDaltrey、TestDeleteDaltrey、TestCreateDaltrey、TestDeleteDaltrey 等...

每次删除并重新创建 Daltrey 条目时，MySQL 自动递增的 ID 都会增加 1。最终，无论是 TestCreateDaltrey 还是 TestDeleteDaltrey，虽然没有报告错误，但不会更新数据库（有时 TestCreateDaltrey 将无法添加新记录，而其他时候 TestDeleteDaltrey 不会删除它应该删除的现有记录）。这是因为在某些时候，GORM 建模的 User{} 结构中的 ID 采用了来自先前的 Create 或 Delete 的先前 ID，就好像 ID 已被缓存一样，并且 User{} 结构中的 ID 开始使用无效的 ID（ users 表中的记录的 ID 为 4，但代码发现 Daltrey 的 ID 为 3）。我不知道这是我的错误、VSCode 单元测试错误（用户结构从一个单元测试的运行缓存到另一个）、GORM 缓存错误，甚至是 MySQL 缓存错误。

package main

import (
    "fmt"
    "log"
    "testing"

    // MySQL db driver. Aliased to blank identifier, because we need the init()
    //   function to run, but we are not using anything else in the driver.
    // _ "github.com/go-sql-driver/mysql"
    _ "github.com/jinzhu/gorm/dialects/mysql"

    "github.com/jinzhu/gorm"
)

const (
    mySQLHost    = "localhost" // Replace with your MySQL host.
    mySQLPort    = "3306"
    mySQLUser    = "mysqluser" // Enter MySQL user.
    mySQLPass    = "mysqlpass" // Enter MySQL password.
    mySQLDBName  = "mysqldb"   // Enter MySQL database name.
    mySQLCharset = "utf8mb4"   // See https://mathiasbynens.be/notes/mysql-utf8mb4
)

type User struct {
    gorm.Model
    Name  string `gorm:"not null"`
    Email string `gorm:"unique_index; not null"`
}

// Db provides global access to the database.
var db *gorm.DB

func init() {
    var err error
    db, err = gorm.Open("mysql", getDSN())
    if err != nil {
        fmt.Println("failed to connect to database", mySQLDBName)
        log.Fatal(err)
    }
    err = db.DB().Ping()
    if err != nil {
        fmt.Println("failed to ping database", mySQLDBName)
    }
    fmt.Println("successfully connected to database")
    return
}

func getDSN() string {
    dsn := mySQLUser + ":" + mySQLPass
    dsn += "@tcp(" + mySQLHost + ":" + mySQLPort + ")"
    dsn += "/" + mySQLDBName
    dsn += "?charset=" + mySQLCharset
    dsn += "&parseTime=True"
    dsn += "&loc=Local"
    return dsn
}

// String to satisfy the Stringer interface.
func (u User) String() string {
    return fmt.Sprintf("user: ID=%d Name=%s Email=%s", u.ID, u.Name, u.Email)
}

// NewUser creates a new user struct, not adding user to database.
func NewUser(name, email string) User {
    return User{Name: name, Email: email}
}

// NewDatabaseUser creates a new user in the database.
func NewDatabaseUser(name, email string) (user User, err error) {
    // Create a user.
    user = NewUser(name, email)
    fmt.Println("NewDatabaseUser (before Create):", user)
    // Create new user in the database.
    err = user.Create()
    fmt.Println("NewDatabaseUser (after Create):", user)
    if err != nil {
        return User{}, err
    }
    return user, nil
}

// Create a new user, save user info into the database
func (u *User) Create() (err error) {
    err = db.Debug().Create(&u).Error
    fmt.Println("Create:", u)
    if err != nil {
        return err
    }
    return nil
}

// UserByEmail gets a single user given the email
func UserByEmail(email string) (u User, err error) {
    err = db.Debug().Where("email = ?", email).First(&u).Error
    fmt.Println("UserByEmail:", u)
    if err != nil {
        return User{}, err
    }
    return u, nil
}

// Delete user from database
func (u *User) Delete() (err error) {
    delUser, err := UserByEmail(u.Email)
    fmt.Println("Delete delUser: ", delUser)
    if err != nil {
        return err
    }
    // Double check that user has primary key ID.  If primary key is 0,
    //   GORM will delete all users from the database!
    if delUser.ID <= 0 {
        return fmt.Errorf("attempt to delete user with invalid id %d", delUser.ID)
    }
    err = db.Debug().Unscoped().Delete(&delUser).Error
    if err != nil {
        return err
    }
    return nil
}

func TestGormCreateDB(t *testing.T) {
    // Cleanup old stuff.
    if err := db.Debug().DropTableIfExists("users").Error; err != nil {
        t.Fatal(err)
    }

    // Create new stuff.
    if err := db.Debug().CreateTable(&User{}).Error; err != nil {
        t.Fatal(err)
    }
}

func TestCreateMoon(t *testing.T) {
    // Create new user in the database.
    u, err := NewDatabaseUser("Keith Moon", "[e-mail]")
    if err != nil {
        t.Error(err)
    } else {
        fmt.Println("created", u)
    }
}

func TestCreateDaltrey(t *testing.T) {
    // Create new user in the database.
    u, err := NewDatabaseUser("Roger Daltrey", "[e-mail]")
    if err != nil {
        t.Error(err)
    } else {
        fmt.Println("created", u)
    }
}

func TestDeleteDaltrey(t *testing.T) {
    u := User{Email: "[e-mail]"}
    fmt.Println("TestDeleteUser:", u)
    err := u.Delete()
    if err != nil {
        t.Fatalf("delete user with email %s failed: %v\n", u.Email, err)
    }
    fmt.Printf("user with email %s deleted\n", u.Email)
}

标签： mysqlgovisual-studio-codego-gorm