Gorm

2023-03-04

1、安装mysql驱动

go get -u github.com/go-sql-driver/mysql

2、远程连接DevClound个人服务器的数据库

/*
@author: louris
@since: 2022/9/24
@desc: //TODO
*/

package mysqlSyntax

import (
	"database/sql"
	"strings"

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

const (
	userName = "root"
	passWord = "1234"      // 数据库mysql的登录密码
	ip       = "127.0.0.1" // 这里改为DevClound个人服务器ip
	port     = "3306"
	dbName   = "go_db"
)

// Connect mysq数据库连接
func Connect() *sql.DB {
	// dataSouceName: "用户名:密码@tcp(IP:port)/数据库名?...."
	path := strings.Join([]string{
		userName, ":", passWord, "@tcp(", ip, ":", port,
		")/", dbName, "?charset=utf8&parseTime=True",
	}, "")
	log.Println(path)
	//db, err := sql.Open("mysql", "root:ROot1234567&@tcp(9.134.5.247:3306)/go_db?charset=utf8&parseTime=True")
	db, err := sql.Open("mysql", path)
	if err != nil {
		log.Fatal("database connect failed!", err)
	}

	// 设置数据库最大连接数
	db.SetConnMaxLifetime(10)
	// 设置数据库最大闲置连接数
	db.SetMaxIdleConns(5)
	// 验证连接
	if err := db.Ping(); err != nil {
		log.Fatal("database ping failed!", err)
	}
	return db
}

/*

CREATE TABLE user_tb(
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    age INTEGER
);

*/

// User 数据库表user_tb的实体数据结构
type User struct {
	ID       int32
	UserName string
	Age      int32
}

// QueryOneRecord 查询单条数据
func QueryOneRecord(db *sql.DB, id int32) {
	user := User{}
	rows := db.QueryRow("select * from user_tb where id = ?", id)
	// rows.Scan 用于把读取的数据赋值到User对象的属性上，注意字段顺序，按照表定义顺序
	err := rows.Scan(&user.ID, &user.UserName, &user.Age)
	if err != nil {
		log.Fatal(err)
	}

	// 延迟到函数结束关闭连接
	// defer db.Close()
	log.Println("单条数据结果：", user)
}

// QueryMoreRecords @Description: 查询多条记录
func QueryMoreRecords(db *sql.DB) {
	rows, err := db.Query("select * from user_tb where id > ?", 0)
	if err != nil {
		log.Fatal(err)
	}
	users := []User{}
	for rows.Next() {
		user := User{}
		if err := rows.Scan(&user.ID, &user.UserName, &user.Age); err != nil {
			log.Fatal(err)
		}
		users = append(users, user)
	}
	log.Println("查询多条数据：\n", users)
	// defer db.Close()
}

// UpdateRecords 更删改查
func UpdateRecords(db *sql.DB, sql string, args ...interface{}) {
	result, err := db.Exec(sql, args...)
	if err != nil {
		log.Fatal(err)
	}

	rows, _ := result.RowsAffected()
	id, _ := result.LastInsertId()
	log.Println("受影响行数：", rows)
	log.Println("自增id：", id)
}

// UpdateDataWithTransaction 事务
func UpdateDataWithTransaction(db *sql.DB) {
	// 开始事务
	tx, err := db.Begin()
	if err != nil {
		log.Fatal(err)
	}
	UpdateRecords(db, "update user_tb set age=40 where id = ?", 1)
	UpdateRecords(db, "update user_tb set age=70 where id = ?", 2)
	// 提交事务
	if e := tx.Commit(); e != nil {
		// 报错则回滚
		err := tx.Rollback()
		if err != nil {
			log.Fatal("事务回滚失败!")
		}
	}
	// defer db.Close()
}

func TestMysql() {
	db := Connect()
	// QueryOneRecord(db, 1)
	QueryMoreRecords(db)
	UpdateDataWithTransaction(db)
	QueryMoreRecords(db)
	defer db.Close()
}

3、GORM

/*
Package gormSyntax test
@author: louris
@since: 2022/9/25
@desc: //TODO
*/
package gormSyntax

import (
	"errors"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
	"log"
	"strings"
)

const (
	userName  = "root"
	passWord  = "1234"
	ip        = "127.0.0.1"
	port      = "3306"
	dbName    = "go_db"
	tableName = "user_tb"
)

// User 数据库表user_tb的实体数据结构
type User struct {
	ID       int32  `gorm:"column:id;primaryKey;<-:create"`
	UserName string `gorm:"column:username;not null"`
	Age      int32  `gorm:"column:age;not null"`
}

// Connect 数据库链接
// @return *gorm.DB
func Connect() *gorm.DB {
	// dataSourceName: "用户名:密码@tcp(IP:port)/数据库名?...."
	path := strings.Join([]string{
		userName, ":", passWord, "@tcp(", ip, ":", port,
		")/", dbName, "?charset=utf8&parseTime=True",
	}, "")
	log.Println(path)

	db, err := gorm.Open(mysql.Open(path), &gorm.Config{})
	if err != nil {
		log.Fatal("Failed to connect database!", err)
	}
	return db
}

// InsertOneRecord 插入记录
// @param db
func InsertOneRecord(db *gorm.DB, user User) {

	//result := db.Table(tableName).Create(&user)
	result := db.Raw("INSERT INTO ? (username, age) VALUES (?, ?)",
		clause.Table{Name: tableName}, "lourisxu", 27).Scan(&user)

	if result.Error != nil {
		log.Fatal("return the error：", result.Error)
	}
	log.Println("return the primary key of inserted data：", user.ID)
	log.Println("return the rows of affected：", result.RowsAffected)
}

// InsertMoreRecords 批量插入数据
// @param db
// @param users
func InsertMoreRecords(db *gorm.DB, users []User) {
	result := db.Table(tableName).CreateInBatches(users, len(users))

	if result.Error != nil {
		log.Fatal("return the error：", result.Error)
	}
	log.Println("return the rows of affected", result.RowsAffected)
}

// InsertMoreRecordsByMap 通过map批量插入数据
// association不会被调用，且主键也不会自动填充
// @param db
// @param userMap
func InsertMoreRecordsByMap(db *gorm.DB, userMap []map[string]interface{}) {
	result := db.Model(&User{}).Create(userMap)
	if result.Error != nil {
		log.Fatal("return the error：", result.Error)
	}
	log.Println("return the rows of affected", result.RowsAffected)
}

// BeforeCreate 创建记录前钩子示例
// @receiver u
// @param db
// @return err
func (u *User) BeforeCreate(tx *gorm.DB) (err error) {
	log.Println("before create....")
	if u.UserName == "louris" {
		log.Println("good luck!")
	}
	return nil
}

// QueryOneRecord 查询一条记录
// @param db
// @return User
func QueryOneRecord(db *gorm.DB) User {
	user := User{}
	// SELECT * FROM `user_tb` ORDER BY `user_tb`.`id` LIMIT 1
	//result := db.First(&user)
	// SELECT * FROM `user_tb` WHERE `username` = "tom" LIMIT 1
	//result := db.Table(tableName).Where("username = ?", "louris").Scan(&user)
	result := db.Raw("SELECT * FROM ? WHERE username = ?", clause.Table{Name: tableName}, "louris").Scan(&user)

	// 行锁
	//result := db.Clauses(clause.Locking{Strength: "UPDATE"}).Where("username = ?", "tom").Scan(&user)

	if result.Error != nil {
		log.Fatal("return the error：", result.Error)
	}
	log.Println("return the rows of affected", result.RowsAffected)
	log.Println("result: ", user)
	return user
}

// QueryMoreRecords 查询所有数据
// @param db
func QueryMoreRecords(db *gorm.DB) {
	var users []User
	// find查询所有数据
	// result := db.Table("user_tb").Select("age").Limit(2).Find(&users)
	// result := db.Table("user_tb").Limit(10).Find(&users)
	result := db.Raw("SELECT * FROM ? LIMIT 10", clause.Table{Name: tableName}).Scan(&users)
	if result.Error != nil {
		log.Fatal("return the error：", result.Error)
	}
	log.Println("return the rows of affected", result.RowsAffected)
	for _, user := range users {
		log.Println(user)
	}
}

// UpdateRecord 更新记录
// @param db
func UpdateRecord(db *gorm.DB) {
	user := User{UserName: "tom"}
	result := db.Table(tableName).Where("username = ?", "tom").Update("username", "Tom")
	if result.Error != nil {
		log.Fatal("return the error：", result.Error)
	}
	log.Println("return the rows of affected", result.RowsAffected)
	log.Println("result: ", user)
}

// DeleteRecord 删除记录
// @param db
func DeleteRecord(db *gorm.DB) {
	var users []User
	result := db.Table(tableName).Clauses(clause.Returning{}).Where("username = ?", "louris").Delete(&users)
	if result.Error != nil {
		log.Fatal("return the error：", result.Error)
	}
	log.Println("return the rows of delete affected", result.RowsAffected)
	log.Println("result: ")
	for _, user := range users { // 并没有返回任何删除的数据???
		log.Println(user)
	}
}

// UpdateRecordsWithTransaction 事务更新记录
// @param db
func UpdateRecordsWithTransaction(db *gorm.DB, user User) {
	err := db.Transaction(func(tx *gorm.DB) error {
		//result := db.Table(tableName).Where("username = ?", "louris").Scan(&user)
		result := db.Raw("SELECT * FROM ? WHERE username = ? LIMIT 2", clause.Table{Name: tableName}, user.UserName).Scan(&user)
		if result.Error != nil {
			return result.Error
		}
		log.Println("return the rows of delete affected", result.RowsAffected)
		if result.RowsAffected > 1 {
			return errors.New("affected row more than 1")
		}
		res := db.Table(tableName).Create(&user)
		if res.Error != nil {
			return res.Error
		}
		if res.RowsAffected != 1 {
			return errors.New("insert sql affected row is not equal 1")
		}
		return nil
	})
	if err != nil {
		log.Fatal("transaction failed!", err)
	}
}

func TestGORM() {
	db := Connect()
	user := User{UserName: "louris", Age: 27}
	//InsertOneRecord(db, user)
	//InsertMoreRecords(db, []User{user, user})
	//QueryMoreRecords(db)
	//DeleteRecord(db)
	QueryMoreRecords(db)
	UpdateRecordsWithTransaction(db, user)
	QueryMoreRecords(db)
}

4、常见问题解决方案：

（1）ERROR 1130 —— Host XXX is not allowed to connect to this MySQL server

# 服务器登录mysql
mysql -hlocalhost -uroot -pyour_password

user mysql; # 选择数据库

select user,host from user;  # 一般会看到host为localhost或127.0.0.1

update user set host='%' where user='root' and host='localhost' limit 1;

（2）服务器防火墙是否需要放开3306端口
（注意：这里是个人DevCloud服务器，另外服务器使用注意高压线）

# 检查防火墙状态：
systemctl status firewalld  # 如果是dead状态，即防火墙未开启则需要开启防火墙

# 开启防火墙
systemctl stsart firewalld

# 开放3306端口
firewall-cmd --permanent --zone=public --add-port-3306/tcp

5、参考资料

官方文档 Go-MySQL-Driver
官方github
GORM官方指南