Golang数据库操作之ORM

原创

西北老李 2023-08-28 18:25:29 博主文章分类：Golang专栏 ©著作权

文章标签 ORM GORM sqlbuilder 文章分类 Go语言后端开发

©著作权归作者所有：来自51CTO博客作者西北老李的原创作品，请联系作者获取转载授权，否则将追究法律责任

SQLBuilder

SQLbuilder是一个用于生成SQL语句的库
项目：https://gitee.com/iRainIoT/go-sqlbuilder https://github.com/parkingwang/go-sqlbuilder

已支持MySQL基本的CRUD操作
目前只支持MySQL
不支持多表查询
go get -u github.com/parkingwang/go-sqlbuilder

https://github.com/huandu/go-sqlbuilder 功能更强

go get github.com/huandu/go-sqlbuilder

使用

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
	"github.com/huandu/go-sqlbuilder"
)

var db *sql.DB

func init() {
	var err error
	db, err = sql.Open("mysql", "my_root:123@tcp(192.168.131.12:3306)/world")
	if err != nil {
		log.Panic(err)
	}
}

type City struct { //和字段对应的变量或结构以定义，最好和数据库中的字段顺序对应
	Id          int
	Name        string
	CountryCode string
	District    string
	Population  int
	Cdate       string
}

func main() {
	query := sqlbuilder.
		Select("ID", "Name", "CountryCode", "District", "Population", "Cdate").
		From("city").
		Where("ID>?").
		Offset(3).Limit(3).
		OrderBy("Population").Desc().
		String() //输出为字符串，底层调用Build()
	fmt.Println(query)

	rows, err := db.Query(query, 10)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var city City
		err := rows.Scan(&city.Id, &city.Name, &city.CountryCode, &city.District, &city.Population, &city.Cdate)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Println(city)
	}

}


//SELECT ID, Name, CountryCode, District, Population, Cdate FROM city WHERE ID>? ORDER BY Population DESC LIMIT 3 OFFSET 3
{1890 Shanghai CHN Shanghai 9696300 2023-07-24 21:52:54}
{939 Jakarta IDN Jakarta Raya 9604900 2023-07-24 21:52:54}
{2822 Karachi PAK Sindh 9269265 2023-07-24 21:52:54}

本质上sqlbuilder就是在生成SQL字符串

args参数化

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/go-sql-driver/mysql"
	"github.com/huandu/go-sqlbuilder"
)

var db *sql.DB

func init() {
	var err error
	db, err = sql.Open("mysql", "my_root:123@tcp(192.168.131.12:3306)/world")
	if err != nil {
		log.Panic(err)
	}
}

type City struct { //和字段对应的变量或结构以定义，最好和数据库中的字段顺序对应
	Id          int
	Name        string
	CountryCode string
	District    string
	Population  int
	Cdate       string
}

func main() {
	builder := sqlbuilder.
		Select("ID", "Name", "CountryCode", "District", "Population", "Cdate").
		From("city")

	builder.Where(builder.In("ID", 1, 2, 30)) //参数化
	query, args := builder.Build()

	fmt.Printf("%s\n%v\n", query, args)

	rows, err := db.Query(query, args...)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		var city City
		err := rows.Scan(&city.Id, &city.Name, &city.CountryCode, &city.District, &city.Population, &city.Cdate)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Println(city)
	}

}

ORM

对象关系映射（ Object Relational Mapping），指的是对象和关系之间的映射，使用面向对象的方式操作数据库。

关系模型和Go对象之间的映射：
table --> struct  表映射为结构体
row  -->  object   行映射为实例
column --> property 字段映射为属性

type Student struct {
    id int
    namt string
    age int
}

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

可以认为ORM是一种高级抽象，对象的操作最终还是会转换成对应关系数据库操作的SQL语句，数据库操作的结构会被封装成对象

GORM

GORM是一个友好的、功能全面、性能不错的基于Go语言实现ORM库

安装

https://gorm.io/zh_CN/docs/index.html

gorm.io/driver/mysql 依赖github.com/go-sql-driver/mysql，可以认为它是对驱动的再封装

