GORM框架

GORM是ORM的一种流行框架，那什么是ORM呢？

ORM是通过使用描述对象和数据库之间映射的元数据，将程序中的对象与关系数据库相互映射换言之，ORM就是将数据从数据库中的表结构转换成代码中可以使用的数据结构

连接数据库

安装MySQL驱动

$ go get -u github.com/go-sql-driver/mysql

数据源名称

[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]
dsn := "root:pluto@tcp(192.168.1.10:3306)/golang?charset=utf8mb4&parseTime=True&loc=Local"

包含了你要连接数据库的信息，一些参数制定了此次连接的标准

简单连接

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: "gorm:gorm@tcp(127.0.0.1:3306)/gorm?charset=utf8&parseTime=True&loc=Local", // DSN data source name
  DefaultStringSize: 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{})

这样可以设置更多的参数，便于更加复杂的运用场景

连接数据库遇到的一些问题

报错failed to initialize database, got error Error 1130: Host 'LAPTOP-PNM85NLV' is not allowed to connect to this MySQL server

这是因为MySQL没有设置允许远程连接导致的

打开cmd，登录MySQL（如果遇到无法识别MySQL命令，则是因为没有在系统环境变量中加入MySQL的安装路径） mysql -u name -p password

切换到mysql数据库 use mysql

查看用户表SELECT Host,User FROM user;

更新用户表UPDATE user SET Host = '%' WHERE User = 'root' LIMIT 1; %表示既可以本地访问也可以远程访问

刷新权限flush privileges;

GORM中的高级配置

db, err := gorm.Open(mysql.New(mysql.Config{
        DSN:               "root:pluto@tcp(192.168.1.10:3306)/golang?charset=utf8mb4&parseTime=True&loc=Local", // data source name
        DefaultStringSize: 171,
    }), &gorm.Config{
        SkipDefaultTransaction: false, //default single transaction
        NamingStrategy: schema.NamingStrategy{
            TablePrefix:   "p_", //table name prefix, table for `user` would be `p_users`
            SingularTable: true, // use singular table name, table for `User` would be `user` with this option enabled
        },
        DisableForeignKeyConstraintWhenMigrating: true, //Automatically establish foreign key constraints
    })

golang中对database的一些基本操作

package main

