go mysql使用

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
)

type User struct {
	id   int64
	name string
	age  int
}

func main() {
	// DSN:Data Source Name
	dsn := "root:@tcp(10.101.16.222:3306)/sql_test"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		fmt.Println("connect err",err)
		panic(err)
	}
	defer db.Close() // 注意这行代码要写在上面err判断的下面
	err = db.Ping()
	if err != nil {
		fmt.Println("ping err",err)

		return
	}
	//createDb(db)
	createTable(db)
	//insertRows(db,100)
	rows, err := queryRows(db)
	if err != nil {
		fmt.Println("queryRows err",err)

		return
	}

	for _, row := range rows {
		fmt.Printf("%#v\n",row)
	}
	updateRow(db,78)
	users, err := queryRows(db)
	if err != nil {
		fmt.Println("after update queryRows err",err)

		return
	}
	for _, row := range users {
		fmt.Printf("after update %#v\n",row)
	}
	txDemo(db)
}

func txDemo(db *sql.DB) {
	timeout, _ := context.WithTimeout(context.Background(), time.Duration(time.Second*10))
	tx, err := db.BeginTx(timeout, &sql.TxOptions{
		Isolation: sql.LevelRepeatableRead,
		ReadOnly:  false,
	})
	if err != nil {
		fmt.Printf("begin err%#v", err)
		return
	}
	_, err = db.Exec("update user set name ='txupdate' where id >= 0")
	querRowAndPrint(err, db)

	if err != nil {
		err.Error()
		fmt.Printf("update err %#v", err)
		tx.Rollback()
		//panic(err)
	}
	tx.Rollback()
	querRowAndPrint(err, db)
	_, err = db.Exec("update user set age =age +10 where id >= 0")
	if err != nil {
		fmt.Printf("update err %#v", err)
		tx.Rollback()
		panic(err)
	}

	querRowAndPrint(err, db)

	err = tx.Commit()
	if err!=nil {
		fmt.Println("commit error",err)
	}

}

func querRowAndPrint(err error, db *sql.DB) bool {
	rows, err := queryRows(db)
	if err != nil {
		fmt.Println("after update queryRows err", err)

		return true

	}
	for _, row := range rows {
		fmt.Printf("after update %#v\n", row)
	}
	return false
}
func insertRows(db *sql.DB, n int) {
	insertSql := "insert into user  (name,age) values(?,?)"
	for i := 0; i < n; i++ {
		var name = "houson" + string(i)
		exec, err := db.Exec(insertSql, name, i)
		if err!=nil {
			fmt.Println("insert err",err)
			return

		}
		fmt.Printf("insert succed%#v\n",exec)
	}
}
func queryRows(db *sql.DB) ([]User, error) {
	querySql := "select * from user where id >=0"
	query, err := db.Query(querySql)
	if err != nil {
		fmt.Println("query err", err)
		return nil, err
	}
	defer query.Close()
	var res = make([]User, 0, 10)
	for query.Next() {
		usr := User{}
		err := query.Scan(&usr.id,&usr.name,&usr.age)
		if err != nil {
			fmt.Println("scan err", err)
			return nil, err
		}
		res = append(res, usr)
	}
	return res, nil
}

func updateRow(db *sql.DB,id int64) error {
	updateSql:="update user set age=? where id =?"
	exec, err := db.Exec(updateSql, 1024, id)
	if err!= nil {
		fmt.Println("updated err",err)
		return err
	}
	affected, err := exec.RowsAffected()
	fmt.Printf("updated %#v,%#v \n",affected, err)
	return nil
}
func createTable(db *sql.DB) {
	exec, err := db.Exec("CREATE TABLE  IF NOT EXISTS `user`(" +
		"`id` bigint not null primary key," +
		"`name` varchar(10) not null default ''," +
		"`age` int default 0)engine=innodb auto_increment=1 default charset=utf8mb4;")
	if err!=nil {
		fmt.Println("create table err",err)
		panic(err)
	}
	fmt.Println(exec)

}

func createDb(db *sql.DB) {
	createSql := "CREATE DATABASE sql_test;"
	db.Exec(createSql)
	selectedSql := "use sql_test;"
	db.Exec(selectedSql)
}