gorm数据库是否需要设置外键

结论

gorm一对多官方文档

这里先说结论,gorm使用联合查询时不需要强制设置外键;但是从官方文档上来看,貌似关联查询必须建立外键,但其实是不需要设置外键的.代码会拿一对多进行举例;

设置外键的缺点

1. 性能影响: 大型互联网项目或者分布式项目,进行更新操作时外键会影响数据库的性能
2. 强耦合: 如果数据库中存在外键,会导致表与表之间的强耦合;其实可以再物理上删除外键;逻辑上还是存在的.(举个例子:用户表与信用卡表;信用卡表是有user_id这个字段的;我们再查询的时候还是通过user_id字段进行查询,但是表中并不会存在外键进行关联)
3. 热更新: 如果数据库存在外键,会导致新更新的代码无法运行,会有产生冲突,sql报错的隐患
4. 分库分表难度增加: 外键会导致分库分表难度增加(举个例子:用户表与信用卡表之间有外键关联,如果需要做分库,由于有外键的存在,是没有办法实现分布操作的)

sql

CREATE TABLE `credit_card` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `number` varchar(30) NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_credit_card_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

代码

model

package model

import "gorm.io/gorm"

type User struct {
	gorm.Model
	CreditCards []*CreditCard `gorm:"foreignKey:UserId"`
}

type CreditCard struct {
	gorm.Model
	Number string `gorm:"number;type:varchar(30);not null"`
	UserId uint
}

main

新增

package main

import (
	"ShopBefore/gorm/model"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"log"
	"os"
	"time"
)

func main() {

	// 连接对应的数据库
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		"root", "root", "192.168.193.128", 3306, "test")
	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer（日志输出的目标，前缀和日志包含的内容——译者注）
		logger.Config{
			SlowThreshold:             time.Second, // 慢 SQL 阈值
			LogLevel:                  logger.Info, // 日志级别
			IgnoreRecordNotFoundError: true,        // 忽略ErrRecordNotFound（记录未找到）错误
			Colorful:                  true,        // 使用用彩色打印
		},
	)

	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: newLogger,
	})
	if err != nil {
		panic(err)
	}
	var users []model.User
	user := model.User{
		CreditCards: []*model.CreditCard{
			{
				Number: "123",
			},
			{
				Number: "456",
			},
			{
				Number: "789",
			},
		},
	}
	user1 := model.User{
		CreditCards: []*model.CreditCard{
			{
				Number: "321",
			},
			{
				Number: "654",
			},
			{
				Number: "987",
			},
		},
	}
	users = append(users,user)
	users = append(users,user1)
	db.Save(&users)
}

日志:

INSERT INTO `credit_cards` (`created_at`,`updated_at`,`deleted_at`,`number`,`user_id`) VALUES ('2022-06-20 22:40:07.591','2022-06-20 22:40:07.591',NULL,'123',1),('2022-06-20 22:40:07.591','2022-06-20 22:40:07.591',NULL,'456',1),('2022-06-20 22:40:07.591','2022-06-20 22:40:07.591',NULL,'789',1),('2022-06-20 22:40:07.591','2022-06-20 22:40:07.591',NULL,'321',2),('2022-06-20 22:40:07.591','2022-06-20 22:40:07.591',NULL,'654',2),('2022-06-20 22:40:07.591','2022-06-20 22:40:07.591',NULL,'987',2) ON DUPLICATE KEY UPDATE `user_id`=VALUES(`user_id`)
INSERT INTO `users` (`created_at`,`updated_at`,`deleted_at`) VALUES ('2022-06-20 22:40:07.532','2022-06-20 22:40:07.532',NULL),('2022-06-20 22:40:07.532','2022-06-20 22:40:07.532',NULL) ON DUPLICATE KEY UPDATE `updated_at`='2022-06-20 22:40:07.532',`deleted_at`=VALUES(`deleted_at`)

查询

package main

import (
	"ShopBefore/gorm/model"
	"encoding/json"
	"fmt"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"log"
	"os"
	"time"
)

func main() {

	// 连接对应的数据库
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=True&loc=Local",
		"root", "root", "192.168.193.128", 3306, "test")
	newLogger := logger.New(
		log.New(os.Stdout, "\r\n", log.LstdFlags), // io writer（日志输出的目标，前缀和日志包含的内容——译者注）
		logger.Config{
			SlowThreshold:             time.Second, // 慢 SQL 阈值
			LogLevel:                  logger.Info, // 日志级别
			IgnoreRecordNotFoundError: true,        // 忽略ErrRecordNotFound（记录未找到）错误
			Colorful:                  true,        // 使用用彩色打印
		},
	)

	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: newLogger,
	})
	if err != nil {
		panic(err)
	}
	
	var user model.User
	db.Preload("CreditCards").Find(&user,2)
	marshal, _ := json.Marshal(user)
	fmt.Println(string(marshal))
}

日志:

SELECT * FROM `credit_cards` WHERE `credit_cards`.`user_id` = 2 AND `credit_cards`.`deleted_at` IS NULL
SELECT * FROM `users` WHERE `users`.`id` = 2 AND `users`.`deleted_at` IS NULL

结果:

{
    "ID": 2,
    "CreatedAt": "2022-06-20T22:40:07.532 08:00",
    "UpdatedAt": "2022-06-20T22:40:07.532 08:00",
    "DeletedAt": null,
    "CreditCards": [
        {
            "ID": 4,
            "CreatedAt": "2022-06-20T22:40:07.591 08:00",
            "UpdatedAt": "2022-06-20T22:40:07.591 08:00",
            "DeletedAt": null,
            "Number": "321",
            "UserId": 2
        },
        {
            "ID": 5,
            "CreatedAt": "2022-06-20T22:40:07.591 08:00",
            "UpdatedAt": "2022-06-20T22:40:07.591 08:00",
            "DeletedAt": null,
            "Number": "654",
            "UserId": 2
        },
        {
            "ID": 6,
            "CreatedAt": "2022-06-20T22:40:07.591 08:00",
            "UpdatedAt": "2022-06-20T22:40:07.591 08:00",
            "DeletedAt": null,
            "Number": "987",
            "UserId": 2
        }
    ]
}

最后

官方文档看起来很容易误导,让开发以为一对多的关系就必须要存在外键,但是我们再真实的开发环境下,大多是不会用到数据库中的物理外键的,仅仅只是做一个逻辑关联就够了.官方文档这里说的必须存在外键是我们再model里面必须要指明外键,而不是数据库中一定要有这个外键,
注意: 这种情况我们就不能使用db.AutoMigrate() 来自动创建表了,因为我们再代码中指明外键,如果再使用
db.AutoMigrate() 来创建表,那么gorm是一定会帮我们做一个物理外键的

这篇好文章是转载于：学新通技术网