Gorm 对mysql进行删除操作，删除后进行查询，查询出的结果不对。

Your Question

前端触发，对mysql的数据进行同步（删除过时记录）。在同步后，马上进行查询操作。同一个页面的两次请求。 发现查询的数据不对，比较少，多次刷新后正常。正常后再刷新数据又不齐。 感觉是删除时对mysql加了锁。 请问是否有状态查询mysql的表是否为可读状态？ 这种问题如何处理？

The document you expected this should be explained

Expected answer

Comment From: herosql

建议使用gorm.DB.Model() 直接操作数据，我在项目中和你遇到了同样的问题，代码如下：

package main

import (
    "fmt"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

type RolePermission struct {
    ID           string `gorm:"primaryKey;column:id" json:"-"`
    RoleID       string `gorm:"column:role_id" json:"roleId"`
    PermissionID string `gorm:"column:permission_id" json:"permissionId"`
}

func (m *RolePermission) TableName() string {
    return "role_permission"
}

func main() {

    Db, err := gorm.Open(mysql.New(mysql.Config{
        DSN: "root:123456@tcp(127.0.0.1:3306)/hpvdb_test?charset=utf8mb4&parseTime=True&loc=Local",
    }), &gorm.Config{
        CreateBatchSize: 1000,
    })

    if err != nil {
        panic(err)
    }

    rolePermission := RolePermission{}
    rolePermissionTable := Db.Table("role_permission")
    addList := []RolePermission{
        RolePermission{
            ID:           "1",
            RoleID:       "1",
            PermissionID: "1",
        },
        RolePermission{
            ID:           "2",
            RoleID:       "1",
            PermissionID: "2",
        },
    }
    rolePermissionTable.Model(rolePermission).CreateInBatches(addList, len(addList))

    rolePermissionTable.Model(rolePermission).Delete(&rolePermission, "id = ?", "1")

    list := []RolePermission{}
    rolePermissionTable.Model(rolePermission).Where("role_id", "1").Find(&list)

    fmt.Println("targetList:1", "nowListLength:", len(list))
}

查看数据库，发现role_permission表里存在一条刚插入的数据，这里查询出来，条数却为0。 尝试另一种写法如下：

package main

import (
    "fmt"

    "gorm.io/driver/mysql"
    "gorm.io/gorm"
)

type RolePermission struct {
    ID           string `gorm:"primaryKey;column:id" json:"-"`
    RoleID       string `gorm:"column:role_id" json:"roleId"`
    PermissionID string `gorm:"column:permission_id" json:"permissionId"`
}

func (m *RolePermission) TableName() string {
    return "role_permission"
}

func main() {

    Db, err := gorm.Open(mysql.New(mysql.Config{
        DSN: "root:123456@tcp(127.0.0.1:3306)/hpvdb_test?charset=utf8mb4&parseTime=True&loc=Local",
    }), &gorm.Config{
        CreateBatchSize: 1000,
    })

    if err != nil {
        panic(err)
    }

    rolePermission := RolePermission{}
    // rolePermissionTable := Db.Table("role_permission")
    addList := []RolePermission{
        RolePermission{
            ID:           "1",
            RoleID:       "1",
            PermissionID: "1",
        },
        RolePermission{
            ID:           "2",
            RoleID:       "1",
            PermissionID: "2",
        },
    }

    /*
        rolePermissionTable.Model(rolePermission).CreateInBatches(addList, len(addList))

        rolePermissionTable.Model(rolePermission).Delete(&rolePermission, "id = ?", "1")

        list := []RolePermission{}
        rolePermissionTable.Model(rolePermission).Where("role_id", "1").Find(&list)

        fmt.Println("targetList:1", "nowListLength:", len(list))
    */
    Db.Model(RolePermission{}).CreateInBatches(addList, len(addList))

    Db.Model(RolePermission{}).Delete(&rolePermission, "id = ?", "1")

    list := []RolePermission{}
    Db.Model(RolePermission{}).Where("role_id", "1").Find(&list)
    fmt.Println("targetList:1", "nowListLength:", len(list))
}

结果数据正常。

Comment From: limawanyan

感谢！我碰到了类似的问题，单独开启了一个goroutines执行任务，在任务中会有查询数据库的操作，程序运行一段时间后出现查询不到新插入数据库记录(其他程序插入的数据)的情况，重启项目就正常了。 我也是使用的Db.Table("role_permission")这种方式进行数据查询，没有使用Model。我现在声明一个Model，使用Model试试看还会不会出现查询不到新数据的情况。 你有了解到为什么使用Table会出现数据不一致的情况吗？

Comment From: ritbrz

建议使用gorm.DB.Model() 直接操作数据，我在项目中和你遇到了同样的问题，代码如下：

```go package main

import ( "fmt"

"gorm.io/driver/mysql" "gorm.io/gorm" )

type RolePermission struct { ID string gorm:"primaryKey;column:id" json:"-" RoleID string gorm:"column:role_id" json:"roleId" PermissionID string gorm:"column:permission_id" json:"permissionId" }

func (m *RolePermission) TableName() string { return "role_permission" }

func main() {

Db, err := gorm.Open(mysql.New(mysql.Config{ DSN: "root:123456@tcp(127.0.0.1:3306)/hpvdb_test?charset=utf8mb4&parseTime=True&loc=Local", }), &gorm.Config{ CreateBatchSize: 1000, })

if err != nil { panic(err) }

rolePermission := RolePermission{} rolePermissionTable := Db.Table("role_permission") addList := []RolePermission{ RolePermission{ ID: "1", RoleID: "1", PermissionID: "1", }, RolePermission{ ID: "2", RoleID: "1", PermissionID: "2", }, } rolePermissionTable.Model(rolePermission).CreateInBatches(addList, len(addList))

rolePermissionTable.Model(rolePermission).Delete(&rolePermission, "id = ?", "1")

list := []RolePermission{} rolePermissionTable.Model(rolePermission).Where("role_id", "1").Find(&list)

fmt.Println("targetList:1", "nowListLength:", len(list)) } ```

查看数据库，发现role_permission表里存在一条刚插入的数据，这里查询出来，条数却为0。 尝试另一种写法如下：

```go package main

import ( "fmt"

"gorm.io/driver/mysql" "gorm.io/gorm" )

type RolePermission struct { ID string gorm:"primaryKey;column:id" json:"-" RoleID string gorm:"column:role_id" json:"roleId" PermissionID string gorm:"column:permission_id" json:"permissionId" }

func (m *RolePermission) TableName() string { return "role_permission" }

func main() {

Db, err := gorm.Open(mysql.New(mysql.Config{ DSN: "root:123456@tcp(127.0.0.1:3306)/hpvdb_test?charset=utf8mb4&parseTime=True&loc=Local", }), &gorm.Config{ CreateBatchSize: 1000, })

if err != nil { panic(err) }

rolePermission := RolePermission{} // rolePermissionTable := Db.Table("role_permission") addList := []RolePermission{ RolePermission{ ID: "1", RoleID: "1", PermissionID: "1", }, RolePermission{ ID: "2", RoleID: "1", PermissionID: "2", }, }

/* rolePermissionTable.Model(rolePermission).CreateInBatches(addList, len(addList))

  rolePermissionTable.Model(rolePermission).Delete(&rolePermission, "id = ?", "1")

  list := []RolePermission{}
  rolePermissionTable.Model(rolePermission).Where("role_id", "1").Find(&list)

  fmt.Println("targetList:1", "nowListLength:", len(list))

*/ Db.Model(RolePermission{}).CreateInBatches(addList, len(addList))

Db.Model(RolePermission{}).Delete(&rolePermission, "id = ?", "1")

list := []RolePermission{} Db.Model(RolePermission{}).Where("role_id", "1").Find(&list) fmt.Println("targetList:1", "nowListLength:", len(list)) } ```

结果数据正常。

By logging the SQL, you can find the where clause in the DELETE operation would be included in your third query when you reuse the *gorm.DB

INSERT INTO `role_permission` (`id`,`role_id`,`permission_id`) VALUES ("1","1","1"),("2","1","2")

DELETE FROM `role_permission` WHERE id = "1"

SELECT * FROM `role_permission` WHERE id = "1" AND `role_id` = "1"