GO数据库操作

Golang 出色的 ORM 库为 GORM。

官网文档：https://gorm.io/docs/

我们来说说增删改查的用法，更深入的研究可以去官网看看。

GORM功能概览：

• 关联（有一个、有多个、属于、多对多、多态性、单表继承）
• 挂钩（创建/保存/更新/删除/查找之前/之后）
• 急切加载Preload,Joins
• 事务、嵌套事务、保存点、RollbackTo 到保存点
• 上下文、准备语句模式、DryRun 模式
• 批量插入、FindInBatches、使用 Map 查找/创建、使用 SQL Expr 和 Context Valuer 进行 CRUD
• SQL 生成器、更新插入、锁定、优化器/索引/注释提示、命名参数、子查询
• 复合主键、索引、约束
• 自动迁移
• 记录器
• 可扩展、灵活的插件 API：Database Resolver（多个数据库、读/写拆分）/Prometheus...

  下载依赖

go -u gorm.io/gorm

表Model定义

GORM内置了一个gorm.Model结构体，gorm.Model包含了ID, CreatedAt, UpdatedAt, DeletedAt四个字段。默认情况下，GORM 使用 ID 作为主键，使用结构体名的 蛇形复数 作为表名，字段名的 蛇形 作为列名，并使用 CreatedAt、UpdatedAt 字段追踪创建、更新时间。如果表中定义了这种字段，GORM 在创建、更新和删除时会自动填充当前时间。

当然也可以不用gorm.Model，完全自定义创建、更新时间及其格式，这就需要在创建和更新时自己赋值并入库。

// 定义product表，使用gorm.Model结构体
type Product struct {gorm.ModelCode  stringPrice uint
}// 指定product表名
func (Product) TableName() string {return "product"
}

连接数据库

var db *gorm.DB// 连接数据库
func ConnDB() {var (Username = "root"Password = "123456"Endpoint = "localhost:3306"Database = "aiee"err      error)dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=utf8&parseTime=true&loc=Local",Username, Password, Endpoint, Database,)db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})if err != nil {fmt.Println("failed to connect database,", err.Error())return}// debug模式db = db.Debug()
}

数据插入

// 数据插入
func CreateProduct() error {var (product  = Product{Code: "D42", Price: 100}products = []Product{{Code: "D43", Price: 120},{Code: "D45", Price: 150},}err error)// 数据插入一条// 实际执行sql: INSERT INTO `product` (`created_at`,`updated_at`,`deleted_at`,`code`,`price`) VALUES ('2023-12-21 11:42:10.798','2023-12-21 11:42:10.798',NULL,'D42',100)err = db.Create(&product).Errorif err != nil {fmt.Println("failed to create data,", err.Error())return err}// 插入多条// 实际执行sql: INSERT INTO `product` (`created_at`,`updated_at`,`deleted_at`,`code`,`price`) VALUES ('2023-12-21 11:42:10.8','2023-12-21 11:42:10.8',NULL,'D43',120),('2023-12-21 11:42:10.8','2023-12-21 11:42:10.8',NULL,'D45',150)err = db.Create(&products).Errorif err != nil {fmt.Println("failed to create data,", err.Error())return err}return err
}

数据读取

// 数据读取
func ReadProduct() error {var (productById   ProductproductByCode Productproducts      []Producterr           error)// 通过主键ID查询一条// 实际执行sql: SELECT * FROM `product` WHERE `product`.`id` = 1 AND `product`.`deleted_at` IS NULL ORDER BY `product`.`id` LIMIT 1err = db.First(&productById, 1).Errorif err != nil {fmt.Println("failed to read first data by id,", err.Error())return err}fmt.Println(productById)// 通过code字段读取一条// 实际执行sql: SELECT * FROM `product` WHERE code = 'D42' AND `product`.`deleted_at` IS NULL ORDER BY `product`.`id` LIMIT 1err = db.First(&productByCode, "code = ?", "D42").Errorif err != nil {fmt.Println("failed to read first data by code,", err.Error())return err}fmt.Println(productByCode)// 读取列表// 实际执行sql: SELECT * FROM `product` WHERE `product`.`deleted_at` IS NULL LIMIT 10err = db.Limit(10).Find(&products).Errorif err != nil {fmt.Println("failed to read products,", err.Error())return err}fmt.Println(products)// Where条件查询// 实际执行sql: SELECT * FROM `product` WHERE code = 'D42' AND `product`.`deleted_at` IS NULL LIMIT 10err = db.Where("code = ?", "D42").Limit(10).Find(&products).Errorif err != nil {fmt.Println("failed to read products,", err.Error())return err}fmt.Println(products)return err
}

数据更新

// 更新数据
func UpdateProduct() error {var (product Producterr     error)// 通过主键ID查询一条err = db.First(&product, 1).Errorif err != nil {fmt.Println("failed to read first data by id,", err.Error())return err}// 更新查询的数据，Price=200// 实际执行sql: UPDATE `product` SET `price`=200,`updated_at`='2023-12-21 11:57:51.842' WHERE `product`.`deleted_at` IS NULL AND `id` = 1err = db.Model(&product).Update("price", 200).Errorif err != nil {fmt.Println("failed to update product,", err.Error())return err}// 更新查询的数据，使用Product更新多字段// 实际执行sql: UPDATE `product` SET `updated_at`='2023-12-21 11:57:51.845',`code`='F41',`price`=200 WHERE `product`.`deleted_at` IS NULL AND `id` = 1err = db.Model(&product).Updates(Product{Price: 200, Code: "F41"}).Errorif err != nil {fmt.Println("failed to update product,", err.Error())return err}// 更新查询的数据，使用map更新多字段// 实际执行sql: UPDATE `product` SET `code`='F42',`price`=200,`updated_at`='2023-12-21 11:57:51.847' WHERE `product`.`deleted_at` IS NULL AND `id` = 1err = db.Model(&product).Updates(map[string]interface{}{"price": 200, "code": "F42"}).Errorif err != nil {fmt.Println("failed to update product,", err.Error())return err}// Where条件更新数据// 实际执行sql: UPDATE `product` SET `code`='F42',`price`=200,`updated_at`='2023-12-21 11:57:51.848' WHERE code = 'D42' AND `product`.`deleted_at` IS NULLerr = db.Model(&Product{}).Where("code = ?", "D42").Updates(map[string]interface{}{"price": 200, "code": "F42"}).Errorif err != nil {fmt.Println("failed to update product,", err.Error())return err}return err
}

数据删除

func DeleteProduct() error {var (product Producterr     error)// 通过ID删除数据// 实际执行sql: UPDATE `product` SET `deleted_at`='2023-12-21 12:01:41.257' WHERE `product`.`id` = 1 AND `product`.`deleted_at` IS NULLerr = db.Delete(&product, 1).Errorif err != nil {fmt.Println("failed to delete first data by id,", err.Error())return err}// 通过code删除数据// 实际执行sql: UPDATE `product` SET `deleted_at`='2023-12-21 12:03:06.449' WHERE code = 'D42' AND `product`.`deleted_at` IS NULLerr = db.Delete(&product, "code = ?", "D42").Errorif err != nil {fmt.Println("failed to delete first data by id,", err.Error())return err}// 通过Where条件删除数据// 实际执行sql: UPDATE `product` SET `deleted_at`='2023-12-21 12:04:38.424' WHERE id < 2 AND `product`.`deleted_at` IS NULLerr = db.Where("id < ?", 2).Delete(&product).Errorif err != nil {fmt.Println("failed to delete first data by id,", err.Error())return err}return err
}