MySQL表间关系

一对一关系

一对一关系意味着两个表中的一行记录只能分别关联对方表中的一行记录。

典型场景：用户表和用户详情表。每个用户只有一份详细信息，反之亦然。
示例：

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL
);

CREATE TABLE user_profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE,
    date_of_birth DATE NOT NULL,
    bio TEXT,
FOREIGN KEY (user_id) REFERENCES users (id)
);

在这个例子中，user_profiles表中的user_id与users表中的id建立了一对一关系。

使用GORM 构建一对一表间关系

一对一（One-to-One）关系：这种关系可以通过在模型中定义一个拥有唯一约束的外键来实现。

type RoleV2 struct {
	gorm.Model
	Name    string   `gorm:"type:varchar(20);not null;comment:'角色名'" json:"name"`
	Keyword string   `gorm:"type:varchar(20);not null;comment:'角色标识'" json:"keyword"` // unique唯一
	Desc    string   `gorm:"type:varchar(100);comment:'角色描述'" json:"desc"`
	Status  int32    `gorm:"type:int(1) unsigned;default:1;comment:'1正常, 2禁用'" json:"status"`
	Sort    int32    `gorm:"type:int(3) unsigned;default:999;comment:'角色排序(排序越大权限越低, 不能查看比自己序号小的角色, 不能编辑同序号用户权限, 排序为1表示超级管理员)'" json:"sort"`
	Creator string   `gorm:"type:varchar(10);comment:'创建人'" json:"creator"`
	AppID uint  `gorm:"type:int(3);default:999;not null;comment:'APPID'" json:"app_id"` // 外键字段声明；外键字段不能使用 unsigned约束，创建外键约束时类型不兼容、且必须和AppV2 的 ID 类型兼容「gromg.ID unit」
    App   AppV2  `gorm:"foreignKey:AppID;references:ID" json:"app"` // 这是一对一的关联表达
	Users   []UserV2 `gorm:"many2many:user_roles_v2" json:"users"`  //role_menus_v2自定义多对多的中间表表名;自动处理参照完整性（创建外键约束），使用 `references` 标签
	Menus   []MenuV2 `gorm:"many2many:role_menus_v2;" json:"menus"` // 角色菜单多对多关系
}

type AppV2 struct {
	gorm.Model
	Name    string `gorm:"type:varchar(20);comment:'应用名'" json:"name"`
	Host    string `gorm:"type:varchar(40);comment:'访问地址'" json:"host"`
	Desc    string `gorm:"type:varchar(100);comment:'说明'" json:"desc"`
	Creator string `gorm:"type:varchar(10);comment:'创建人'" json:"creator"`
	LoginByLDAP bool `gorm:"type:bool;comment:'是否支持LDAP登录'" json:"loginByLDAP"`
	LoginByFeishu bool `gorm:"type:bool;comment:'是否支持Feishu登录'" json:"loginByFeishu"`
}

// 创建表并建立外键
// db is *gorm.DB
db.AutoMigrate(&RoleV2{}, &AppV2{})

在这个例子中，RoleV2 通过 AppID 字段与 AppV2 形成了一对一关系

使用GROM 执行一对一查询

假设为 User 和 UserProfile 生成了Gorm Gen代码。

var user User
var userProfile UserProfile

// 查询User同时获取其关联的UserProfile
db.Find(&user, "id = ?", userId) // userId 为查找的用户ID
db.Model(&user).Association("Profile").Find(&userProfile)

在这个示例中，我们首先查询了一个User对象，然后通过 Association 方法获取了它关联的 UserProfile 对象。

使用GROM GEN执行一对一查询

一对一（One-to-One）关系：

var user User
var userProfile UserProfile

// 使用 gen 生成的用户查询对象
db.User.Where(User.ID.Eq(userId)).First(&user) // userId 为查找的用户ID
db.UserProfile.Where(UserProfile.UserID.Eq(user.ID)).First(&userProfile)

