数据库:MySQL
编程语言:GO 数据库操作 连接
db,err :=gorm.Open("mysql","root:ms126127@(193.112.126.44:3306)/test?charset=utf8&parseTime=True") defer db.Close()
数据迁移
自动迁移模式将保持更新到最新。
警告:自动迁移仅仅会创建表,增加缺少的列和缺少的索引,并且不会改变现有列的类型或删除未使用的列以保护数据。(只增不减)
db.AutoMigrate(&User{}) db.AutoMigrate(&User{}, &Product{}, &Order{}) // 创建表时添加表后缀 我写了一个支持utf8编码的写法。 db.Set("gorm:table_options", "ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;").AutoMigrate(&User{})
检查表是否存在
// 检查模型`User`表是否存在 db.HasTable(&User{}) // 检查表`users`是否存在 db.HasTable("users")
创建表
db.CreateTable(&MyTable{}) db.Set("gorm:table_options", "ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;").CreateTable(&MyTable{})
删除表
// 删除模型`User`的表 db.DropTable(&User{}) // 删除表`users` db.DropTable("users") // 删除模型`User`的表和表`products` db.DropTableIfExists(&User{}, "products") //测试代码 //db.CreateTable(&Table1{}) table1 被创建 //db.CreateTable(&Table2{}) table2 被创建 //db.CreateTable(&Table3{}) table3 被创建 //db.DropTable(&MyTable{}) my_table 被删除 //db.DropTableIfExists(&Table1{}) table1 被删除 //db.DropTableIfExists(&Table2{},&Table4{}) table2 被删除 //db.CreateTable(&Table4{}) table4 被创建 //db.DropTableIfExists(&Table3{},&Table4{}) table3 和 table4 一起被删除
修改列
// 修改模型`User`的description列的数据类型为`text` db.Model(&User{}).ModifyColumn("description", "text")
删除列
// 删除模型`User`的description列 db.Model(&User{}).DropColumn("description")
添加外键
// 添加外键 // 1st param : 外键字段 // 2nd param : 外键表(字段) // 3rd param : ONDELETE // 4th param : ONUPDATE db.Model(&User{}).AddForeignKey("city_id", "cities(id)", "RESTRICT", "RESTRICT")
添加索引
// 为`name`列添加索引`idx_user_name` db.Model(&User{}).AddIndex("idx_user_name", "name") // 为`name`, `age`列添加索引`idx_user_name_age` db.Model(&User{}).AddIndex("idx_user_name_age", "name", "age") // 添加唯一索引 db.Model(&User{}).AddUniqueIndex("idx_user_name", "name") // 为多列添加唯一索引 db.Model(&User{}).AddUniqueIndex("idx_user_name_age", "name", "age") // 删除索引 db.Model(&User{}).RemoveIndex("idx_user_name")
模型定义 约定 gorm.Model结构体
// 基本模型的定义 type Model struct { ID uint `gorm:"primary_key"` CreatedAt time.Time UpdatedAt time.Time DeletedAt *time.Time } // 添加字段 `ID`, `CreatedAt`, `UpdatedAt`, `DeletedAt` type User struct { gorm.Model Name string } // 只需要字段 `ID`, `CreatedAt` type User struct { ID uint CreatedAt time.Time Name string }
表名是结构体名称的复数形式
type User struct {} // 默认表名是`users` // 设置User的表名为`profiles` func (User) TableName() string { return "profiles" } func (u User) TableName() string { if u.Role == "admin" { return "admin_users" } else { return "users" } } // 全局禁用表名复数 db.SingularTable(true) // 如果设置为true,`User`的默认表名为`user`,使用`TableName`设置的表名不受影响
更改默认表名
gorm.DefaultTableNameHandler = func (db *gorm.DB, defaultTableName string) string { return "prefix_" + defaultTableName; }
列名是字段名的蛇形小写
type User struct { ID uint // 列名为 `id` Name string // 列名为 `name` Birthday time.Time // 列名为 `birthday` CreatedAt time.Time // 列名为 `created_at` } // 重设列名 type Animal struct { AnimalId int64 `gorm:"column:beast_id"` // 设置列名为`beast_id` Birthday time.Time `gorm:"column:day_of_the_beast"` // 设置列名为`day_of_the_beast` Age int64 `gorm:"column:age_of_the_beast"` // 设置列名为`age_of_the_beast` }
字段ID为主键
type User struct { ID uint // 字段`ID`为默认主键 Name string } // 使用tag`primary_key`用来设置主键 type Animal struct { AnimalId int64 `gorm:"primary_key"` // 设置AnimalId为主键 Name string Age int64 }
字段CreatedAt用于存储记录的创建时间
db.Create(&user) // 将会设置`CreatedAt`为当前时间 // 要更改它的值, 你需要使用`Update` db.Model(&user).Update("CreatedAt", time.Now())
字段UpdatedAt用于存储记录的修改时间
db.Save(&user) // 将会设置`UpdatedAt`为当前时间 db.Model(&user).Update("name", "jinzhu") // 将会设置`UpdatedAt`为当前时间
字段DeletedAt用于存储记录的删除时间,如果字段存在
删除具有DeletedAt字段的记录,它不会冲数据库中删除,但只将字段DeletedAt设置为当前时间,并在查询时无法找到记录. 关联 属于
// `User`属于`Profile`, `ProfileID`为外键 type User struct { gorm.Model Profile Profile ProfileID int } type Profile struct { gorm.Model Name string } db.Model(&user).Related(&profile) //// SELECT * FROM profiles WHERE id = 111; // 111是user的外键ProfileID
指定外键
type Profile struct { gorm.Model Name string } type User struct { gorm.Model Profile Profile `gorm:"ForeignKey:ProfileRefer"` // 使用ProfileRefer作为外键 ProfileRefer int }
指定外键和关联外键
type Profile struct { gorm.Model Refer string Name string } type User struct { gorm.Model Profile Profile `gorm:"ForeignKey:ProfileID;AssociationForeignKey:Refer"` ProfileID int }
包含一个
// User 包含一个 CreditCard, UserID 为外键 type User struct { gorm.Model CreditCard CreditCard } type CreditCard struct { gorm.Model UserID uint Number string } var card CreditCard db.Model(&user).Related(&card, "CreditCard") //// SELECT * FROM credit_cards WHERE user_id = 123; // 123 is user's primary key // CreditCard是user的字段名称,这意味着获得user的CreditCard关系并将其填充到变量 // 如果字段名与变量的类型名相同,如上例所示,可以省略,如: db.Model(&user).Related(&card)
包含多个
// User 包含多个 emails, UserID 为外键 type User struct { gorm.Model Emails []Email } type Email struct { gorm.Model Email string UserID uint } db.Model(&user).Related(&emails) //// SELECT * FROM emails WHERE user_id = 111; // 111 是 user 的主键
多对多
// User 包含并属于多个 languages, 使用 `user_languages` 表连接 type User struct { gorm.Model Languages []Language `gorm:"many2many:user_languages;"` } type Language struct { gorm.Model Name string } db.Model(&user).Related(&languages, "Languages") //// SELECT * FROM "languages" INNER JOIN "user_languages" ON "user_languages"."language_id" = "languages"."id" WHERE "user_languages"."user_id" = 111
多种包含
支持多种的包含一个和包含多个的关联
type Cat struct { Id int Name string Toy Toy `gorm:"polymorphic:Owner;"` } type Dog struct { Id int Name string Toy Toy `gorm:"polymorphic:Owner;"` } type Toy struct { Id int Name string OwnerId int OwnerType string }
关联模式
// 开始关联模式 var user User db.Model(&user).Association("Languages") // `user`是源,它需要是一个有效的记录(包含主键) // `Languages`是关系中源的字段名。 // 如果这些条件不匹配,将返回一个错误,检查它: // db.Model(&user).Association("Languages").Error // Query - 查找所有相关关联 db.Model(&user).Association("Languages").Find(&languages) // Append - 添加新的many2many, has_many关联, 会替换掉当前 has_one, belongs_to关联 db.Model(&user).Association("Languages").Append([]Language{languageZH, languageEN}) db.Model(&user).Association("Languages").Append(Language{Name: "DE"}) // Delete - 删除源和传递的参数之间的关系,不会删除这些参数 db.Model(&user).Association("Languages").Delete([]Language{languageZH, languageEN}) db.Model(&user).Association("Languages").Delete(languageZH, languageEN) // Replace - 使用新的关联替换当前关联 db.Model(&user).Association("Languages").Replace([]Language{languageZH, languageEN}) db.Model(&user).Association("Languages").Replace(Language{Name: "DE"}, languageEN) // Count - 返回当前关联的计数 db.Model(&user).Association("Languages").Count() // Clear - 删除源和当前关联之间的关系,不会删除这些关联 db.Model(&user).Association("Languages").Clear()
读写数据 创建 创建数据
user := User{Name: "Jinzhu", Age: 18, Birthday: time.Now()} db.NewRecord(user) // => 主键为空返回`true` db.Create(&user) db.NewRecord(user) // => 创建`user`后返回`false` // NewRecord只检查主键,不创建记录
默认值
type Animal struct { ID int64 Name string `gorm:"default:'galeone'"` Age int64 } var animal = Animal{Age: 99, Name: ""} db.Create(&animal) // INSERT INTO animals("age") values('99'); // SELECT name from animals WHERE ID=111; // 返回主键为 111 // animal.Name => 'galeone'
在Callbacks中设置主键
func (user *User) BeforeCreate(scope *gorm.Scope) error { scope.SetColumn("ID", uuid.New()) return nil }
扩展创建选项
// 为Instert语句添加扩展SQL选项 db.Set("gorm:insert_option", "ON CONFLICT").Create(&product) // INSERT INTO products (name, code) VALUES ("name", "code") ON CONFLICT;
查询
// 获取第一条记录,按主键排序 db.First(&user) //// SELECT * FROM users ORDER BY id LIMIT 1; // 获取最后一条记录,按主键排序 db.Last(&user) //// SELECT * FROM users ORDER BY id DESC LIMIT 1; // 获取所有记录 db.Find(&users) //// SELECT * FROM users; // 使用主键获取记录 db.First(&user, 10) //// SELECT * FROM users WHERE id = 10;
Where条件查询(简单SQL)
注意:当使用struct查询时,GORM将只查询那些具有值的字段
// Struct db.Where(&User{Name: "jinzhu", Age: 20}).First(&user) //// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1; // Map db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users) //// SELECT * FROM users WHERE name = "jinzhu" AND age = 20; // 主键的Slice db.Where([]int64{20, 21, 22}).Find(&users) //// SELECT * FROM users WHERE id IN (20, 21, 22);
Not条件查询
db.Not("name", "jinzhu").First(&user) //// SELECT * FROM users WHERE name <> "jinzhu" LIMIT 1; // Not In db.Not("name", []string{"jinzhu", "jinzhu 2"}).Find(&users) //// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2"); // Not In slice of primary keys db.Not([]int64{1,2,3}).First(&user) //// SELECT * FROM users WHERE id NOT IN (1,2,3); db.Not([]int64{}).First(&user) //// SELECT * FROM users; // Plain SQL db.Not("name = ?", "jinzhu").First(&user) //// SELECT * FROM users WHERE NOT(name = "jinzhu"); // Struct db.Not(User{Name: "jinzhu"}).First(&user) //// SELECT * FROM users WHERE name <> "jinzhu";
带内联条件的查询
注意:使用主键查询时,应仔细检查所传递的值是否为有效主键,以避免SQL注入
// 按主键获取 db.First(&user, 23) //// SELECT * FROM users WHERE id = 23 LIMIT 1; // 简单SQL db.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; // Struct db.Find(&users, User{Age: 20}) //// SELECT * FROM users WHERE age = 20; // Map db.Find(&users, map[string]interface{}{"age": 20}) //// SELECT * FROM users WHERE age = 20;
Or条件查询
db.Where("role = ?", "admin").Or("role = ?", "super_admin").Find(&users) //// SELECT * FROM users WHERE role = 'admin' OR role = 'super_admin'; // Struct db.Where("name = 'jinzhu'").Or(User{Name: "jinzhu 2"}).Find(&users) //// SELECT * FROM users WHERE name = 'jinzhu' OR name = 'jinzhu 2'; // Map db.Where("name = 'jinzhu'").Or(map[string]interface{}{"name": "jinzhu 2"}).Find(&users)
查询链
Gorm有一个可链接的API,你可以这样使用它
db.Where("name <> ?","jinzhu").Where("age >= ? and role <> ?",20,"admin").Find(&users) //// SELECT * FROM users WHERE name <> 'jinzhu' AND age >= 20 AND role <> 'admin'; db.Where("role = ?", "admin").Or("role = ?", "super_admin").Not("name = ?", "jinzhu").Find(&users)
扩展查询选项
// 为Select语句添加扩展SQL选项 db.Set("gorm:query_option", "FOR UPDATE").First(&user, 10) //// SELECT * FROM users WHERE id = 10 FOR UPDATE;
FirstOrInit
获取第一个匹配的记录,或者使用给定的条件初始化一个新的记录(仅适用于struct,map条件)
// Unfound db.FirstOrInit(&user, User{Name: "non_existing"}) //// user -> User{Name: "non_existing"} // Found db.Where(User{Name: "Jinzhu"}).FirstOrInit(&user) //// user -> User{Id: 111, Name: "Jinzhu", Age: 20} db.FirstOrInit(&user, map[string]interface{}{"name": "jinzhu"}) //// user -> User{Id: 111, Name: "Jinzhu", Age: 20}
Attrs
如果未找到记录,则使用参数初始化结构
// Unfound db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = 'non_existing'; //// user -> User{Name: "non_existing", Age: 20} db.Where(User{Name: "non_existing"}).Attrs("age", 20).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = 'non_existing'; //// user -> User{Name: "non_existing", Age: 20} // Found db.Where(User{Name: "Jinzhu"}).Attrs(User{Age: 30}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = jinzhu'; //// user -> User{Id: 111, Name: "Jinzhu", Age: 20}
Assign
将参数分配给结果,不管它是否被找到
// Unfound db.Where(User{Name: "non_existing"}).Assign(User{Age: 20}).FirstOrInit(&user) //// user -> User{Name: "non_existing", Age: 20} // Found db.Where(User{Name: "Jinzhu"}).Assign(User{Age: 30}).FirstOrInit(&user) //// SELECT * FROM USERS WHERE name = jinzhu'; //// user -> User{Id: 111, Name: "Jinzhu", Age: 30}
FirstOrCreate
获取第一个匹配的记录,或创建一个具有给定条件的新记录(仅适用于struct, map条件)
// Unfound db.FirstOrCreate(&user, User{Name: "non_existing"}) //// INSERT INTO "users" (name) VALUES ("non_existing"); //// user -> User{Id: 112, Name: "non_existing"} // Found db.Where(User{Name: "Jinzhu"}).FirstOrCreate(&user) //// user -> User{Id: 111, Name: "Jinzhu"} // 如果未找到记录,则为参数分配结构 // Unfound db.Where(User{Name: "non_existing"}).Attrs(User{Age: 20}).FirstOrCreate(&user) //// SELECT * FROM users WHERE name = 'non_existing'; //// INSERT INTO "users" (name, age) VALUES ("non_existing", 20); //// user -> User{Id: 112, Name: "non_existing", Age: 20} // Found db.Where(User{Name: "jinzhu"}).Attrs(User{Age: 30}).FirstOrCreate(&a
谢谢!学习了!