import (
    "fmt"

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

func main() {

    db, _ := gorm.Open(mysql.New(mysql.Config{
        DSN:               "root:pluto@tcp(192.168.1.10:3306)/golang?charset=utf8mb4&parseTime=True&loc=Local", // data source name
        DefaultStringSize: 171,
    }), &gorm.Config{
        SkipDefaultTransaction: false, //default single transaction
        NamingStrategy: schema.NamingStrategy{
            TablePrefix:   "p_", //table name prefix, table for `user` would be `p_users`
            SingularTable: true, // use singular table name, table for `User` would be `user` with this option enabled
        },
        DisableForeignKeyConstraintWhenMigrating: true, //Automatically establish foreign key constraints
    })
    // struct in golang map to table in database
    type User struct {
        Name string
    }
    M := db.Migrator()
    // create table
    fmt.Println(M.CreateTable(&User{}))
    // if table is exist
    fmt.Println(M.HasTable(&User{}))
    // delete table
    fmt.Println(M.DropTable(&User{}))
    //change table name
    // this way has a question:in golang hasn't a struct to operator the rename table User_new
    M.RenameTable(&User{}, "User_new")
}

更多详细的操作参考gorm官方文档

gorm.io/

连接池

建立和关闭与数据库之间的连接都是一个很耗时的过程，所以在创建的一开始就建立一个连接池里面有很多可用的连接，避免了因创建和关闭连接而造成的大量时间开销

// 获取通用数据库对象 sql.DB ，然后使用其提供的功能
    sqlDB, _ := db.DB()

    // SetMaxIdleConns 用于设置连接池中空闲连接的最大数量。
    sqlDB.SetMaxIdleConns(10)

    // SetMaxOpenConns 设置打开数据库连接的最大数量。
    sqlDB.SetMaxOpenConns(100)

    // SetConnMaxLifetime 设置了连接可复用的最大时间。
    sqlDB.SetConnMaxLifetime(time.Hour)

模型

模型是标准的 struct，由 Go 的基本数据类型、实现了 Scanner 和 Valuer 接口的自定义类型及其指针或别名组成

type testuser struct {
    ID           uint
    Name         string
    Email        *string
    Age          uint8
    Birthday     *time.Time
    MemberNumber sql.NullString
    ActivatedAt  sql.NullTime
    CreatedAt    time.Time
    UpdatedAt    time.Time
}

func testUserCreate() {
    GLOBAL_DB.AutoMigrate(&testuser{})
}

mysql> describe p_testuser;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| id            | bigint unsigned  | NO   | PRI | NULL    | auto_increment |
| name          | varchar(171)     | YES  |     | NULL    |                |
| email         | varchar(171)     | YES  |     | NULL    |                |
| age           | tinyint unsigned | YES  |     | NULL    |                |
| birthday      | datetime(3)      | YES  |     | NULL    |                |
| member_number | varchar(171)     | YES  |     | NULL    |                |
| activated_at  | datetime(3)      | YES  |     | NULL    |                |
| created_at    | datetime(3)      | YES  |     | NULL    |                |
| updated_at    | datetime(3)      | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

嵌入结构体（创建表）

对于匿名字段，GORM 会将其字段包含在父结构体中，例如：

type User struct {
  gorm.Model
  Name string
}
// 等效于
type User struct {
  ID        uint           `gorm:"primaryKey"`
  CreatedAt time.Time
  UpdatedAt time.Time
  DeletedAt gorm.DeletedAt `gorm:"index"`
  Name string
}

对于正常的结构体字段，你也可以通过标签 embedded 将其嵌入，例如：

type Author struct {
    Name  string
    Email string
}

type Blog struct {
  ID      int
  Author  Author `gorm:"embedded"`
  Upvotes int32
}
// 等效于
type Blog struct {
  ID    int64
  Name  string
  Email string
  Upvotes  int32
}

字段标签

type Model struct {
    MyId uint      `gorm:"primarykey"`    // set as primary key
    Time time.Time `gorm:"column:myTime"` //named as myTime in table
}
type user struct {
    Model        Model   `gorm:"embedded;embeddedPrifix:My"` //通过标签 `embedded` 将其嵌入 set prifixname:My
    Name         string  `gorm:"default:pluto"`              //set default value
    Email        *string `gorm:"not null"`                   // not null
    Age          uint8   `gorm:"comment:年龄"`                 //some comments
    Birthday     *time.Time
    MemberNumber sql.NullString
    ActivatedAt  sql.NullTime
}

func testUserCreate() {
    GLOBAL_DB.AutoMigrate(&user{})
}

mysql> describe users;
+---------------+------------------+------+-----+---------+----------------+
| Field         | Type             | Null | Key | Default | Extra          |
+---------------+------------------+------+-----+---------+----------------+
| my_id         | bigint unsigned  | NO   | PRI | NULL    | auto_increment |
| myTime        | datetime(3)      | YES  |     | NULL    |                |
| name          | varchar(171)     | YES  |     | pluto   |                |
| email         | varchar(171)     | NO   |     | NULL    |                |
| age           | tinyint unsigned | YES  |     | NULL    |                |
| birthday      | datetime(3)      | YES  |     | NULL    |                |
| member_number | varchar(171)     | YES  |     | NULL    |                |
| activated_at  | datetime(3)      | YES  |     | NULL    |                |
+---------------+------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

简单的增删改查

插入数据

func CreateTest() int64 {
    //Returns a pointer of type DB after execution is complete
    //if you didn't accept the return result,can't found out if there is some errors or not
    dbresult := GLOBAL_DB.Create(&user{Name: "pluto", Age: 20})
    /*type DB struct {
        *Config
        Error        error
        RowsAffected int64 //number of rows affected
        Statement    *Statement
        clone        int
    }*/
    if dbresult.Error != nil {
        return -1
    } else {
        return dbresult.RowsAffected
    }
}

当结构体中有成员变量没有被赋值，那么会赋予默认值。但是在插入数据到数据库时我们希望它是为空的

select

指定只想创建的列

func CreateTest() int64 {
    //Returns a pointer of type DB after execution is complete
    //if you didn't accept the return result,can't found out if there is some errors or not
    dbresult := GLOBAL_DB.Select("Name").Create(&user{Name: "pluto"}) //just set value for Name
    /*type DB struct {
        *Config
        Error        error
        RowsAffected int64 //number of rows affected
        Statement    *Statement
        clone        int
    }*/
    if dbresult.Error != nil {
        fmt.Println("having a error")
        return -1
    } else {
        fmt.Println(dbresult.RowsAffected)
        return dbresult.RowsAffected
    }
    //
}

+-------+--------+-------+------+
| my_id | myTime | name  | age  |
+-------+--------+-------+------+
|     6 | NULL   | pluto | NULL |
+-------+--------+-------+------+
1 row in set (0.00 sec)

Omit

指定不想创建的列

func CreateTest() int64 {
    //Returns a pointer of type DB after execution is complete
    //if you didn't accept the return result,can't found out if there is some errors or not
    dbresult := GLOBAL_DB.Omit("Age").Create(&user{Name: "pluto", Age: 18}) //set a column except Age
    /*type DB struct {
        *Config
        Error        error
        RowsAffected int64 //number of rows affected
        Statement    *Statement
        clone        int
    }*/
    if dbresult.Error != nil {
        fmt.Println("having a error")
        return -1
    } else {
        fmt.Println(dbresult.RowsAffected)
        return dbresult.RowsAffected
    }
}

+-------+-------------------------+-------+------+
| my_id | myTime                  | name  | age  |
+-------+-------------------------+-------+------+
|     8 | 0000-00-00 00:00:00.000 | pluto | NULL |
+-------+-------------------------+-------+------+
1 row in set (0.00 sec)

批量插入

利用切片进行批量插入

var users = []User{{Name: "jinzhu1"}, {Name: "jinzhu2"}, {Name: "jinzhu3"}}
db.Create(&users)

查询数据

单个查询

// 获取第一条记录（主键升序）
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;

主键查询

package main

import (
    "errors"
    "fmt"

    "gorm.io/gorm"
)

func Testselect() {
    var result = make(map[string]interface{})              //string as key interface ar value
    dbResult := GLOBAL_DB.Model(&user{}).First(&result, 3) //the second parameter is primary key
    //
    fmt.Println(errors.Is(dbResult.Error, gorm.ErrRecordNotFound))
    fmt.Println(result)
}

带where条件

where内为字符串型

func Testselect() {
    var _user user
    dbResult := GLOBAL_DB.Where("name=? and age=?", "cy", 20).First(&_user)
    if dbResult.Error != nil {
        fmt.Println(dbResult.Error)
    } else {
        fmt.Println(_user)
    }
}

where内为map

func Testselect() {
    var _user user
    dbResult := GLOBAL_DB.Where(map[string]interface{}{
        "name": "cy",
        "age":  20,
    }).First(&_user)
    if dbResult.Error != nil {
        fmt.Println(dbResult.Error)
    } else {
        fmt.Println(_user)
    }
}

where内为struct类型

func Testselect() {
    var _user user
    dbResult := GLOBAL_DB.Where(user{
        Model: Model{},
        Name:  "",
        Age:   0,
    }).First(&_user)
    if dbResult.Error != nil {
        fmt.Println(dbResult.Error)
    } else {
        fmt.Println(_user)
    }
}

where条件内抽离关键字

Not 条件

Build NOT conditions, works similar to Where

db.Not("name = ?", "jinzhu").First(&user)
// SELECT * FROM users WHERE NOT name = "jinzhu" ORDER BY id LIMIT 1;

// Not In
db.Not(map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}).Find(&users)
// SELECT * FROM users WHERE name NOT IN ("jinzhu", "jinzhu 2");