go get -u gorm.io/gorm
go get -u gorm.io/driver/mysql

连接

import (
	"fmt"
	"log"
	"time"

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

var db *gorm.DB

func init() {
	var err error
	dsn := "my_root:123@tcp(192.168.131.12:3306)/orm?charset=utf8mb4&parseTime=true&loc=Local"
	db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		log.Panic(err)
	}
}

在“gorm.io/driver/mysql/mysql.go中”导入了github.com/go-sql-driver/mysql
也就是说驱动导入了，Dialector的initalize方法也使用了sql.Open

Golang数据库操作之ORM_ORM

模型定义

GORM 倾向于约定优于配置默认情况下，GORM 使用 ID 作为主键，使用结构体名的蛇形复数作为表名，字段名的蛇形作为列名，并使用 CreatedAt、UpdatedAt 字段追踪创建、更新时间。对应关系：

type UserAbc struct {
	ID           uint
	Name_a       string
	Email        *string
	Age          uint8
	Birthday     *time.Time
	MemberNumber sql.NullString
	ActivatedAt  sql.NullTime
	CreatedAt    time.Time
	UpdatedAt    time.Time
}
+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name_a        | longtext            | YES  |     | NULL    |                |
| email         | longtext            | YES  |     | NULL    |                |
| age           | tinyint(3) unsigned | YES  |     | NULL    |                |
| birthday      | datetime(3)         | YES  |     | NULL    |                |
| member_number | longtext            | YES  |     | NULL    |                |
| activated_at  | datetime(3)         | YES  |     | NULL    |                |
| created_at    | datetime(3)         | YES  |     | NULL    |                |
| updated_at    | datetime(3)         | YES  |     | NULL    |                |
+---------------+---------------------+------+-----+---------+----------------+

如果不遵从以上约定就要自定义配置：

// 不符合约定的定义，很多都需要配置，直接用不行
type Emp struct { // 默认表名emps
	emp_no     int    // 不是ID为主键，需要配置
	first_name string // 首字母未大写，也需要配置
	last_name  string
	gender     byte
	birth_date string
}

// 符合约定的定义如下
type student struct { // 默认表名students
	ID   int    // Id也可以
	Name string // 字段首字母要大写
	Age  int
}

表名配置

表名没有遵从约定，需要实现TableName接口
func (TbSctmp) TableName() string {
	return "tb_sctmp"
}

字段配置

tag 参考：https://gorm.io/zh_CN/docs/models.html

package main

import (
	"fmt"
	"log"

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

var db *gorm.DB

func init() {
	var err error
	dsn := "my_root:123@tcp(192.168.131.12:3306)/world?charset=utf8mb4&parseTime=true&loc=Local"
	db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info), //日志级别，默认silent
	})
	if err != nil {
		log.Panic(err)
	}
	fmt.Println(db.Select("1"))
}

type TbCountryLanguage struct { //默认表名tb_country_languages
	CountryCode string `gorm:"primaryKey;type:char(3);not null;default:'';column:countrycode"` //不是ID为主键
	Language    string `gorm:"type:char(30);not null;default:''"`
	IsOfficial  string `gorm:"type:enum('T','F');not null;default:'F'"` //驼峰体，默认字段为：is_official
	Percentage  string `gorm:"type:decimal(4,1);not null;default:'0.0'"`
}

func (TbCountryLanguage) TableName() string {
	return "tb_countrylanguage"
}

func main() {
	// db.Migrator().CreateTable(&TbCountryLanguage{})
	var t TbCountryLanguage
	result := db.Take(&t)
	fmt.Println(result)
	fmt.Println(result.Error)
	fmt.Println(t)
}

列名

如果未按照约定定义字段，需要定义结构体属性时执行数据库字段名称是什么，但是首字母一定要大写

BirthDate string `gorm:"column:birth_date"` // 可以使用column指定数据库表中的对应
字段名
Xyz string `gorm:"column:birth_date"` // 字段名可以不符合约定，但字段名首字母一定要
大写

迁移

