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

func Open(driverName, dataSourceName string) (*DB, error)

import (
	"database/sql"

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

func main() {
	dsn := "user:password@tcp(127.0.0.1:3306)/dbname"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		panic(err)
	}
	defer db.Close()  

// 定義一個(gè)全局對(duì)象db
var db *sql.DB

// 定義一個(gè)初始化數(shù)據(jù)庫(kù)的函數(shù)
func initDB() (err error) {
	// DSN:Data Source Name
	dsn := "user:password@tcp(127.0.0.1:3306)/sql_test?charset=utf8mb4&parseTime=True"
	// 注意?。?！這里不要使用:=，我們是給全局變量賦值，然后在main函數(shù)中使用全局變量db
	db, err = sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	// 嘗試與數(shù)據(jù)庫(kù)建立連接
	err = db.Ping()
	if err != nil {
		return err
	}
	return nil
}

func main() {
	err := initDB() 
	if err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
		return
	}
}

func (db *DB) SetMaxOpenConns(n int)

func (db *DB) SetMaxIdleConns(n int)

CREATE DATABASE sql_test;

use sql_test;

CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT '',
    `age` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

type user struct {
	id   int
	age  int
	name string
}

func (db *DB) QueryRow(query string, args ...interface{}) *Row

// 查詢單條數(shù)據(jù)示例
func queryRowDemo() {
	sqlStr := "select id, name, age from user where id=?"
	var u user
	// 確保QueryRow之后調(diào)用Scan方法，否則持有的數(shù)據(jù)庫(kù)鏈接不會(huì)被釋放
	err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name, &u.age)
	if err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

// 查詢多條數(shù)據(jù)示例
func queryMultiRowDemo() {
	sqlStr := "select id, name, age from user where id > ?"
	rows, err := db.Query(sqlStr, 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	// 關(guān)閉rows釋放持有的數(shù)據(jù)庫(kù)鏈接
	defer rows.Close()

	// 循環(huán)讀取結(jié)果集中的數(shù)據(jù)
	for rows.Next() {
		var u user
		err := rows.Scan(&u.id, &u.name, &u.age)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
	}
}

func (db *DB) Exec(query string, args ...interface{}) (Result, error)

// 插入數(shù)據(jù)
func insertRowDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "王五", 38)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	theID, err := ret.LastInsertId() // 新插入數(shù)據(jù)的id
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新數(shù)據(jù)
func updateRowDemo() {
	sqlStr := "update user set age=? where id = ?"
	ret, err := db.Exec(sqlStr, 39, 3)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影響的行數(shù)
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

// 刪除數(shù)據(jù)
func deleteRowDemo() {
	sqlStr := "delete from user where id = ?"
	ret, err := db.Exec(sqlStr, 3)
	if err != nil {
		fmt.Printf("delete failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() // 操作影響的行數(shù)
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("delete success, affected rows:%d\n", n)
}

func (db *DB) Begin() (*Tx, error)

func (tx *Tx) Commit() error

func (tx *Tx) Rollback() error

// 事務(wù)操作示例
func transactionDemo() {
	tx, err := db.Begin() // 開(kāi)啟事務(wù)
	if err != nil {
		if tx != nil {
			tx.Rollback() // 回滾
		}
		fmt.Printf("begin trans failed, err:%v\n", err)
		return
	}
	sqlStr1 := "Update user set age=30 where id=?"
	ret1, err := tx.Exec(sqlStr1, 2)
	if err != nil {
		tx.Rollback() // 回滾
		fmt.Printf("exec sql1 failed, err:%v\n", err)
		return
	}
	affRow1, err := ret1.RowsAffected()
	if err != nil {
		tx.Rollback() // 回滾
		fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
		return
	}

	sqlStr2 := "Update user set age=40 where id=?"
	ret2, err := tx.Exec(sqlStr2, 3)
	if err != nil {
		tx.Rollback() // 回滾
		fmt.Printf("exec sql2 failed, err:%v\n", err)
		return
	}
	affRow2, err := ret2.RowsAffected()
	if err != nil {
		tx.Rollback() // 回滾
		fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
		return
	}

	fmt.Println(affRow1, affRow2)
	if affRow1 == 1 && affRow2 == 1 {
		fmt.Println("事務(wù)提交啦...")
		tx.Commit() // 提交事務(wù)
	} else {
		tx.Rollback()
		fmt.Println("事務(wù)回滾啦...")
	}

	fmt.Println("exec trans success!")
}