// Struct
db.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 keys
db.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';

// Struct
db.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);

// Map
db.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);

For more complicated SQL queries. please also refer to Group Conditions in Advanced Query.

选择特定字段

Select allows you to specify the fields that you want to retrieve from database. Otherwise, GORM will select all fields by default.

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;
//COALESCE means return thr first not null value

Also check out Smart Select Fields

Order

Specify order when retrieving records from the database

db.Order("age desc, name").Find(&users)
// SELECT * FROM users ORDER BY age desc, name;

// Multiple orders
db.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

Limit specify the max number of records to retrieve Offset specify the number of records to skip before starting to return the records

db.Limit(3).Find(&users)
// SELECT * FROM users LIMIT 3;

// Cancel limit condition with -1
db.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 -1
db.Offset(10).Find(&users1).Offset(-1).Find(&users2)
// SELECT * FROM users OFFSET 10; (users1)
// SELECT * FROM users; (users2)

Refer to Pagination for details on how to make a paginator

Group By & Having

type result struct {
  Date  time.Time
  Total int
}

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 1


db.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"

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Rows()
defer rows.Close()
for rows.Next() {
  ...
}

rows, err := db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Rows()
defer rows.Close()
for rows.Next() {
  ...
}

type Result struct {
  Date  time.Time
  Total int64
}
db.Table("orders").Select("date(created_at) as date, sum(amount) as total").Group("date(created_at)").Having("sum(amount) > ?", 100).Scan(&results)

Distinct

distinct:remove duplicate rows

Selecting distinct values from the model

db.Distinct("name", "age").Order("name, age desc").Find(&results)

Distinct works with Pluck and Count too

高级查询

智能选择字段

单个表查询时我们有时只会查找个别字段，所以可以通过model告诉GORM要查询的是哪张表，再构造一个struct去存放需要查找的个别字段

func Testselect() {
	var _user []userInfo
	// just want to select name and age so make a struct to store just name and age
	// but using userInfo gorm can't known which table you want to select
	// this is the role of Model
	dbResult := GLOBAL_DB.Model(&user{}).Where("name=?", "pluto").Find(&_user)
	if dbResult.Error != nil {
		fmt.Println(dbResult.Error)
	} else {
		for _, temp := range _user {
			fmt.Println(temp)
		}
	}
}

输出

{pluto 20}
{pluto 20}
{pluto 0}
{pluto 0}
{pluto 0}
{pluto 0}
{pluto 18}
{pluto 0}

更新数据

update只更新你选择的字段
updates 更新所有字段此时有两种形式一种为Map 一种为结构体结构体零值不参与更新
save 无论如何都更新所有内容包括零值

save更新

func TestUpdate() {
    //save through primaryKey to update
    //if the primaryKey didn't exist in table it will create a limit
    //the comment is what we want to update
    GLOBAL_DB.Where("name=?", "curry").Save(&user{Name: "steph", Age: 34})
}

save更新的改进

func TestUpdate() {
    var users []user
    //First find out the datas you want to update
    dbResult := GLOBAL_DB.Where("name=?", "pluto").Find(&users)
    //revise the datas
    for k := range users {
        users[k].Age = 20
    }
    // store the new datas it means update
    dbResult.Save(&users)
}

更新单个列 update

当使用 Update 更新单个列时，你需要指定条件，否则会返回 ErrMissingWhereClause 错误，查看 Block Global Updates 获取详情。当使用了 Model 方法，且该对象主键有值，该值会被用于构建条件，例如：

