Gorm简单入门

Gorm简单入门

介绍简单的数据库连接和CRUD内容

0.基本配置

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

1.连接数据库

// 连接数据库
func initMySQL() (err error) {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=true&loc=Local"
	DB, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic("failed to connect database")
		return
	}

	err = DB.AutoMigrate(&Todo{})
	if err != nil {
		panic("failed to migrate")
		return
	}
	return
}

package main

import (
	"time"

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

type User struct {
	ID       uint
	Name     string
	Age      uint8
	BirthDay time.Time
}

func main() {
	dsn := "root:@tcp(127.0.0.1:3306)/gorm?charset=utf8mb4&parseTime=true&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic("failed to connect database")
	}

	err = db.AutoMigrate(&User{})
	if err != nil {
		panic("failed to migrate")
	}

	user := User{Name: "jinzhu", Age: 18, BirthDay: time.Now()}

	db.Create(&user)
}

也可以批量创建

users := []User{
		{Name: "hjr", Age: 18, BirthDay: time.Now()},
		{Name: "csn", Age: 19, BirthDay: time.Now()},
		{Name: "cqh", Age: 20, BirthDay: time.Now()},
	}
	db.Create(users)

根据Map创建

db.Model(&User{}).Create([]map[string]interface{}{
		{"Name": "hjr", "Age": 18},
		{"Name": "csn", "Age": 19},
	})

2. 查询

一般查询

//根据主键查询第一条记录
db.First(&user)
//根据主键查询最后一条记录
db.Last(&user)
//查询id在1, 2, 3之间的记录
db.Find(&user, []int{1, 2, 3})

Where条件

// Get first matched record
db.Where("name = ?", "jinzhu").First(&user)
 SELECT * FROM users WHERE name = 'jinzhu' limit 1;

// Get all matched records
db.Where("name = ?", "jinzhu").Find(&users)
 SELECT * FROM users WHERE name = 'jinzhu';

// <>
db.Where("name <> ?", "jinzhu").Find(&users)
 SELECT * FROM users WHERE name <> 'jinzhu';

// IN
db.Where("name IN (?)", []string{"jinzhu", "jinzhu 2"}).Find(&users)
 SELECT * FROM users WHERE name in ('jinzhu','jinzhu 2');

// LIKE
db.Where("name LIKE ?", "%jin%").Find(&users)
 SELECT * FROM users WHERE name LIKE '%jin%';

// AND
db.Where("name = ? AND age >= ?", "jinzhu", "22").Find(&users)
 SELECT * FROM users WHERE name = 'jinzhu' AND age >= 22;

// Time
db.Where("updated_at > ?", lastWeek).Find(&users)
 SELECT * FROM users WHERE updated_at > '2000-01-01 00:00:00';

// BETWEEN
db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&users)
 SELECT * FROM users WHERE created_at BETWEEN '2000-01-01 00:00:00' AND '2000-01-08 00:00:00';

Struct & Map查询

// Struct
db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
 SELECT * FROM users WHERE name = "jinzhu" AND age = 20 LIMIT 1;

// Map
db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
 SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

// 主键的切片
db.Where([]int64{20, 21, 22}).Find(&users)
 SELECT * FROM users WHERE id IN (20, 21, 22);

3.更新

保存所有字段

Save()默认会更新该对象的所有字段，即使没有赋值

单列使用Update进行更新

// Update with conditions
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's ID is `111`:
db.Model(&user).Update("name", "hello")
// UPDATE users SET name='hello', updated_at='2013-11-17 21:34:10' WHERE id=111;

// Update with conditions and model value
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更新

// Update attributes with `struct`, will only update non-zero fields
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;

// Update attributes with `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;

4.删除

删除记录时，请确保主键字段有值，GORM 会通过主键去删除记录，如果主键为空，GORM 会删除该 model 的所有记录。

//删除id为10的数据
db.Delete(&User{}, 10)
//删除id为1和2和3的数据
db.Delete(&User{}, []int{1, 2, 3})
//带条件的删除
db.Where("name = ?", "hjr").Delete(&User{})
//带 SQL 语句的删除
db.Set("gorm:delete_option", "OPTION (OPTIMIZE FOR UNKNOWN)").Delete(&User{})