结构体属性类型用来封装实例的属性数据，Tag中类型指定迁移到数据库表中字段的类型。

迁移用的很少。主要是理解迁移的原理和作用

// 迁移后，主键默认不为空，其他字段默认都是能为空的
type Student struct {
	ID       int       // 缺省主键bigint AUTO_INCREMENT
	Name     string    `gorm:"not null;type:varchar(48);comment:姓名"`
	Age      byte      // byte=>tinyint unsigned
	Birthday time.Time // datetime
	Gender   byte      `gorm:"type:tinyint"`
}

 CREATE TABLE `students` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(48) NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned DEFAULT NULL,
  `birthday` datetime(3) DEFAULT NULL,
  `gender` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci   |

新增

package main

import (
	"fmt"
	"log"
	"time"

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

var db *gorm.DB

func init() {
	var err error
	dsn := "my_root:123@tcp(192.168.131.12:3306)/world?charset=utf8mb4&parseTime=true&loc=Local"
	db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info), //日志级别，默认silent
	})
	if err != nil {
		log.Panic(err)
	}
	fmt.Println(db.Select("1"))
}

type Student struct {
	ID       int    // 缺省主键bigint AUTO_INCREMENT
	Name     string `gorm:"not null;type:varchar(48);comment:姓名"`
	Age      byte   // byte=>tinyint unsigned
	Birthday string // datetime
	Gender   byte   `gorm:"type:tinyint"`
}

func main() {
	t := time.Now().Format("2006-01-02 15:04:05")
	s := Student{Name: "Tom", Age: 30, Birthday: t}
	fmt.Println(s)

	//新增一条
	result := db.Create(&s)
	fmt.Println(s)
	fmt.Println(result.Error)
	fmt.Println(result.RowsAffected)

	//新增多条
	result = db.Create([]*Student{&s, &s, &s})
	fmt.Println(s)
	fmt.Println(result.Error)
	fmt.Println(result.RowsAffected)
}

查询一条

package main

import (
	"fmt"
	"log"

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

var db *gorm.DB

func init() {
	var err error
	dsn := "my_root:123@tcp(192.168.131.12:3306)/world?charset=utf8mb4&parseTime=true&loc=Local"
	db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info), //日志级别，默认silent
	})
	if err != nil {
		log.Panic(err)
	}
	fmt.Println(db.Select("1"))
}

type Student struct {
	ID       int    // 缺省主键bigint AUTO_INCREMENT
	Name     string `gorm:"not null;type:varchar(48);comment:姓名"`
	Age      byte   // byte=>tinyint unsigned
	Birthday string // datetime
	Gender   byte   `gorm:"type:tinyint"`
}

func main() {
	var s Student
	db.Take(&s)

	db.First(&s)

	db.Last(&s)

	//根据ID查询
	db.First(&s, 4)
}

时间相关

1、时间类型错误

在连接中增加parseTime=true，这样时间类型就会自动转换为time.Time类型：dsn := "my_root:123@tcp(192.168.131.12:3306)/world?charset=utf8mb4&parseTime=true&loc=Local"
也可以Birthday string拿到Birthday字符串后，必要时转换成时间类型。

2、UTC时间

如果想存入的时间或读取的时间直接是当前时区时间，可以使用loc参数loc=Local。
如果loc=Local:

存入时，数据库字段中的时间就是当前时区的时间值
读取时，数据库字段中的时间就被解读为当前时区

查询所有

package main

import (
	"fmt"
	"log"
	"time"

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

var db *gorm.DB

func init() {
	var err error
	dsn := "my_root:123@tcp(192.168.131.12:3306)/world?charset=utf8mb4&parseTime=true&loc=Local"
	db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info), //日志级别，默认silent
	})
	if err != nil {
		log.Panic(err)
	}
	fmt.Println(db.Select("1"))
}

type Student struct {
	ID          int       // 缺省主键bigint AUTO_INCREMENT
	Name        string    `gorm:"not null;type:varchar(48);comment:姓名"`
	Age         byte      // byte=>tinyint unsigned
	Birthday    time.Time // datetime
	Gender      byte      `gorm:"type:tinyint"`
	GmtCreate   time.Time
	GmtModified time.Time
}