// 条件更新
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;

更新多个列

Updates 方法支持 struct 和 map[string]interface{} 参数。当使用 struct 更新时，默认情况下，GORM 只会更新非零值的字段

// 根据 `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;

更新选择字段

如果您想要在更新时选定、忽略某些字段，您可以使用 Select、Omit

// 使用 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})

删除数据

删除一条数据

删除一条记录时，删除对象需要指定主键，否则会触发批量 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";

ORM 允许通过主键(可以是复合主键)和内联条件来删除对象，它可以使用数字（如以下例子。也可以使用字符串——译者注）。查看查询-内联条件（Query Inline Conditions）了解详情。

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);

原生SQL

package main

import "fmt"

type Result struct {
    Name string
    Age  int
}

func Testdelete() {
    var result Result
    GLOBAL_DB.Raw("select age,name from users where name=?", "steph").Scan(&result)
    fmt.Println(result)
}

关系

一对一关系

结构体中包含的其他结构体(非数组)是标识：该结构体和此结构体是一对一关系，默认结构名 +ID为外键，创建表时其他结构体不会作为表中属性

何为belong to 和has one

属于： 我身上有你的标签，我是属于你的。但你并不知道我。类似于暗恋

拥有一个：该关系更强，我身上有你的标签，我是属于你的，你也知道我。你只知道我的标签。类似于已经表白

belong to

package main

import "gorm.io/gorm"

//belong to
//goddess belongs to dog 
type Dog struct {
    gorm.Model
    Name      string
    GirlGodID uint//foreign key as GirlGod's ID
    GirlGod   GirlGod
}

type GirlGod struct {
    gorm.Model
    Name string
}

func One2one() {
    g := Dog{
        Name: "十三",
        GirlGod: GirlGod{
            Name: "小七",
        },
    }
    //create  Dog table 
    GLOBAL_DB.AutoMigrate(&Dog{})//because GirlGod belongs to Dog ,so create Dog table the same time will create GirlGod table
    GLOBAL_DB.Create(&g)
}

mysql>  select * from dogs;
+----+-------------------------+-------------------------+------------+------+-------------+
| id | created_at              | updated_at              | deleted_at | name | girl_god_id |
+----+-------------------------+-------------------------+------------+------+-------------+
|  1 | 2022-08-01 22:21:11.798 | 2022-08-01 22:21:11.798 | NULL       | 十三 |           1 |
+----+-------------------------+-------------------------+------------+------+-------------+
1 row in set (0.00 sec)

mysql>  select * from girl_gods;
+----+-------------------------+-------------------------+------------+------+
| id | created_at              | updated_at              | deleted_at | name |
+----+-------------------------+-------------------------+------------+------+
|  1 | 2022-08-01 22:21:11.767 | 2022-08-01 22:21:11.767 | NULL       | 小七 |
+----+-------------------------+-------------------------+------------+------+
1 row in set (0.00 sec)

has one

package main

import "gorm.io/gorm"

//has one
type Dog struct {
    gorm.Model
    Name      string
    GirlGodID uint
}

type GirlGod struct {
    gorm.Model
    Name string
    Dog  Dog //has one
}

func One2one() {
    d := Dog{
        Name: "十四",
    }
    g := GirlGod{
        Name: "小七",
        Dog:  d,
    }

    GLOBAL_DB.Create(&g)
    //GLOBAL_DB.AutoMigrate(&GirlGod{}, &Dog{})
}

查找

预加载： 与该表有关联的其他表数据，可以通过预加载的方式带出来

func One2one() {
    var girl GirlGod
    GLOBAL_DB.Preload("Dog").First(&girl, 2)//预加载
    fmt.Println("this is :", girl)
}

等价于

select * from dogs,girl_gods where girl_gods.id=dogs.girl_god_id;

建立联系

未建立关系之前

+----+-------------------------+-------------------------+------------+------+-------------+
| id | created_at              | updated_at              | deleted_at | name | girl_god_id |
+----+-------------------------+-------------------------+------------+------+-------------+
|  1 | 2022-08-01 22:21:11.798 | 2022-08-01 22:21:11.798 | NULL       | 十三 |           0 |
+----+-------------------------+-------------------------+------------+------+-------------+

建立关系

func One2one() {
    d := Dog{
        Model: gorm.Model{
            ID: 1,
        },
    }
    g := GirlGod{
        Model: gorm.Model{
            ID: 1,
        },
    }
    // point out which model:Dog
    // do what:Association point out build relationships with GirlGod
    // Append point out which colunm :g
    GLOBAL_DB.Model(&d).Association("GirlGod").Append(&g)
}

建立关系之后

+----+-------------------------+-------------------------+------------+------+-------------+
| id | created_at              | updated_at              | deleted_at | name | girl_god_id |
+----+-------------------------+-------------------------+------------+------+-------------+
|  1 | 2022-08-01 22:21:11.798 | 2022-08-02 11:03:10.775 | NULL       | 十三 |           1 |
+----+-------------------------+-------------------------+------------+------+-------------+

取消该关系

