使用 go-sql-driver 操作 mysql demo

package main

import (
	"database/sql"
	"fmt"
	// 初始化驱动
	_ "github.com/go-sql-driver/mysql"
	"log"
)

// 执行 SQL，可以用来执行不需要返回结果集的 SQL
func execSql(conn *sql.DB, sql string, args ...interface{}) {
	execResp, err := conn.Exec(sql, args...)
	if err != nil {
		log.Fatalf("exec sql [%s] failed, err: %v\n", sql, err)
	}
	// 查看受影响的行数
	fmt.Println(execResp.RowsAffected())
}

// 多行查询
func querySql(conn *sql.DB, sql string) {
	var (
		id   int
		name string
		age  int
	)
	queryResult, err := conn.Query(sql)
	if err != nil {
		log.Fatalf("query sql [%s] failed, err: %v\n", sql, err)
	}
	for queryResult.Next() {
		queryResult.Scan(&id, &name, &age)
		fmt.Printf("id: %d, name: %s, age: %d\n", id, name, age)
	}
}

// 单行查询
func queryOneSql(conn *sql.DB, sql string, args ...interface{}) {
	var (
		id   int
		name string
		age  int
	)
	query := conn.QueryRow(sql, args...)
	err := query.Scan(&id, &name, &age)
	if err != nil {
		log.Fatalf("exec query raw sql [%s] failed, err: %v", sql, err)
	}
	fmt.Printf("id: %d, name: %s, age: %d", id, name, age)
}

func changeMoney(tx *sql.Tx, accountId int, money float32) error {
	_, err := tx.Exec("update account set money = money + ? where id = ?", money, accountId)
	return err
}

// 事务示例
func execTrans(conn *sql.DB) {
	execSql(conn, `
		create table if not exists account(
			id int primary key auto_increment,
			name varchar(24),
			money float
		) engine=innodb default charset utf8mb4
	`)
	//execSql(conn, "insert into account(name, money) values('u1', 100)")
	//execSql(conn, "insert into account(name, money) values('u2', 100)")
	tx, err := conn.Begin()
	if err != nil {
		log.Fatalf("start transaction failed, err: %v", err)
	}
	err1 := changeMoney(tx, 1, -10)
	err2 := changeMoney(tx, 2, 10)
	if err1 == nil && err2 == nil {
		err := tx.Commit()
		if err != nil {
			log.Fatalf("commit transaction failed, err: %v", err)
		}
		fmt.Println("trans money success!")
	} else {
		err := tx.Rollback()
		if err != nil {
			log.Fatalf("rollback transaction failed, err: %v", err)
		}
		fmt.Println("trans money failed!")
	}
}

// 预解析 SQL
func prepareSql(conn *sql.DB) {
	sqlStr := "insert into t1(name, age) values(?, ?)"
	prepareStatment, err := conn.Prepare(sqlStr)
	if err != nil {
		log.Fatalf("prepare sql [%s] failed, err: %v", sqlStr, err)
	}
	for i := 0; i < 10; i++ {
		name := fmt.Sprintf("u%d", i)
		age := 10 + i
		_, err := prepareStatment.Exec(name, age)
		if err != nil {
			log.Fatalf("exec prepare statment with params [name: %s, age %d]", name, age)
		}
	}
}

func main() {
	dsn := "root:123456@tcp(172.18.80.77:3306)/golang?charset=utf8mb4&loc=PRC"
	conn, err := sql.Open("mysql", dsn)
	//关闭数据库连接
	defer conn.Close()
	if err != nil {
		log.Fatalf("open mysql conn failed, err: %v", err)
	}
	// 检查数据库是否可以正常建立连接
	err = conn.Ping()
	if err != nil {
		log.Fatalf("ping failed, err: %v", err)
	}

	execSql(conn, `
		create table if not exists t1(
			id int primary key auto_increment,
			name varchar(24),
			age tinyint
		) engine=innodb default charset utf8mb4
	`)
	execSql(conn, "insert into t1(name, age) values(?, ?);", "zze3", 18)
	querySql(conn, "select id, name, age from t1")
	queryOneSql(conn, "select id, name, age from t1 where id = ?", 2)
	execTrans(conn)
	prepareSql(conn)
}