Mysql+Go 预处理和事务

Mysql预处理和事务

golang

Mysql预处理和数据库事务相结合，并用uuid作唯一标识。

package main

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

	_ "github.com/go-sql-driver/mysql"
	"github.com/google/uuid" // 使用第三方库来生成 UUID
)

type Student struct {
	ID   string // 使用 string 类型存储 UUID
	Name string
	Age  int
}

func main() {
	db, err := sql.Open("mysql", "username:password@tcp(localhost:3306)/dbname")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	err = performOperations(db)
	if err != nil {
		log.Fatal(err)
	}
}

func performOperations(db *sql.DB) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer func() {
		if err != nil {
			tx.Rollback()
			return
		}
		err = tx.Commit()
	}()

	// 生成 UUID
	studentID := uuid.New().String()

	err = insertStudent(tx, studentID, "John Doe", 25)
	if err != nil {
		return err
	}

	err = updateStudentAge(tx, studentID, 26)
	if err != nil {
		return err
	}

	student, err := queryStudent(tx, studentID)
	if err != nil {
		return err
	}
	fmt.Printf("Student ID: %s, Name: %s, Age: %d\n", student.ID, student.Name, student.Age)

	err = deleteStudent(tx, studentID)
	if err != nil {
		return err
	}

	return nil
}

func insertStudent(tx *sql.Tx, id string, name string, age int) error {
	insertStmt, err := tx.Prepare("INSERT INTO students(id, name, age) VALUES(?, ?, ?)")
	if err != nil {
		return err
	}
	defer insertStmt.Close()

	_, err = insertStmt.Exec(id, name, age)
	if err != nil {
		return err
	}

	fmt.Printf("Inserted row with ID %s\n", id)

	return nil
}

func updateStudentAge(tx *sql.Tx, id string, newAge int) error {
	updateStmt, err := tx.Prepare("UPDATE students SET age=? WHERE id=?")
	if err != nil {
		return err
	}
	defer updateStmt.Close()

	_, err = updateStmt.Exec(newAge, id)
	if err != nil {
		return err
	}
	fmt.Printf("Updated age for student with ID %s\n", id)

	return nil
}

func queryStudent(tx *sql.Tx, id string) (Student, error) {
	queryStmt, err := tx.Prepare("SELECT id, name, age FROM students WHERE id=?")
	if err != nil {
		return Student{}, err
	}
	defer queryStmt.Close()

	var student Student
	err = queryStmt.QueryRow(id).Scan(&student.ID, &student.Name, &student.Age)
	if err != nil {
		return Student{}, err
	}

	return student, nil
}

func deleteStudent(tx *sql.Tx, id string) error {
	deleteStmt, err := tx.Prepare("DELETE FROM students WHERE id=?")
	if err != nil {
		return err
	}
	defer deleteStmt.Close()

	_, err = deleteStmt.Exec(id)
	if err != nil {
		return err
	}
	fmt.Printf("Deleted student with ID %s\n", id)

	return nil
}