func One2one() {
    d := Dog{
        Model: gorm.Model{
            ID: 1,
        },
    }
    g := GirlGod{
        Model: gorm.Model{
            ID: 1,
        },
    }
    // point out which model:Dog
    // do what:Association point out cancel the relationship with GirlGod
    // Append point out which colunm :g
    GLOBAL_DB.Model(&d).Association("GirlGod").Delete(&g)
}

取消关系后

+----+-------------------------+-------------------------+------------+------+-------------+
| id | created_at              | updated_at              | deleted_at | name | girl_god_id |
+----+-------------------------+-------------------------+------------+------+-------------+
|  1 | 2022-08-01 22:21:11.798 | 2022-08-02 11:03:10.775 | NULL       | 十三 |        NULL |
+----+-------------------------+-------------------------+------------+------+-------------+

关系操作

func One2one() {
    d := Dog{
        Model: gorm.Model{
            ID: 1,
        },
    }
    g := GirlGod{
        Model: gorm.Model{
            ID: 1,
        },
    }
    d1 := Dog{
        Model: gorm.Model{
            ID: 2,
        },
    }
    //build relationship
    GLOBAL_DB.Model(&d).Association("GirlGod").Append(&g)
    //cancel contact with GirlGod
    GLOBAL_DB.Model(&d).Association("GirlGod").Delete(&g)
    //replace relationship
    GLOBAL_DB.Model(&d).Association("GirlGod").Replace(&d1)
    //clear relationship
    GLOBAL_DB.Model(&d).Association("GirlGod").Clear()
}

一对多

has many

创建

package main

import (
    "gorm.io/gorm"
)

//has many
type Dog struct {
    gorm.Model
    Name      string
    GirlGodID uint //foreign key as GirlGod's ID
}

type GirlGod struct {
    gorm.Model
    Name string
    Dogs []Dog
}

func One2one() {
    d1 := Dog{
        Model: gorm.Model{
            ID: 1,
        },
        Name: "十三",
    }
    d2 := Dog{
        Model: gorm.Model{
            ID: 2,
        },
        Name: "小十七",
    }
    g := GirlGod{
        Model: gorm.Model{
            ID: 1,
        },
        Name: "十七",
        Dogs: []Dog{d1, d2}, //has many
    }
    GLOBAL_DB.Create(&g)
    GLOBAL_DB.AutoMigrate(&Dog{}, &GirlGod{})
}

mysql>  select * from dogs;
+----+-------------------------+-------------------------+------------+--------+-------------+
| id | created_at              | updated_at              | deleted_at | name   | girl_god_id |
+----+-------------------------+-------------------------+------------+--------+-------------+
|  1 | 2022-08-02 21:05:33.953 | 2022-08-02 21:05:33.953 | NULL       | 十三   |           1 |
|  2 | 2022-08-02 21:05:33.953 | 2022-08-02 21:05:33.953 | NULL       | 小十七 |           1 |
+----+-------------------------+-------------------------+------------+--------+-------------+
2 rows in set (0.00 sec)

mysql>  select * from girl_gods;
+----+-------------------------+-------------------------+------------+------+
| id | created_at              | updated_at              | deleted_at | name |
+----+-------------------------+-------------------------+------------+------+
|  1 | 2022-08-02 21:05:33.922 | 2022-08-02 21:05:33.922 | NULL       | 十七 |
+----+-------------------------+-------------------------+------------+------+
1 row in set (0.00 sec)

查询

func One2one() {
    var girl GirlGod
    // not preload
    GLOBAL_DB.First(&girl)
    fmt.Println(girl)//just the girl's data
    //preload
    GLOBAL_DB.Preload("Dogs").First(&girl)// the girl's data and all of her dogs
    fmt.Println(girl)
    //conditional preload
    GLOBAL_DB.Preload("Dogs", "name=?", "小十七").First(&girl)//the girl's data and dogs'data which she want to bring out
    fmt.Println(girl)
}

复杂条件的查询

func One2one() {
    var girl GirlGod

    //conditional preload
    //customize SQL form
    GLOBAL_DB.Preload("Dogs",
        // this function can accomplish complex SQL statements
        func(db *gorm.DB) *gorm.DB {
            return db.Where("name=?", "小十七")
        }).First(&girl) //the girl's data and dogs'data which she want to bring out
    fmt.Println(girl)
}

多重预加载

通过preload将与之关联的表带出来，要注意用法。详细见代码注释

package main

import (
    "fmt"

    "gorm.io/gorm"
)

//belong to
type MoneyInfo struct {
    gorm.Model
    Money int
    DogID uint //this money belong to which dog
}

//has many
type Dog struct {
    gorm.Model
    Name      string
    GirlGodID uint      //foreign key as GirlGod's ID
    MoneyInfo MoneyInfo //
}

type GirlGod struct {
    gorm.Model
    Name string
    Dogs []Dog
}

func One2one() {
    GLOBAL_DB.AutoMigrate(&GirlGod{}, &Dog{}, &MoneyInfo{})
    var girl GirlGod
    //Dogs must be the table name in mysql
    //MoneyInfo must be the struct name in golang
    //otherwise not run
    GLOBAL_DB.Preload("Dogs.MoneyInfo").First(&girl)
    fmt.Println(girl)
}

带条件的多重预加载