在这个例子中，db.User 和 db.UserProfile 是由 gen 生成的基于模型的查询对象，可以类型安全地构建查询。

一对一关系的插入「Grom方式」

// 进行事务处理
err := roleDB.Transaction(func(tx *gorm.DB) error {

	app := &adminv2.AppV2{
		Name: &app,
	}

	// 首先插入 AppV2 记录
	if err := tx.Create(&app).Error; err != nil {
		return err // 插入失败，回滚事务
	}

	role := &adminv2.RoleV2{
		Name:    sub,
		Keyword: sub,
		// Status: &{1},
		// Obj: obj,
		// Act: act,
		// Method: method,
		// T: t,
		// App: app,
	}

	// 设置 RoleV2 中的 AppID 为新插入 AppV2 的 ID
	role.AppID = &app.ID

	// 然后插入 RoleV2 记录
	if err := tx.Create(&role).Error; err != nil {
		return err // 插入失败，回滚事务
	}

	// 其他相关操作...

	return nil // 事务提交

一对多关系

在一对多关系中，一个表的一行记录可以关联到另一个表中的多行记录。

典型场景：客户表和订单表。一个客户可以有多个订单，但每个订单只对应一个客户。
示例：

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,
    customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers (id)
);

每个订单 (orders) 中的 customer_id 指向一个特定的客户 (customers)，每个客户都可能有多个订单。

使用GORM 构建一对多表间关系

一对多关系是通过在多的一端的模型中定义外键来实现的。

type Customer struct {
    gorm.Model
    Name string
    Orders []Order
}

type Order struct {
    gorm.Model
    OrderDate time.Time
    CustomerID uint // 这里是外键
}

// 创建表并建立外键
db.AutoMigrate(&Customer{}, &Order{})

在这个例子中，Order 模型中的 CustomerID 字段用作外键，将订单与一个客户相关联，每个客户可以拥有多个订单。

使用GROM 执行一对多查询

假设为 Customer 和 Order 生成了Gorm Gen代码。

var customer Customer
var orders []Order

// 查询Customer同时获取其关联的所有Order
db.Find(&customer, "id = ?", customerId) // customerId 为查找的客户ID
db.Model(&customer).Association("Orders").Find(&orders)

在这个示例中，我们首先查询了一个Customer对象，然后通过 Association 方法获取了它关联的所有 Order 对象。

使用GROM GEN执行一对多查询

var customer Customer
var orders []Order

// 使用 gen 生成的客户查询对象
db.Customer.Where(Customer.ID.Eq(customerId)).First(&customer) // customerId 为查找的客户ID
db.Order.Where(Order.CustomerID.Eq(customer.ID)).Find(&orders)

在这个例子中，通过生成的 Customer 和 Order 查询对象进行了类型安全的查询。

多对多关系

在多对多关系中，两个表的行记录相互之间可以有多条关联。

典型场景：学生表和课程表。每个学生可以选多个课程，每个课程也可以有多个学生参加。
示例：

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students (id),
FOREIGN KEY (course_id) REFERENCES courses (id)
);

在这个例子中，student_courses作为一个联结表（或叫关联表、中间表），它包含外键 student_id 和 course_id，这两个外键共同建立起学生和课程之间的多对多关系。

使用GORM 构建多对多表间关系

多对多关系通常需要一个中间表来连接两种类型的记录，Gorm借助模型的tag以及运行时的逻辑自动维护中间表。

type Student struct {
    gorm.Model
    Name string
    Courses []*Course `gorm:"many2many:student_courses;"`
}

type Course struct {
    gorm.Model
    Title string
    Students []*Student `gorm:"many2many:student_courses;"`
}

// Gorm会自动维护一个名为student_courses的表
db.AutoMigrate(&Student{}, &Course{})

在这个例子中，Student 和 Course 之间存在多对多的关系，Gorm自动处理了中间表 student_courses 的创建和维护。

使用GROM 执行多对多查询

假设为 Student 和 Course 生成了Gorm Gen代码。