func (s Student) String() string {
	return fmt.Sprintf("%d %s %d %v %d %v %v",
		s.ID,
		s.Name,
		s.Age,
		s.Birthday,
		s.Gender,
		s.GmtCreate,
		s.GmtModified,
	)
}
func main() {
	var students []*Student
	db.Find(&students)
	fmt.Println(students)

}

distinct

package main

import (
	"fmt"
	"log"
	"time"

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

var db *gorm.DB

func init() {
	var err error
	dsn := "my_root:123@tcp(192.168.131.12:3306)/world?charset=utf8mb4&parseTime=true&loc=Local"
	db, err = gorm.Open(mysql.Open(dsn), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info), //日志级别，默认silent
	})
	if err != nil {
		log.Panic(err)
	}
}

type Student struct {
	ID          int       // 缺省主键bigint AUTO_INCREMENT
	Name        string    `gorm:"not null;type:varchar(48);comment:姓名"`
	Age         byte      // byte=>tinyint unsigned
	Birthday    time.Time // datetime
	Gender      byte      `gorm:"type:tinyint"`
	GmtCreate   time.Time `gorm:"autoCreateTime"`
	GmtModified time.Time `gorm:"autoUpdateTime"`
}

func (s Student) String() string {
	return fmt.Sprintf("%d %s %d %v %d %v %v",
		s.ID,
		s.Name,
		s.Age,
		s.Birthday,
		s.Gender,
		s.GmtCreate,
		s.GmtModified,
	)
}
func main() {
	var students []*Student
	db.Distinct("name").  //这里只返回了一个字段，需要重新定义个一个结构体接收
		Find(&students)
	for _, v := range students {
		fmt.Println(v)
	}

}

投影

var students []*Student
r := db.Select("id", "name", "age").Find(&students)
r := db.Select([]string{"id", "name", "age"}).Find(&students)
fmt.Println(students)

limit和offset

//链式编程需要注意limit的位置
var students []*Student
db.Limit(2).Offset(1).Select("id", "name", "age").Find(&students)

条件查询

1、字符串条件

var students []*Student
	db.Where("name=?", "Tom").Find(&students)
	db.Where("name<>?", "Tom").Find(&students)
	db.Where("name in ?", []string{"Tom", "John"}).Find(&students) //传入切片
	db.Where("name like ?", "T%").Find(&students)
	db.Where("name like ? and age>?", "T%", 20).Find(&students)
	db.Where("id between ? and ?", 15, 17).Find(&students) //范围[15,17]
	db.Where("id =? or id =?", 15, 17).Find(&students)

2、struct或map条件

var students []*Student
db.Where([]int{1, 4, 6}).Find(&students)                                 // where id int(1,4,6)
db.Where(&Student{}).Find(&students)                                     //SELECT * FROM `students`
db.Where(&Student{ID: 4, Name: "Tom"}).Find(&students)                   //SELECT * FROM `students` WHERE `students`.`id` = 4 AND `students`.`name` = 'Tom'
db.Where(&Student{Name: "Tom", Age: 30}, "name", "age").Find(&students)
db.Where(map[string]interface{}{"name": "Tom", "ID": 3}).Find(&students) //SELECT * FROM `students` WHERE `ID` = 3 AND `name` = 'Tom'
fmt.Println(students)

struct条件中出现了零值，例如 db.Where(&Student{Name: "Tom", Age: 0}) ，Age是零值，就不会出现在条件中。

3、Not

将Where换成Not即可

r := db.Not("id = ? or id = ?", 15, 17).Find(&students)
r := db.Not("name = ?", "Tom").Find(&students)

4、Or

or的用法和where一样
Where.Where是 and的关系，Where().Or()是Or关系

r := db.Where("name = ?", "Tom").Or("name=?", "Jerry").Find(&students)
r := db.Where("name = ?", "Tom").Or(&Student{Name: "Jerry"}).Find(&students)

排序

db.Order("id desc").Find(&students)
db.Order("name,id desc").Find(&students)
db.Order("name asc").Order("id desc").Find(&students)