func One2one() {
    GLOBAL_DB.AutoMigrate(&GirlGod{}, &Dog{}, &MoneyInfo{})
    var girl GirlGod
    //Dogs must be the table name in mysql
    //MoneyInfo must be the struct name in golang
    //otherwise not run
    GLOBAL_DB.Preload("Dogs.MoneyInfo", "money>3000").Preload("Dogs", "name=?", "小十七").First(&girl)
    fmt.Println(girl)
}

如果没有 Preload("Dogs", "name=?", "小十七")默认将该girl所关联的所有Dogs中的信息全部带出，Preload("Dogs.MoneyInfo", "money>3000")这一重Preload则是对Dogs带出来的信息进行筛选

只能在每一重预加载中对其信息进行筛选，不能将多重信息一同筛选

查询条件只适用于当前预加载的这一层，不满足该层条件，不影响其他符合条件数据的带出。如：Dogs.MoneyInfo不符合条件，仅仅是不将MoneyInfo的信息带出，Dogs中的信息仍然被带出

join(提前连表)

Join Preload适用于一对一的关系，例如：has one,belongs to

func One2one() {
    GLOBAL_DB.AutoMigrate(&GirlGod{}, &Dog{}, &MoneyInfo{})
    var girl GirlGod
    //Dogs must be the table name in mysql
    //MoneyInfo must be the struct name in golang
    //otherwise not run
    GLOBAL_DB.Preload("Dogs", func (db*gorm.DB)*gorm.DB{
        return db.Joins("MoneyInfo").Where("money>2000")
    }).First(&girl)
    fmt.Println(girl)
}

多对多关系

创建

package main

import (
    "gorm.io/gorm"
)

//belong to
type MoneyInfo struct {
    gorm.Model
    Money int
    DogID uint //this money belong to which dog
}

//has many
type Dog struct {
    gorm.Model
    Name      string
    MoneyInfo MoneyInfo
    GirlGod   []GirlGod `gorm:"many2many:girl_god_dog"`
}

type GirlGod struct {
    gorm.Model
    Name string
    Dogs []Dog `gorm:"many2many:girl_god_dog"`
}

func One2one() {
    m1 := MoneyInfo{
        Model: gorm.Model{
            ID: 1,
        },
        Money: 8000,
    }
    m2 := MoneyInfo{
        Model: gorm.Model{
            ID: 2,
        },
        Money: 10000,
    }
    g1 := GirlGod{
        Model: gorm.Model{
            ID: 1,
        },
        Name: "小七",
    }
    g2 := GirlGod{
        Model: gorm.Model{
            ID: 2,
        },
        Name: "十九",
    }
    d1 := Dog{
        Model: gorm.Model{
            ID: 1,
        },
        Name:      "十三",
        MoneyInfo: m1,
        GirlGod:   []GirlGod{g1, g2},
    }
    d2 := Dog{
        Model: gorm.Model{
            ID: 2,
        },
        Name:      "小十七",
        MoneyInfo: m2,
        GirlGod:   []GirlGod{g1, g2},
    }
    GLOBAL_DB.Create(&d1)
    GLOBAL_DB.Create(&d2)
}

查询

当A与B是有关系，可以通过A找到B，但如果我只想要B的数据，怎么办？

func One2one() {
    d := Dog{
        Model: gorm.Model{
            ID: 1,
        },
    }
    var girls []GirlGod
    GLOBAL_DB.Model(&d).Association("GirlGod").Find(&girls)//just throuh dogs find out girlgod 
    fmt.Println(girls)
}

综合查询

package main

import (
    "fmt"

    "gorm.io/gorm"
)

//belong to
type MoneyInfo struct {
    gorm.Model
    Money int
    DogID uint //this money belong to which dog
}

//has many
type Dog struct {
    gorm.Model
    Name      string
    MoneyInfo MoneyInfo
    GirlGod   []GirlGod `gorm:"many2many:girl_god_dog"`
}

type GirlGod struct {
    gorm.Model
    Name string
    Dogs []Dog `gorm:"many2many:girl_god_dog"`
}

func One2one() {
    d := Dog{
        Model: gorm.Model{
            ID: 1,
        },
    }
    var girls []GirlGod
    GLOBAL_DB.Model(&d).Preload("Dogs", func(db *gorm.DB) *gorm.DB {
        return db.Joins("MoneyInfo").Where("money<?", 10000)
    }).Association("GirlGod").Find(&girls)
    fmt.Println(girls)
}

[{{1 2022-08-03 19:46:40.329 +0800 CST 2022-08-03 19:46:40.329 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} 小七 [{{1 2022-08-03 19:46:40.296 +0800 CST 2022-08-03 19:46:40.296 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} 十三 {{1 2022-08-03 19:46:40.328 +0800 CST 2022-08-03 19:46:40.328 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} 8000 1} []}]} {{2 2022-08-03 19:46:40.329 +0800 CST 2022-08-03 19:46:40.329 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} 十九 [{{1 2022-08-03 19:46:40.296 +0800 CST 2022-08-03 19:46:40.296 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} 十三 {{1 2022-08-03 19:46:40.328 +0800 CST 2022-08-03 19:46:40.328 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} 8000 1} []}]}]

