GORM CRUD操作指南 | 李文周的博客

Q1mi commented 4 years ago

https://www.liwenzhou.com/posts/Go/gorm_crud/

李文周的Blog Go语言教程 golang中文教程李文周博客七米 qimi orm gorm 框架 gorm教程 gorm中文教程 Go web framework xorm sqlalchemy

yljphp commented 4 years ago

赞👍👍👍

eryajf commented 4 years ago

package main

import (
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
    "time"
)

// 定义模型
type User struct {
    gorm.Model
    Name string
    Age  int64
}

func main() {
    db, err := gorm.Open("mysql", "root:root1234@(127.0.0.1:13306)/db1?charset=utf8mb4&parseTime=True&loc=Local")
    if err != nil {
        panic(err)
    }
    defer db.Close()
    // 默认情况下，gorm创建的表将会是结构体名称的复数形式，如果不想让它自动复数，可以加一下禁用
    db.SingularTable(true)
    // 2, 把模型与数据库中的表对应起来
    db.AutoMigrate(&User{})
    // 3, 创建
    //u1 := User{Name: "eryajf", Age: 20}
    //db.Create(&u1)
    //u2 := User{Name: "jinzhu", Age: 22}
    //db.Create(&u2)

    // 4,查询
    var user []User
    db.Debug().First(&user) // SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("根据主键查询第一条记录：", user)

    db.Debug().Take(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL LIMIT 1
    fmt.Println("随机获取一条记录：", user)

    db.Debug().Last(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` DESC LIMIT 1
    fmt.Println("根据主键查询最后一条记录：", user)

    db.Debug().Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL
    fmt.Println("查询所有的记录：", user)

    db.Debug().First(&user, 2)      //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`id` = 2)) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查询指定的某条记录：", user) //仅当主键为整型时可用

    Where
    db.Debug().Where("name = ?", "jinzhu").First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查询第一条匹配条件记录：", user)

    db.Debug().Where("name = ?", "jinzhu").Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu'))
    fmt.Println("查询所有匹配条件的记录：", user)

    db.Debug().Where("name <> ?", "jinzhu").Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name <> 'jinzhu'))
    fmt.Println("查询name不等于jinzhu的所有记录：", user)

    db.Debug().Where("name IN (?)", []string{"jinzhu", "jinzhu 2"}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name IN ('jinzhu','jinzhu 2')))
    fmt.Println("查询name在jinzhu和jinzhu 2的所有记录：", user)

    db.Debug().Where("name LIKE ?", "%jin%").Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name LIKE '%jin%'))
    fmt.Println("查询name包含jin的所有记录：", user)

    db.Debug().Where("name = ? AND age >= ?", "jinzhu", "20").Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu' AND age >= '20'))
    fmt.Println("查询两个条件都符合的所有记录：", user)

    oneDay, _ := time.ParseDuration("-24h")
    lastWeek := time.Now().Add(oneDay * 7)
    db.Debug().Where("updated_at > ?", lastWeek).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((updated_at > '2020-03-01 19:45:11'))
    fmt.Println("查询一周内更新的用户记录：", user)

    today := time.Now()
    db.Debug().Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((created_at BETWEEN '2020-03-01 19:52:51' AND '2020-03-08 19:52:51'))
    fmt.Println("查询一周内创建的记录：", user)

    db.Debug().Where(&User{Name: "jinzhu", Age: 22}).First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu') AND (`user`.`age` = 22)) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("通过结构体查询：", user)

    db.Debug().Where(map[string]interface{}{"name": "jinzhu", "age": 22}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu') AND (`user`.`age` = 22))
    fmt.Println("通过map查询：", user)

    db.Debug().Where([]int64{1, 2}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`id` IN (1,2)))
    fmt.Println("通过主键的切片查询：", user)

    db.Debug().Not("name", "jinzhu").First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` NOT IN ('jinzhu'))) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查询name不是jinzhu的第一条记录：", user)

    db.Debug().Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` NOT IN ('jinzhu','jinzhu 2')))
    fmt.Println("查询name不在jinzhu或jinzhu2的所有记录：", user)

    db.Debug().Not([]int64{1, 2, 3}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`id` NOT IN (1,2,3)))
    fmt.Println("查询主键不是1，2，3的所有记录：", user)

    db.Debug().Not([]int64{}).First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查询所有用户中的第一个：", user)

    db.Debug().Not("name = ?", "jinzhu").First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND (NOT (name = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查询name不是jinzhu的第一个用户：", user)

    db.Debug().Not(User{Name: "jinzhu"}).First(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` <> 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("通过结构体查询name不是jinzhu的第一个用户：", user)

    db.Debug().Where("age > ?", 25).Or("age < ?", 23).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((age > 25) OR (age < 23))
    fmt.Println("查询年龄小于23的或者大于25的所有记录：", user)

    // struct
    db.Debug().Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu') OR (`user`.`name` = 'jinzhu 2'))
    fmt.Println("结构体：查询名字是jinzhu的或者是jinzhu 2的所有记录：", user)

    // map
    db.Debug().Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu') OR (`user`.`name` = 'jinzhu 2'))
    fmt.Println("map：查询名字是jinzhu的或者是jinzhu 2的所有记录：", user)

    db.Debug().First(&user, 3)          //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`id` = 3)) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("根据主键查询指定的某条记录：", user) //仅当主键为整型时可用

    db.Debug().First(&user, "id = ?", "string_primary_key") //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((id = 'string_primary_key')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("根据主键是非整形主键获取记录：", user)

    db.Debug().Find(&user, "name = ?", "jinzhu") //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name = 'jinzhu'))
    fmt.Println("查询name为jinzhu的记录：", user)

    db.Debug().Find(&user, "name <> ? AND age > ? ", "jinzhu", "20") //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((name <> 'jinzhu' AND age > '20' ))
    fmt.Println("查询name不是jinzhu且年龄大于20的记录：", user)

    db.Debug().Find(&user, User{Age: 20}) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`age` = 20))
    fmt.Println("通过结构体查询年龄是20的所有记录：", user)

    db.Debug().Find(&user, map[string]interface{}{"age": 20}) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`age` = 20))
    fmt.Println("通过map查询年龄是20的所有记录：", user)

    db.Debug().FirstOrInit(&user, User{Name: "non_existing"}) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("查询name为non_existing的记录：", user)

    db.Debug().Where(User{Name: "jinzhu"}).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("通过结构体查询name为jinzhu的记录：", user)

    db.Debug().FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"}) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println("通过map查询name为jinzhu的记录：", user)

    // 未找到
    db.Debug().Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    // 找到
    db.Debug().Where(User{Name: "jinzhu"}).Attrs(User{Age: 50}).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    // 找到
    db.Debug().Where(User{Name: "jinzhu"}).Assign(User{Age: 50}).FirstOrInit(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().FirstOrCreate(&user, User{Name: "non_existing"}) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Where(User{Name: "jinzhu"}).FirstOrCreate(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'non_existing')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Where(User{Name: "jinzhu"}).Attrs(User{Age: 30}).FirstOrCreate(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL AND ((`user`.`name` = 'jinzhu')) ORDER BY `user`.`id` ASC LIMIT 1
    fmt.Println(user)

    db.Debug().Select("name", "age").Find(&user) //SELECT name FROM `user`  WHERE `user`.`deleted_at` IS NULL'age'
    fmt.Println("查询表中name字段参数为age的记录：", user)

    db.Debug().Select([]string{"name", "age"}).Find(&user) //SELECT name, age FROM `user`  WHERE `user`.`deleted_at` IS NULL
    fmt.Println("列出表中name与age字段：", user)

    db.Debug().Order("age desc,name").Find(&user) //SELECT * FROM `user`  WHERE `user`.`deleted_at` IS NULL ORDER BY age desc,name
    fmt.Println("根据年龄排序来查询：", user)

    db.Debug().Order("age desc").Order("name").Find(&user)
    fmt.Println("根据多个条件排序查询：", user)

}

实在查不动了。

其中的 db.Debug().Select("name", "age").Find(&user)这一句貌似有误的样子，看效果是把age作为name的一个参数了。

ChanXxb commented 4 years ago

七米老师，我使用一个接受过查询结果的实例，再去接收一个新的查询结果，为什么会与用空的实例接收的不一样呢。我可不可以认为gorm体系中实例一旦接收了一个查询结果，同时这个实例也与该条记录的主键进行了绑定

code：db.Debug().Where("Name<>?" ,"张三").First(&u1) // u1接收过id为1的查询记录

debug：SELECT * FROM user WHERE user.id = 1 AND ((Name<>'张三')) ORDER BY user.id ASC LIMIT 1

Nowaterbottles commented 4 years ago

q1mi老师我在使用db.Take(&user)时，获取的随机一条一直是一个结果添加多条数据结果仍不变，后来我去数据库cmd直接输sql语句：SELECT * FROM users ORDER BY id LIMIT 1;得到同样的效果，改变limit数量也是一样。在sql语句中加入order by rand()才能得到随机数据，请问在gorm中怎么解决呢？

pro911 commented 3 years ago

qimi老师这个GORM是v1版本吗? 我现在根据github.com 检索到的项目地址变了,但是我发现项目维护人还是jinzhu. 一些语法上也有了很多变化, 跟着课程后面要自己脑补了

alexclownfish commented 2 years ago

我来交作业了，第一次交作业好激动
以下为代码，Name,Age都设置默认值

package main

import (
    "database/sql"
    "fmt"

    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/mysql"
)

//1.定义模型
type User struct {
    Id   int64
    Name *string       `gorm:"default:'杨文哲'"`
    Age  sql.NullInt64 `gorm:"default:'0'"`
}

func main() {

    // 连接mysql数据库
    db, err := gorm.Open("mysql", "root:123456@(127.0.0.1:3306)/db1?charset=utf8mb4&parseTime=True&loc=Local")
    if err != nil {
        panic(err)
    }
    defer db.Close()       //关闭数据库
    db.SingularTable(true) //禁用表名 复数

    //创建表  自动迁移（把结构体和数据表进行对应）
    //2.把模型与数据库中的表对应起来
    err = db.Set("gorm:table_options", "ENGINE=InnoDB DEFAULT CHARSET=utf8").AutoMigrate(&User{}).Error //自动创建表的时候修改表的字符集
    if err != nil {
        panic(err)
    }

    //3.创建
    u := User{Name: new(string), Age: sql.NullInt64{Int64: 0, Valid: true}} //在代码层面创建一个User对象
    fmt.Println(db.NewRecord(&u))                                           //判断主键是否为空，若为空返回true
    db.Debug().Create(&u)                                                   //新增alexclownfish 15这条数据到u对象内
    fmt.Println(db.NewRecord(&u))                                           //判断逐渐是否为空，若不为空返回false
}

mysql> select * from user;
+----+---------------+------+
| id | name          | age  |
+----+---------------+------+
|  1 | 杨文哲        |   99 |
|  2 | 杨文哲        |   99 |
|  3 |               |   99 |
|  4 | 杨文哲        |   99 |
|  5 | 杨文哲        |   39 |
|  6 | alexclownfish |   39 |
|  7 | 杨文哲        |   58 |
|  8 |               |   58 |
|  9 | 杨文哲        |   18 |
| 10 |               |   18 |
| 11 | NULL          |   18 |
| 12 | NULL          |  448 |
| 13 | 杨文哲        | NULL |
| 14 |               |    0 |
+----+---------------+------+
14 rows in set (0.00 sec)

Q1mi / BlogComments

GORM CRUD操作指南 | 李文周的博客 #71