var student Student
var courses []Course
// 查询Student同时获取其关联的所有Course
db.Find(&student, "id = ?", studentId) // studentId 为查找的学生ID
db.Model(&student).Association("Courses").Find(&courses)

在这个示例中，我们首先查询了一个Student对象，然后通过 Association 方法获取了它关联的所有 Course 对象。

使用GROM GEN执行多对多查询

如果多对多关系定义为以下形式，比如学生和课程的例子：

type Student struct {
    ID uint
    Name string
    Courses []Course `gorm:"many2many:student_courses;"`
}

type Course struct {
    ID uint
    Title string
    Students []Student `gorm:"many2many:student_courses;"`
}

通过 gorm/gen 生成代码后，能够使用以下方式进行查询：

// 假设 studentQuery 和 courseQuery 是 `gen` 生成的查询对象

// 获取学生以及关联的课程
var student Student
err := studentQuery.Where(studentQuery.ID.Eq(studentId)).Preload("Courses").First(&student).Error

// 以类似的方式获取课程以及关联的学生
var course Course
err = courseQuery.Where(courseQuery.ID.Eq(courseId)).Preload("Students").First(&course).Error

在这个例子中，Preload 方法会根据定义好的多对多关系来预加载课程数据（用于学生）或学生数据（用于课程）。

GORM 高级使用

「一对多」关系

显性声明一对多关系

字段类型为嵌套[]struct
是否需要显性声明一对多关系，需要。否则会报错

[error] invalid field found for struct x.com/dto/xBillDetail's field ComponentSet: define a valid foreign key for relations or implement the Valuer/Scanner interface

如果使用ForeignKey，则不用实现Valuer/Scanner

示例

tag；定义 ForeignKey，指向子表的BillDetailID字段

type QCloudBillDetail struct {
	gorm.Model 
	
	ComponentSet []QCloudBillDetailComponent `json:"componentSet,omitempty" name:"ComponentSet" gorm:"ForeignKey:BillDetailID"`

	Tags []QCloudBillTagInfo `json:"tags,omitempty" name:"Tags" gorm:"ForeignKey:BillDetailID"`
	
	PriceInfo QCloudPriceInfo `json:"priceInfo,omitempty" name:"PriceInfo" gorm:"type:json"`

}

ComponentSet 字段嵌套 ComponentSet列表，需要指定ComponentSet的ID为主键
未指定主键，会报错
- [error] invalid field found for struct acmp-service/dto.XBillDetail's field ComponentSet: define a valid foreign key for relations or implement the Valuer/Scanner interface
- 解决：增加主键
Tags字段处理相同；增加主键

一对多子表的主键设置

type QCloudBillDetailComponent struct {
	gorm.Model 

	BillDetailID int64 `json:"billDetailID,omitempty" name:"BillDetailID" gorm:"type:varchar(16)"`

}

type QCloudBillTagInfo struct {
	gorm.Model 
        
	BillDetailID int64 `json:"billDetailID,omitempty" name:"BillDetailID" gorm:"type:varchar(16)"`
}

「一对多」的另外一种形态[]string

PriceInfo字段；grom支持[]struct，也支持[]string；但不支持[]*string指针
使用[]*string，会报错
- got error unsupported data type: &[]
- 解决：要么使用[]string；要么转换为[]struct
且如果要使用[]string，是支持的，但
- 需要创建实现 Valuer/Scanner 接口的自定义数据类型

通过实现Value、Scan方法


import (
	"database/sql/driver"
)

type XPriceInfo []string 

func (s XPriceInfo) Value() (driver.Value, error) {
	// 序列化 `Strings` 为 JSON 字符串
	jsonString, err := json.Marshal(s)
	if err != nil {
	  return nil, err
	}
  
	// 返回 JSON 字符串
	return jsonString, nil
  }
  
  func (s *XPriceInfo) Scan(value interface{}) error {
	// 反序列化 JSON 字符串为 `Strings`
	jsonString, ok := value.([]byte)
	if !ok {
	  return errors.New("invalid type")
	}
  
	err := json.Unmarshal(jsonString, s)
	if err != nil {
	  return err
	}
  
	return nil
  }

