文章目录 一、GORM简介1、什么是Gorm?2、为什么选择GORM?3、如何使用GORM?4、约定大于配置大于编码GORM 默认使用ID作为表的主键GORM 默认使用结构体名的 snake case 复数作为表名临时指定表名gor
The fantastic ORM library for Golang aims to be developer friendly. ——官网
Preload
、Joins
的预加载## 必须安装gormgo get -u gorm.io/gorm ## 安装相应的数据库驱动。GORM 官方支持的数据库类型有: mysql, PostgreSQL, SQlite, SQL Servergo get -u gorm.io/driver/mysql
package mainimport ("fmt""gorm.io/driver/mysql""gorm.io/gorm""time")func main() {// 参考 https://GitHub.com/go-sql-driver/mysql#dsn-data-source-name 获取详情//dsn := "user:pass@tcp(127.0.0.1:3306)/dbname?charset=utf8mb4&parseTime=True&loc=Local"//db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})db, err := gorm.Open(mysql.New(mysql.Config{DSN: "root:123456@tcp(192.168.168.101:3306)/gorm?charset=utf8&parseTime=True&loc=Local", // DSN data source nameDefaultStringSize: 256, // string 类型字段的默认长度DisableDatetimePrecision: true, // 禁用 datetime 精度,MySQL 5.6 之前的数据库不支持DontSupportRenameIndex: true, // 重命名索引时采用删除并新建的方式,MySQL 5.7 之前的数据库和 MariaDB 不支持重命名索引DontSupportRenameColumn: true, // 用 `change` 重命名列,MySQL 8 之前的数据库和 MariaDB 不支持重命名列SkipInitializeWithVersion: false, // 根据当前 MySQL 版本自动配置}), &gorm.Config{})if err != nil {panic("failed to connect database")}// ----------------------------数据库连接池----------------------------sqlDB, err := db.DB()// SetMaxIdleConns 设置空闲连接池中连接的最大数量sqlDB.SetMaxIdleConns(10)// SetMaxOpenConns 设置打开数据库连接的最大数量。sqlDB.SetMaxOpenConns(100)// SetConnMaxLifetime 设置了连接可复用的最大时间。sqlDB.SetConnMaxLifetime(time.Hour)fmt.Println("success to link mysql")select {}}
GORM 倾向于约定,而不是配置。默认情况下,GORM 使用 ID
作为主键,使用结构体名的 蛇形复数
作为表名,字段名的 蛇形
作为列名,并使用 CreatedAt
、UpdatedAt
字段追踪创建、更新时间。
遵循 GORM 已有的约定,可以减少配置和代码量。如果约定不符合需求,GORM 允许自定义配置它们。
type User struct { ID string // 默认情况下,名为 `ID` 的字段会作为表的主键 Name string}
eg:结构体 userinfo,其表名默认为user_infos
蛇形命名法(snake case)类似 驼峰命名法(camel case) ,都是要克服单词间的空格,从而把不同单词串连起来,最终达到创造出一种新的
单词
的效果。
// 根据 User 的字段创建 `deleted_users` 表db.Table("deleted_users").AutoMigrate(&User{})// 从另一张表查询数据var deletedUsers []Userdb.Table("deleted_users").Find(&deletedUsers)// SELECT * FROM deleted_users;db.Table("deleted_users").Where("name = ?", "jinzhu").Delete(&User{})// DELETE FROM deleted_users WHERE name = 'jinzhu';
GORM 定义一个 gorm.Model
结构体,其包括字段 ID
、CreatedAt
、UpdatedAt
、DeletedAt
。
// gorm.Model 的定义type Model struct { ID uint `gorm:"primaryKey"` CreatedAt time.Time UpdatedAt time.Time DeletedAt gorm.DeletedAt `gorm:"index"`}
声明 model 时,tag 是可选的,GORM 支持以下 tag。 tag 名大小写不敏感,但建议使用驼峰 camelCase
命名风格。
标签名 | 说明 |
---|---|
column | 指定 db 表列名 |
type | 列数据类型,推荐使用兼容性好的通用类型 |
serializer | 指定如何序列化/反序列化到数据库, e.g: serializer:JSON/gob/unixtime |
size | 指定列的数据大小/长度, e.g: size:256 |
primaryKey | 指定列作为主键 |
unique | 指定列唯一 |
default | 指定列为默认值 |
precision | 指定列的精度 |
scale | specifies column scale |
not null | 指定列 NOT NULL |
autoIncrement | 指定列为自增列 |
autoIncrementIncrement | auto increment step, controls the interval between successive column values |
embedded | 嵌入字段 |
embeddedPrefix | 嵌入字段的列名前缀 |
autoCreateTime | 记录创建时间 |
autoUpdateTime | 记录创建/更新时间 |
index | 根据选项创建索引 |
uniqueIndex | 唯一索引 |
check | 创建检查约束, eg: check:age > 13 , refer Constraints |
<- | 设置字段的写权限<-:create create-only field, <-:update update-only field, <-:false no write permission,<- create and update permission |
-> | 设置字段读权限->:false no read permission |
- | 忽略当前字段- no read/write permission, -:migration no migrate permission,-:all no read/write/migrate permission |
comment | 【迁移时】为字段添加注释 |
可导出的字段在使用 GORM 进行 CRUD 时拥有全部的权限,此外,GORM 允许用标签控制字段级别的权限。这样就可以让一个字段的权限是只读、只写、只创建、只更新或者被忽略。
type User struct { Name string `gorm:"<-:create"` // allow read and create Name string `gorm:"<-:update"` // allow read and update Name string `gorm:"<-"` // allow read and write (create and update) Name string `gorm:"<-:false"` // allow read, disable write permission Name string `gorm:"->"` // readonly (disable write permission unless it configured) Name string `gorm:"->;<-:create"` // allow read and create Name string `gorm:"->:false;<-:create"` // createonly (disabled read from db) Name string `gorm:"-"` // ignore this field when write and read with struct Name string `gorm:"-:all"` // ignore this field when write, read and migrate with struct Name string `gorm:"-:migration"` // ignore this field when migrate with struct}
GORM 约定使用 CreatedAt
、UpdatedAt
追踪创建/更新时间。如果定义了这种字段,GORM 在创建、更新时会自动填充当前时间。
要使用不同名称的字段,可以配置 autoCreateTime
、autoUpdateTime
标签。
type User struct { CreatedAt time.Time // 在创建时,如果该字段值为零值,则使用当前时间填充 UpdatedAt int // 在创建时该字段值为零值或者在更新时,使用当前时间戳秒数填充 Updated int64 `gorm:"autoUpdateTime:nano"` // 使用时间戳填纳秒数充更新时间 Updated int64 `gorm:"autoUpdateTime:milli"` // 使用时间戳毫秒数填充更新时间 Created int64 `gorm:"autoCreateTime"` // 使用时间戳秒数填充创建时间}
可以通过标签 embedded
将一个结构体嵌入另一个结构体。可以使用标签 embeddedPrefix
来为 db 中的字段名添加前缀。
type Author struct { Name string Email string}type Blog struct { ID int Author Author `gorm:"embedded;embeddedPrefix:author_"` Upvotes int32}// 等效于type Blog struct { ID int64 AuthorName string AuthorEmail string Upvotes int32}
Gorm增删改查CRUD最常用到的语法。
// -----------------------------插入单条记录---------------------------------user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()}result := db.Create(&user) // 通过数据的指针来创建user.ID // 返回插入数据的主键result.Error // 返回 errorresult.RowsAffected // 返回插入记录的条数// 创建记录并更新给出的字段db.Select("Name", "Age", "CreatedAt").Create(&user)// INSERT INTO `users` (`name`,`age`,`created_at`) VALUES ("jinzhu", 18, "2020-07-04 11:05:21.775")
// -----------------------------批量插入多条记录-----------------------------//要有效地插入大量记录,请将一个 slice 传递给 Create 方法。 GORM 将生成单独一条SQL语句来插入所有数据,并回填主键的值,钩子方法也会被调用。var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}db.Create(&users)//使用 CreateInBatches 分批创建时,你可以指定每批的数量。var users = []User{{name: "jinzhu_1"}, ...., {Name: "jinzhu_10000"}}// 数量为 100db.CreateInBatches(users, 100)
GORM 允许用户定义的钩子有 BeforeSave
, BeforeCreate
, AfterSave
, AfterCreate
创建记录时将调用这些钩子方法。
func (u *User) BeforeCreate(tx *gorm.DB) (err error) { u.UUID = uuid.New() if u.Role == "admin" { return errors.New("invalid role") } return}// 如果想跳过 钩子 方法,可以使用 SkipHooks 会话模式DB.Session(&gorm.Session{SkipHooks: true}).Create(&user)DB.Session(&gorm.Session{SkipHooks: true}).Create(&users)DB.Session(&gorm.Session{SkipHooks: true}).CreateInBatches(users, 100)
db.Model(&User{}).Create(map[string]interface{}{ "Name": "jinzhu", "Age": 18,})// batch insert from `[]map[string]interface{}{}`db.Model(&User{}).Create([]map[string]interface{}{ {"Name": "jinzhu_1", "Age": 18}, {"Name": "jinzhu_2", "Age": 20},})
对于声明了默认值的字段,像 0
、''
、false
等零值是不会保存到数据库。需要使用指针类。
type User struct { gorm.Model Name string `gorm:"default:xiaoming"` Age *int `gorm:"default:18"` Active sql.NullBool `gorm:"default:true"`}type User struct { ID string `gorm:"default:uuid_generate_v3()"` // db func FirstName string LastName string Age uint8 FullName string `gorm:"->;type:GENERATED ALWAYS AS (concat(firstname,' ',lastname));default:(-);"`}
GORM 提供了 First
、Take
、Last
方法,以便从数据库中检索单个对象。当查询数据库时它添加了 LIMIT 1
条件,且没有找到记录时,它会返回 ErrRecordNotFound
错误。
如果想避免ErrRecordNotFound
错误,可以使用Find
,比如db.Limit(1).Find(&user)
,Find
方法可以接受struct和slice的数据。
// 获取第一条记录(主键升序)。只有在目标 struct 是指针或者通过 db.Model() 指定 model 时,该方法才有效。db.First(&user)// SELECT * FROM users ORDER BY id LIMIT 1;// 获取一条记录,没有指定排序字段db.Take(&user)// SELECT * FROM users LIMIT 1;// 获取最后一条记录(主键降序)db.Last(&user)// SELECT * FROM users ORDER BY id DESC LIMIT 1;result := db.First(&user)result.RowsAffected // 返回找到的记录数result.Error // returns error or nil// 检查 ErrRecordNotFound 错误errors.Is(result.Error, gorm.ErrRecordNotFound)
db.First(&user, 10)// SELECT * FROM users WHERE id = 10;db.First(&user, "10")// SELECT * FROM users WHERE id = 10;db.Find(&users, []int{1,2,3})// SELECT * FROM users WHERE id IN (1,2,3);db.First(&user, "id = ?", "1b74413f-f3b8-409f-ac47-e8c062e3472a")// SELECT * FROM users WHERE id = "1b74413f-f3b8-409f-ac47-e8c062e3472a";
// Get all recordsresult := db.Find(&users)// SELECT * FROM users;result.RowsAffected // returns found records count, equals `len(users)`result.Error // returns error
// -----------------------------代码示例-------------------------func (u *User) AfterFind(tx *gorm.DB) (err error) { if u.MemberShip == "" { u.MemberShip = "user" } return}
//-----------------------------string条件-------------------------// Get first matched recorddb.Where("name = ?", "jinzhu").First(&user)// SELECT * FROM users WHERE name = 'jinzhu' ORDER BY id LIMIT 1;// Get all matched recordsdb.Where("name <> ?", "jinzhu").Find(&users)// SELECT * FROM users WHERE name <> 'jinzhu';// INdb.Where("name IN ?", []string{"jinzhu", "jinzhu 2"}).Find(&users)// SELECT * FROM users WHERE name IN ('jinzhu','jinzhu 2');// LIKEdb.Where("name LIKE ?", "%jin%").Find(&users)// SELECT * FROM users WHERE name LIKE '%jin%';// ANDdb.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)// SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;// Timedb.Where("updated_at > ?", lastWeek).Find(&users)// SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';// BETWEENdb.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)// SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';//-----------------------------筛选特定字段-------------------------db.Select("name", "age").Find(&users)// SELECT name, age FROM users;db.Select([]string{"name", "age"}).Find(&users)// SELECT name, age FROM users;db.Table("users").Select("COALESCE(age,?)", 42).Rows()// SELECT COALESCE(age,'42') FROM users;//-----------------------------Struct & Map 条件-------------------------// Structdb.Where(&User{Name: "jinzhu", Age: 20}).First(&user)// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;// Mapdb.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;// Slice of primary keysdb.Where([]int64{20, 21, 22}).Find(&users)// SELECT * FROM users WHERE id IN (20, 21, 22);//-----------------------------内联条件-------------------------// Get by primary key if it were a non-integer typedb.First(&user, "id = ?", "string_primary_key")// SELECT * FROM users WHERE id = 'string_primary_key';// Plain SQLdb.Find(&user, "name = ?", "jinzhu")// SELECT * FROM users WHERE name = "jinzhu";db.Find(&users, "name <> ? AND age > ?", "jinzhu", 20)// SELECT * FROM users WHERE name <> "jinzhu" AND age > 20;// Structdb.Find(&users, User{Age: 20})// SELECT * FROM users WHERE age = 20;// Mapdb.Find(&users, map[string]interface{}{"age": 20})// SELECT * FROM users WHERE age = 20;//-----------------------------Not条件-------------------------db.Not("name = ?", "jinzhu").First(&user)// SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;// Not Indb.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");// Structdb.Not(User{Name: "jinzhu", Age: 18}).First(&user)// SELECT * FROM users WHERE name <> "jinzhu" AND age <> 18 ORDER BY id LIMIT 1;// Not In slice of primary keysdb.Not([]int64{1,2,3}).First(&user)// SELECT * FROM users WHERE id NOT IN (1,2,3) ORDER BY id LIMIT 1;//-----------------------------Or条件-------------------------db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users)// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin';// Structdb.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2", Age: 18}).Find(&users)// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);// Mapdb.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2", "age": 18}).Find(&users)// SELECT * FROM users WHERE name = 'jinzhu' OR (name = 'jinzhu 2' AND age = 18);//-----------------------------Order排序-------------------------db.Order("age desc, name").Find(&users)// SELECT * FROM users ORDER BY age desc, name;// Multiple ordersdb.Order("age desc").Order("name").Find(&users)// SELECT * FROM users ORDER BY age desc, name;db.Clauses(clause.OrderBy{ Expression: clause.Expr{SQL: "FIELD(id,?)", Vars: []interface{}{[]int{1, 2, 3}}, WithoutParentheses: true},}).Find(&User{})// SELECT * FROM users ORDER BY FIELD(id,1,2,3)//-----------------------------Limit和Offset-------------------------db.Limit(3).Find(&users)// SELECT * FROM users LIMIT 3;// Cancel limit condition with -1db.Limit(10).Find(&users1).Limit(-1).Find(&users2)// SELECT * FROM users LIMIT 10; (users1)// SELECT * FROM users; (users2)db.Offset(3).Find(&users)// SELECT * FROM users OFFSET 3;db.Limit(10).Offset(5).Find(&users)// SELECT * FROM users OFFSET 5 LIMIT 10; // 分页// Cancel offset condition with -1db.Offset(10).Find(&users1).Offset(-1).Find(&users2)// SELECT * FROM users OFFSET 10; (users1)// SELECT * FROM users; (users2)//-----------------------------Group by和Having-------------------------db.Model(&User{}).Select("name, sum(age) as total").Where("name LIKE ?", "group%").Group("name").First(&result)// SELECT name, sum(age) as total FROM `users` WHERE name LIKE "group%" GROUP BY `name` LIMIT 1db.Model(&User{}).Select("name, sum(age) as total").Group("name").Having("name = ?", "group").Find(&result)// SELECT name, sum(age) as total FROM `users` GROUP BY `name` HAVING name = "group"//-----------------------------Distinct-------------------------db.Distinct("name", "age").Order("name, age desc").Find(&results)//-----------------------------Scan-------------------------//Scanning results into a struct works similarly to the way we use Findtype Result struct { Name string Age int}var result Resultdb.Table("users").Select("name", "age").Where("name = ?", "AntoNIO").Scan(&result)// Raw SQLdb.Raw("SELECT name, age FROM users WHERE name = ?", "Antonio").Scan(&result)
//-----------------------------保存所有字段-------------------------db.First(&user)user.Name = "jinzhu 2"user.Age = 100db.Save(&user)// UPDATE users SET name='jinzhu 2', age=100, birthday='2016-01-01', updated_at = '2013-11-17 21:34:10' WHERE id=111;//-----------------------------更新单个列-------------------------// 条件更新db.Model(&User{}).Where("active = ?", true).Update("name", "hello")// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE active=true;// User 的 ID 是 `111`db.Model(&user).Update("name", "hello")// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;// 根据条件和 model 的值进行更新db.Model(&user).Where("active = ?", true).Update("name", "hello")// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111 AND active=true;//-----------------------------更新多列-------------------------// 根据 `struct` 更新属性,只会更新非零值的字段db.Model(&user).Updates(User{Name: "hello", Age: 18, Active: false})// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;// 根据 `map` 更新属性db.Model(&user).Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})// UPDATE users SET name='hello', age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;//-----------------------------更新选定字段-------------------------// 使用 Map 进行 Select// User's ID is `111`:db.Model(&user).Select("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})// UPDATE users SET name='hello' WHERE id=111;db.Model(&user).Omit("name").Updates(map[string]interface{}{"name": "hello", "age": 18, "active": false})// UPDATE users SET age=18, active=false, updated_at='2013-11-17 21:34:10' WHERE id=111;// 使用 Struct 进行 Select(会 select 零值的字段)db.Model(&user).Select("Name", "Age").Updates(User{Name: "new_name", Age: 0})// UPDATE users SET name='new_name', age=0 WHERE id=111;// Select 所有字段(查询包括零值字段的所有字段)db.Model(&user).Select("*").Update(User{Name: "jinzhu", Role: "admin", Age: 0})// Select 除 Role 外的所有字段(包括零值字段的所有字段)db.Model(&user).Select("*").Omit("Role").Update(User{Name: "jinzhu", Role: "admin", Age: 0})
//-----------------------------批量更新-------------------------// 根据 struct 更新db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18})// UPDATE users SET name='hello', age=18 WHERE role = 'admin';// 根据 map 更新db.Table("users").Where("id IN ?", []int{10, 11}).Updates(map[string]interface{}{"name": "hello", "age": 18})// UPDATE users SET name='hello', age=18 WHERE id IN (10, 11);//-----------------------------禁止全局更新-------------------------//如果在没有任何条件的情况下执行批量更新,默认情况下,GORM 不会执行该操作,并返回 ErrMissingWhereClause 错误。对此,必须加一些条件,或者使用原生 SQL,或者启用 AllowGlobalUpdate 模式db.Model(&User{}).Update("name", "jinzhu").Error // gorm.ErrMissingWhereClausedb.Model(&User{}).Where("1 = 1").Update("name", "jinzhu")// UPDATE users SET `name` = "jinzhu" WHERE 1=1db.Exec("UPDATE users SET name = ?", "jinzhu")// UPDATE users SET name = "jinzhu"db.Session(&gorm.Session{AllowGlobalUpdate: true}).Model(&User{}).Update("name", "jinzhu")// UPDATE users SET `name` = "jinzhu"//-----------------------------更新记录数-------------------------// 通过 `RowsAffected` 得到更新的记录数result := db.Model(User{}).Where("role = ?", "admin").Updates(User{Name: "hello", Age: 18})// UPDATE users SET name='hello', age=18 WHERE role = 'admin';result.RowsAffected // 更新的记录数result.Error // 更新的错误
//-----------------------------更新 Hook-------------------------//GORM 支持 BeforeSave、BeforeUpdate、AfterSave、AfterUpdate 钩子,这些方法将在更新记录时被调用func (u *User) BeforeUpdate(tx *gorm.DB) (err error) { if u.Role == "admin" { return errors.New("admin user not allowed to update") } return}
//-----------------------------删除一条记录-------------------------//删除一条记录时,删除对象需要指定主键,否则会触发 批量 Delete// Email 的 ID 是 `10`db.Delete(&email)// DELETE from emails where id = 10;// 带额外条件的删除db.Where("name = ?", "jinzhu").Delete(&email)// DELETE from emails where id = 10 AND name = "jinzhu";//-----------------------------根据主键删除-------------------------db.Delete(&User{}, 10)// DELETE FROM users WHERE id = 10;db.Delete(&User{}, "10")// DELETE FROM users WHERE id = 10;db.Delete(&users, []int{1,2,3})// DELETE FROM users WHERE id IN (1,2,3);
//-----------------------------批量删除-------------------------db.Where("email LIKE ?", "%jinzhu%").Delete(&Email{})// DELETE from emails where email LIKE "%jinzhu%";db.Delete(&Email{}, "email LIKE ?", "%jinzhu%")// DELETE from emails where email LIKE "%jinzhu%";//-----------------------------禁止全局删除-------------------------//如果在没有任何条件的情况下执行批量删除,GORM 不会执行该操作,并返回 ErrMissingWhereClause 错误。对此,你必须加一些条件,或者使用原生 SQL,或者启用 AllowGlobalUpdate 模式db.Delete(&User{}).Error // gorm.ErrMissingWhereClausedb.Where("1 = 1").Delete(&User{})// DELETE FROM `users` WHERE 1=1db.Exec("DELETE FROM users")// DELETE FROM usersdb.Session(&gorm.Session{AllowGlobalUpdate: true}).Delete(&User{})// DELETE FROM users
如果model包含gorm.deleted_at 字段(gorm.Model 已经包含了该字段),它将自动获得软删除的能力!拥有软删除能力的模型调用 Delete 时,记录不会从数据库中被真正删除。GORM 会将 DeletedAt 置为当前时间,并且不能再通过普通的查询方法找到该记录。
//-----------------------------软删除-------------------------//By default, gorm.Model uses *time.Time as the value for the DeletedAt field, and it provides other data formats support with plugin gorm.io/plugin/soft_deleteimport "gorm.io/plugin/soft_delete"type User struct { ID uint Name string `gorm:"uniqueIndex:udx_name"` DeletedAt soft_delete.DeletedAt `gorm:"uniqueIndex:udx_name"` // DeletedAt soft_delete.DeletedAt `gorm:"softDelete:nano"` //IsDel soft_delete.DeletedAt `gorm:"softDelete:flag"`}// QuerySELECT * FROM users WHERE is_del = 0;// DeleteUPDATE users SET is_del = 1 WHERE ID = 1;// user 的 ID 是 `111`db.Delete(&user)// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE id = 111;// 批量删除db.Where("age = ?", 20).Delete(&User{})// UPDATE users SET deleted_at="2013-10-29 10:23" WHERE age = 20;// 在查询时会忽略被软删除的记录db.Where("age = 20").Find(&user)// SELECT * FROM users WHERE age = 20 AND deleted_at IS NULL;//-----------------------------查找被软删除的记录-------------------------db.Unscoped().Where("age = 20").Find(&users)// SELECT * FROM users WHERE age = 20;//-----------------------------永久删除-------------------------db.Unscoped().Delete(&order)// DELETE FROM orders WHERE id=10;
//-----------------------------删除 Hook-------------------------func (u *User) BeforeDelete(tx *gorm.DB) (err error) { if u.Role == "admin" { return errors.New("admin user not allowed to delete") } return}
//-----------------------------原生查询 SQL 和 Scan-------------------------type Result struct { ID int Name string Age int}var result Resultdb.Raw("SELECT id, name, age FROM users WHERE name = ?", 3).Scan(&result)db.Raw("SELECT id, name, age FROM users WHERE name = ?", 3).Scan(&result)var age intdb.Raw("SELECT SUM(age) FROM users WHERE role = ?", "admin").Scan(&age)var users []Userdb.Raw("UPDATE users SET name = ? WHERE age = ? RETURNING id, name", "jinzhu", 20).Scan(&users)
//-----------------------------Exec 原生 SQL-------------------------db.Exec("DROP TABLE users")db.Exec("UPDATE orders SET shipped_at = ? WHERE id IN ?", time.Now(), []int64{1, 2, 3})// Exec with SQL Expressiondb.Exec("UPDATE users SET money = ? WHERE name = ?", gorm.Expr("money * ? + ?", 10000, 1), "jinzhu")
//获取 *sql.Row 结果// 使用 GORM API 构建 SQLrow := db.Table("users").Where("name = ?", "jinzhu").Select("name", "age").Row()row.Scan(&name, &age)// 使用原生 SQLrow := db.Raw("select name, age, email from users where name = ?", "jinzhu").Row()row.Scan(&name, &age, &email)// 原生 SQLrows, err := db.Raw("select name, age, email from users where name = ?", "jinzhu").Rows()defer rows.Close()for rows.Next() { rows.Scan(&name, &age, &email) // 业务逻辑...}rows, err := db.Model(&User{}).Where("name = ?", "jinzhu").Select("name, age, email").Rows() // (*sql.Rows, error)defer rows.Close()var user Userfor rows.Next() { // ScanRows 将一行扫描至 user db.ScanRows(rows, &user) // 业务逻辑...}
来源地址:https://blog.csdn.net/qq_41822345/article/details/126072106
--结束END--
本文标题: Gorm—Go语言数据库框架
本文链接: https://www.lsjlt.com/news/374835.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0