分组

db.Group("name").Find(&students)
db.Group("id,name").Find(&students)          //GROUP BY id,name
db.Group("id").Group("name").Find(&students) //GROUP BY id,name

//聚合统计需要重新定义一个结构体来保存结果，但是要用Table指定表名

type Result struct {
	name  string
	count int
}

	var r = Result{}
	rows, _ := db.Table("students").Select("name,count(id) as c").Group("name").Rows()
	for rows.Next() {
		rows.Scan(&r.name, &r.count)
		fmt.Println(r)
	}


__________________________
type Result struct {
	Name  string
	Count int `gorm:"column:c"` //或使用 C int
}


	var rows = []*Result{}
	db.Table("students").Select("name,count(id) as c").Group("name").Having("c>3").Scan(&rows)

	for _, v := range rows {
		fmt.Println(*v)
	}

join

select b.Continent,a.Name,a.CountryCode,a.District from city a join country b on a.CountryCode = b.Code limit 10;
+---------------+----------------+-------------+----------+
| Continent     | Name           | CountryCode | District |
+---------------+----------------+-------------+----------+
| North America | Oranjestad     | ABW         | –        |
| Asia          | Kabul          | AFG         | Kabol    |
| Asia          | Qandahar       | AFG         | Qandahar |
| Asia          | Herat          | AFG         | Herat    |
| Asia          | Mazar-e-Sharif | AFG         | Balkh    |
| Africa        | Luanda         | AGO         | Luanda   |
| Africa        | Huambo         | AGO         | Huambo   |
| Africa        | Lobito         | AGO         | Benguela |
| Africa        | Benguela       | AGO         | Benguela |
| Africa        | Namibe         | AGO         | Namibe   |
+---------------+----------------+-------------+----------+

type Result struct {
	Continent   string
	Name        string
	CountryCode string
	District    string
}

var r Result
rows, _ := db.Table("city as a").Select("b.Continent,a.Name,a.CountryCode,a.District").
	Joins("join country b on a.CountryCode = b.Code").Limit(10).Rows()
for rows.Next() {
	rows.Scan(&r.Continent, &r.Name, &r.CountryCode, &r.District)
	fmt.Println(r)
}


_________________________

type Result struct {
	Continent   string
	Name        string
	CountryCode string
	District    string
}

func main() {
	var r = []*Result{}
	db.Table("city as a").Select("b.Continent,a.Name,a.CountryCode,a.District").
		Joins("join country b on a.CountryCode = b.Code").Limit(10).Scan(&r)
	fmt.Println(r)
    }

更新

先查后改：先查到一个实例，对这个实例属性进行修改，然后调用db.Save()方法保存。

db.Save()方法会保存所有字段，对于没有主键的实例相当于insert into,有主键的实例相当于update.

var student Student
db.First(&student)
fmt.Println(student)
student.Age += 100
student.Name = "hhhh"
fmt.Println(student)
db.Save(&student)
fmt.Println(student)

Update单个字段

db.Model(&Student{ID: 3}).Update("age", 100)
d := db.Model(&Student{}).Update("age", 100) //全表
fmt.Println(d.Error.Error())

var student Student
student.ID = 3
db.Model(&student).Update("age", 1000)

Updates更新多列

多个键值对，使用map或者结构体实例传参。
同样，没有指定ID或者where条件，是全表更新。

var student Student
db.Model(&student).Where("id < ?", 3).Updates(map[string]interface{}{"name": "dddd", "age": 10000})
db.Model(&Student{}).Where("id<?", 3).Updates(Student{Name: "jjj", Age: 88, Birthday: time.Now()})

删除

var student Student
db.Delete(&student)                    // DELETE FROM `students`
result := db.Delete(&Student{}, 1)     // WHERE `students`.`id` = 1
db.Delete(&Student{}, []int{2, 3})     // WHERE `students`.`id` IN (2,3)
db.Where("id<?", 6).Delete(&Student{}) //DELETE FROM `students` WHERE id<6
fmt.Println(result.Error)