对于以上查询我的理解： FInd()函数的传参决定了存储的主体是谁——girlgod Model()函数的传参决定了执行查询的主体是谁——Dog 查询主体和存储主体之间有关系——Association("GirlGod") 预加载Preload将belong to中的数据带出并进行了筛选

关系的各种操作

func One2one() {
    g1 := GirlGod{
        Model: gorm.Model{
            ID: 1,
        },
    }
    g2 := GirlGod{
        Model: gorm.Model{
            ID: 2,
        },
    }
    d := Dog{
        Model: gorm.Model{
            ID: 3,
        },
    }
    //append relationship
    GLOBAL_DB.Model(&d).Association("GirlGod").Append(&g1, &g2)
    ///replace relationship
    GLOBAL_DB.Model(&d).Association("GirlGod").Replace(&g2)
    //clear relationship
    GLOBAL_DB.Model(&d).Association("GirlGod").Clear()
}

多态

夹子是一个结构体，御姐也是一个结构体，当他们与小风车有has one、has many关系时，会造成一个问题：小风车里必须有Jiazi的结构体类型以及ID才能与jiazi结构体建立联系。那小风车与多少个结构建立联系就会有多少个成员变量，这会使结构体变得冗余。所以抽象一个OwnerType存放建立联系的结构体名字，OwnerID存放建立联系实体的ID (Owner是标签中自定义的)

package main

type Jiazi struct {
    ID          uint // In gorm's convention, ID is the primary key
    Name        string
    Xiaofengche Xiaofengche `gorm:"polymorphic:Owner;"`
}
type Yujie struct {
    ID          uint // In gorm's convention, ID is the primary key
    Name        string
    Xiaofengche Xiaofengche `gorm:"polymorphic:Owner;"`
}
type Xiaofengche struct {
    ID        uint
    Name      string
    OwnerType string
    OwnerID   uint
}

func Polymorphic() {
    GLOBAL_DB.Create(&Jiazi{Name: "夹子", Xiaofengche: Xiaofengche{
        Name: "小风车",
    }})
    GLOBAL_DB.Create(&Yujie{Name: "御姐", Xiaofengche: Xiaofengche{
        Name: "大风车",
    }})
}

mysql> select * from jiazis;
+----+------+
| id | name |
+----+------+
|  1 | 夹子 |
+----+------+
mysql> select * from yujies;
+----+------+
| id | name |
+----+------+
|  1 | 御姐 |
+----+------+
mysql> select * from xiaofengches;
+----+--------+------------+----------+
| id | name   | owner_type | owner_id |
+----+--------+------------+----------+
|  1 | 小风车 | jiazis     |        1 |
|  2 | 大风车 | yujies     |        1 |
+----+--------+------------+----------+

gorm:"polymorphic:Owner;"是指将这类多态关系命名为Owner``polymorphicValue:sister是指将这个结构体的Ownertype修改为sister（默认为该结构体名字）

关联标签

标签	描述
foreignKey	指定当前模型的列作为连接表的外键
references	指定引用表的列名，其将被映射为连接表外键
polymorphic	指定多态类型，比如模型名
polymorphicValue	指定多态值、默认表名
many2many	指定连接表表名
joinForeignKey	指定连接表的外键列名，其将被映射到当前表
joinReferences	指定连接表的外键列名，其将被映射到引用表
constraint	关系约束，例如：`OnUpdate`、`OnDelete`

foreignKey reference 标签的使用

has one中的使用

package main

type Jiazi struct {
    ID          uint // In gorm's convention, ID is the primary key
    Name        string
    Xiaofengche []Xiaofengche `gorm:"foreignKey:JiaziName;references:Name"` //Specify the foreign key name
    //The default foreign key will be associated with the primary key of the structure: ID,
    // use reference to change the default associated property to: Name
}
type Xiaofengche struct {
    ID        uint
    Name      string
    JiaziName string
}

func Polymorphic() {
    GLOBAL_DB.AutoMigrate(&Jiazi{}, &Xiaofengche{})
    GLOBAL_DB.Create(&Jiazi{Name: "大夹子", Xiaofengche: []Xiaofengche{
        {Name: "小风车"},
        {Name: "大风车"},
    }})
}

mysql> select * from xiaofengches;
+----+--------+------------+
| id | name   | jiazi_name |
+----+--------+------------+
|  1 | 小风车 | 大夹子     |
|  2 | 大风车 | 大夹子     |
+----+--------+------------+

many to many 、 has many 中的使用

package main

type Jiazi struct {
    ID          uint // In gorm's convention, ID is the primary key
    Name        string
    Xiaofengche []Xiaofengche `gorm:"many2many:jiazi_fengche;foreignKey:Name;references:FCName"`
}
type Xiaofengche struct {
    ID     uint
    FCName string
}

func Polymorphic() {
    GLOBAL_DB.AutoMigrate(&Jiazi{}, &Xiaofengche{})
    GLOBAL_DB.Create(&Jiazi{Name: "大夹子", Xiaofengche: []Xiaofengche{
        {FCName: "小风车"},
        {FCName: "大风车"},
    }})
}