[]string类型，在数据库类型为json

json类型字段

json类型字段，是否需要约束json字段长度。不能限定，否则会报错 PriceInfo QCloudPriceInfo json:"priceInfo,omitempty" name:"PriceInfo" gorm:"type:json"``

Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(32),PRIMARY KEY (id),INDEX idx_q_cloud_bill_details_deleted_at (`deleted_at' at line 1

声明grom类型json

PriceInfo字段指向 QCloudPriceInfo

	PriceInfo QCloudPriceInfo `json:"priceInfo,omitempty" name:"PriceInfo" gorm:"type:json"`

「一对一」关系

一对对关系时，必须实现Value、Scan方法
否则会报错

[error] invalid field found for struct x.com/infra/exporter/dto/qcloud.QCloudBillDetail's field AssociatedOrder: define a valid foreign key for relations or implement the Valuer/Scanner interface

字段为自定义类型如struct


AssociatedOrder QCloudBillDetailAssociatedOrder `json:"associatedOrder,omitempty" name:"AssociatedOrder"`


type QCloudBillDetailAssociatedOrder struct {
   gorm.Model

   BillDetailID int64 `json:"billDetailID,omitempty" name:"BillDetailID" gorm:"type:varchar(16)"`

   PrepayPurchase string `json:"prepayPurchase,omitempty" name:"PrepayPurchase" gorm:"type:varchar(16)"`

   PrepayRenew string `json:"prepayRenew,omitempty" name:"PrepayRenew" gorm:"type:varchar(16)"`

   PrepayModifyUp string `json:"prepayModifyUp,omitempty" name:"PrepayModifyUp" gorm:"type:varchar(16)"`

   ReverseOrder string `json:"reverseOrder,omitempty" name:"ReverseOrder" gorm:"type:varchar(16)"`

   NewOrder string `json:"newOrder,omitempty" name:"NewOrder" gorm:"type:varchar(16)"`

   Original string `json:"original,omitempty" name:"Original" gorm:"type:varchar(16)"`
}

MySQ表间关系以及GORM 的数据完整性约束、一对一、一对多、多对多

MySQL表间关系

一对一关系

使用GORM 构建一对一表间关系

使用GROM 执行一对一查询

使用GROM GEN执行一对一查询

一对一关系的插入「Grom方式」

一对多关系

使用GORM 构建一对多表间关系

使用GROM 执行一对多查询

使用GROM GEN执行一对多查询

多对多关系

使用GORM 构建多对多表间关系

使用GROM 执行多对多查询

使用GROM GEN执行多对多查询

GORM 高级使用

「一对多」关系

显性声明一对多关系

示例

一对多子表的主键设置

「一对多」的另外一种形态[]string

通过实现Value、Scan方法

[]string类型，在数据库类型为json

json类型字段

声明grom类型json

「一对一」关系

字段为自定义类型如struct

struct类型，在数据库类型为longblob

MySQ表间关系以及GORM 的数据完整性约束、一对一、一对多、多对多

MySQL表间关系

一对一关系

使用GORM 构建一对一表间关系

使用GROM 执行一对一查询

使用GROM GEN执行一对一查询

一对一关系的插入「Grom方式」

一对多关系

使用GORM 构建一对多表间关系

使用GROM 执行一对多查询

使用GROM GEN执行一对多查询

多对多关系

使用GORM 构建多对多表间关系

使用GROM 执行多对多查询

使用GROM GEN执行多对多查询

GORM 高级使用

「一对多」关系

显性声明一对多关系

示例

一对多 子表的主键设置

「一对多」的另外一种形态[]string

通过实现Value、Scan方法

[]string类型，在数据库类型为json

json类型字段

声明grom类型json

「一对一」关系

字段为自定义类型 如struct

struct类型，在数据库类型为longblob

一对多子表的主键设置

字段为自定义类型如struct