mysql> SELECT * FROM golang.jiazi_fengche;
+------------+---------------------+
| jiazi_name | xiaofengche_fc_name |
+------------+---------------------+
| 大夹子     | 大风车              |
| 大夹子     | 小风车              |
+------------+---------------------+

更换指定外键的名字

package main

type Jiazi struct {
    ID          uint // In gorm's convention, ID is the primary key
    Name        string
    Xiaofengche []Xiaofengche `gorm:"many2many:jiazi_fengche;foreignKey:Name;joinForeignKey:jiazi;references:FCName;joinReferences:fengche"`
}
type Xiaofengche struct {
    ID     uint
    FCName string
}

func Polymorphic() {
    GLOBAL_DB.AutoMigrate(&Jiazi{}, &Xiaofengche{})
    GLOBAL_DB.Create(&Jiazi{Name: "大夹子", Xiaofengche: []Xiaofengche{
        {FCName: "小风车"},
        {FCName: "大风车"},
    }})
}

mysql> SELECT * FROM golang.jiazi_fengche;
+--------+---------+
| jiazi  | fengche |
+--------+---------+
| 大夹子 | 大风车  |
| 大夹子 | 小风车  |
+--------+---------+
2 rows in set (0.00 sec)

事务

package main

import (
    "errors"
    "fmt"

    "gorm.io/gorm"
)

type TMG struct {
    ID   uint
    Name string
}

func TestTransaction() {
    flag := false
    GLOBAL_DB.AutoMigrate(&TMG{})
    GLOBAL_DB.Transaction(
        func(tx *gorm.DB) error { // return errors all of SQL sentence not be excute
            tx.Create(&TMG{Name: "张三"})
            tx.Create(&TMG{Name: "李四"})
            tx.Create(&TMG{Name: "王五"})
            fmt.Println("执行过")
            if flag {
                return nil
            } else {
                return errors.New("出现错误")
            }
        })
}

嵌套事务

func TestTransaction() {
    GLOBAL_DB.AutoMigrate(&TMG{})
    GLOBAL_DB.Transaction(
        func(tx *gorm.DB) error { // return errors all of SQL sentence not be excute
            tx.Create(&TMG{Name: "张三"})
            tx.Create(&TMG{Name: "李四"})
            tx.Create(&TMG{Name: "王五"})
            tx.Transaction(func(tx *gorm.DB) error {
                tx.Create(&TMG{Name: "老六"})
                return errors.New("has something wrong")
            })
            fmt.Println("执行过")
            return nil
        })
}

mysql> select * from tmgs;
+----+------+
| id | name |
+----+------+
| 10 | 张三 |
| 11 | 李四 |
| 12 | 王五 |
+----+------+
3 rows in set (0.00 sec)

内层事务返回错误，不执行，外层事务成功执行

自定义数据类型

接受和存放json这类自定义字符串，要重写两个函数，Value()、Scan()

package main

import (
    "database/sql/driver"
    "encoding/json"
    "errors"
    "fmt"
)

type CInfo struct {
    Name string
    Age  int
}
type CUser struct {
    ID   uint
    Info CInfo
}

func (c CInfo) Value() (driver.Value, error) {
    str, err := json.Marshal(c) //convert to json
    if err != nil {
        return nil, err
    } else {
        return string(str), nil
    }
}
func (c *CInfo) Scan(value interface{}) error {
    str, ok := value.([]byte) //断言
    if !ok {
        return errors.New("不匹配的数据类型")
    }
    json.Unmarshal(str, c)
    return nil
}
func Customize() {
    GLOBAL_DB.AutoMigrate(&CUser{})
    //GLOBAL_DB.Create(&CUser{Info: CInfo{Name: "十七", Age: 22}})
    var u CUser
    GLOBAL_DB.First(&u)
    fmt.Println(u)
}

package main

import (
    "database/sql/driver"
    "encoding/json"
    "errors"
    "fmt"
    "strings"
)

type CInfo struct {
    Name string
    Age  int
}

func (c CInfo) Value() (driver.Value, error) {
    str, err := json.Marshal(c) //convert to json
    if err != nil {
        return nil, err
    } else {
        return string(str), nil
    }
}
func (c *CInfo) Scan(value interface{}) error {
    str, ok := value.([]byte) //断言
    if !ok {
        return errors.New("不匹配的数据类型")
    }
    json.Unmarshal(str, c)
    return nil
}

type Args []string

func (a Args) Value() (driver.Value, error) {
    if len(a) > 0 {
        var str string = a[0]
        for _, v := range a[1:] {
            str += "," + v
        }
        return str, nil
    } else {
        return "", nil
    }
}

func (a *Args) Scan(value interface{}) error {
    str, ok := value.([]byte)
    if !ok {
        return errors.New("数据类型无法解析")
    }
    *a = strings.Split(string(str), ",")
    return nil
}

type CUser struct {
    ID   uint
    Info CInfo
    Args Args
}

func Customize() {
    GLOBAL_DB.AutoMigrate(&CUser{})
    //GLOBAL_DB.Create(&CUser{Info: CInfo{Name: "十七", Age: 22}, Args: Args{"阿巴阿巴阿巴", "哇啦哇啦哇啦"}})
    var u CUser
    GLOBAL_DB.First(&u)
    fmt.Println(u)
}

ORGM基础