Golang 操作RDBMS

Golang 通過 database/sql package實現了對RDBMS的使用，如MySQL, MSSQL,Oracle和Postgres,除了RDBMS外，也可使用NoSQL，如MongoDB、Redis，這裡提供了目前支持的driver SQLDrivers

SQL標準庫提供通用接口及常用方法用於訪問或操作數據庫。數據庫包包括連接池，正確使用可確保線程安全。數據庫包必須和具體數據庫驅動包一起使用。

以MySQL為例，go-sql-driver支援 database/sql，package本身也是使用go寫的

引用MySQL package

可以發現github.com/go-sql-driver/mysql 的alias是_，

import 下劃線（如：import _ github/demo）的作用：當導入一個package時，該package下的文件裡所有init()函數都會被執行，然而，有些時候我們並不需要把整個package都導入進來，僅僅是是希望它執行init()函數而已。這個時候就可以使用import _ 引用此package。

上面的mysql驅動中引入的就是mysqlpackage中各個init()方法，你無法通過package名來調用package中的其他函數。導入時，驅動的初始化函數會調用sql.Register將自己註冊在database/sqlpackage的全局變量sql.drivers中，以便以後通過sql.Open訪問。

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

設定MySQL連線

裡面有三個參數要記得設定
1.SetConnMaxLifetime：設置了連接可重用的最大時間，如果設定0或是小於0就是沒有生命週期，設定時間到期後這些連線就會廢棄無法重用，需要重新起連線。
2.SetMaxOpenConns：設定最大連線數，如果設定0或是小於0就是是無限大，但是基本上不會設定成無限大，因為db會收不下過量的連線rrrrrr
3.SetMaxIdleConns：設定閒置連線量，如果設定0或是小於0就是沒有閒置連線，一但作完了連線就不回連線池待下次取用直接廢棄掉，變成每次都要起新的連線，沒辦法節省資源

const (
	UserName     string = "root"
	Password     string = "password"
	Addr         string = "127.0.0.1"
	Port         int    = 3306
	Database     string = "test"
	MaxLifetime  int    = 10
	MaxOpenConns int    = 10
	MaxIdleConns int    = 10
)
    //組合sql連線字串
	conn := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s", UserName, Password, Addr, Port, Database)
	//連接MySQL
	DB, err := sql.Open("mysql", conn)
	if err != nil {
		fmt.Println("connection to mysql failed:", err)
		return
	}
    DB.SetConnMaxLifetime(time.Duration(MaxLifetime) * time.Second)
	DB.SetMaxOpenConns(MaxOpenConns)
	DB.SetMaxIdleConns(MaxIdleConns)

實作CRUD

CREATE TABLE

func CreateTable(DB *sql.DB) {
	sql := `CREATE TABLE IF NOT EXISTS cumstomer(
	id bigint(20) PRIMARY KEY AUTO_INCREMENT NOT NULL,
	username VARCHAR(64),
	password VARCHAR(64),
	status INT(4),
	created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
	); `

	if _, err := DB.Exec(sql); err != nil {
		fmt.Println("create table failed:", err)
		return
	}
	fmt.Println("create table successd")
}

INSERT

func AddCustomer(DB *sql.DB) {
	result, err := DB.Exec("insert INTO customer(username,password) values(?,?)", "test", "123456")
	if err != nil {
		fmt.Printf("Insert data failed,err:%v", err)
		return
	}
    //sql.Result 的LastInsertId()可取得AUTO_INCREMENT的值
	lastInsertID, err := result.LastInsertId()
	if err != nil {
		fmt.Printf("Get insert id failed,err:%v", err)
		return
	}
	fmt.Println("Insert data id:", lastInsertID)

    //RowsAffected() 影響的資料筆數，如果很嚴謹的寫法會判斷RowsAffected()是否與新增的資料筆數一致
	rowsaffected, err := result.RowsAffected() 
	if err != nil {
		fmt.Printf("Get RowsAffected failed,err:%v", err)
		return
	}
	fmt.Println("Affected rows:", rowsaffected)
}

UPDATE

func UpdateData(DB *sql.DB) {
	result, err := DB.Exec("UPDATE customer set password=? where id=?", "ggyy", 1)
	if err != nil {
		fmt.Printf("UPDATE failed,err:%v\n", err)
		return
	}
	fmt.Println("update data successd:", result)

	rowsaffected, err := result.RowsAffected()
	if err != nil {
		fmt.Printf("UPDATE RowsAffected failed,err:%v\n", err)
		return
	}
	fmt.Println("UPDATE Affected rows:", rowsaffected)
}

DELETE

func DeleteData(DB *sql.DB) {
	result, err := DB.Exec("delete from customer where id=?", 1)
	if err != nil {
		fmt.Printf("delete failed,err:%v\n", err)
		return
	}
	fmt.Println("delete data successd:", result)

	rowsaffected, err := result.RowsAffected()
	if err != nil {
		fmt.Printf("delete RowsAffected failed,err:%v\n", err)
		return
	}
	fmt.Println("delete Affected rows:", rowsaffected)
}

QUERY：
使用db.Query()來查詢db，取得Rows與err。
使用rows.Next()迭代讀取sql.Rows。
使用rows.Scan 取得一筆資料。
使用rows.Close()關閉sql.Rows，釋放掉連線。

var customer Customer
    //單筆資料
	row := DB.QueryRow("select id,username,password from customer where id=?", 2)
    //Scan對應的欄位與select語法的欄位順序一致
	if err := row.Scan(&customer.ID, &customer.Username, &customer.Password); err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	fmt.Printf("customer:%+v:", customer)
    
    //多筆資料
	rows, err := DB.Query("select id,username,password from customer")
    //記得要close掉連線，不然會一直卡連線
	defer rows.Close()
    //有些linter會擋掉上面寫法，可以使用下面的寫法
	// defer func() {
	// 		rows.Close()	
	// }()
	if err != nil {
		fmt.Printf("Query failed,err:%v\n", err)
		return
	}
	var customers []Customer
    //一筆一筆讀取
	for rows.Next() {
		var customer Customer
		err = rows.Scan(&customer.ID, &customer.Username, &customer.Password)
		if err != nil {
			fmt.Printf("Scan failed,err:%v\n", err)
			return
		}
		customers = append(customers, customer)
	}
	fmt.Printf("customers:%+v:", customers)

transaction

func transaction(DB *sql.DB) {
	tx, txErr := DB.Begin()
	if txErr != nil {
		fmt.Println("txErr:", txErr)
		return
	}
	for i := 100; i <= 120; i++ {
		res, execErr := tx.Exec("insert INTO customer(username,password) values(?,?)", i, i+i)
		if execErr != nil {
			tx.Rollback()
			return
		}
		rowsAffected, rowsErr := res.RowsAffected()
		if rowsErr != nil || rowsAffected != 1 {
			tx.Rollback()
			return
		}
	}
	tx.Commit()
}

關於連線池

以下是有關連線池的說明，重點是記得要close連線...不然會被DBA手刀衝過來打暈

只用sql.Open函式建立連線池，可是此時只是初始化了連線池，並沒有建立任何連線。連線建立都是惰性的，只有當你真正使用到連線的時候，連線池才會建立連線。連線池很重要，它直接影響著你的程式行為。
連線池的工作原來卻相當簡單。當你的函式(例如Exec，Query)呼叫需要訪問底層資料庫的時候，函式首先會向連線池請求一個連線。如果連線池有空閒的連線，則返回給函式。否則連線池將會建立一個新的連線給函式。一旦連線給了函式，連線則歸屬於函式。函式執行完畢後，要不把連線所屬權歸還給連線池，要麼傳遞給下一個需要連線的（Rows）物件，最後使用完連線的物件也會把連線釋放回到連線池。

db.Ping() 呼叫完畢後會馬上把連線返回給連線池。
db.Exec() 呼叫完畢後會馬上把連線返回給連線池，但是它返回的Result物件還保留這連線的引用，當後面的程式碼需要處理結果集的時候連線將會被重用。
db.Query() 呼叫完畢後會將連線傳遞給sql.Rows型別，當然後者迭代完畢或者顯示的呼叫.Close()方法後，連線將會被釋放回到連線池(<---踩雷點，如果沒close掉是不會釋放掉連線)
db.QueryRow()呼叫完畢後會將連線傳遞給sql.Row型別，當.Scan()方法呼叫之後把連線釋放回到連線池。
db.Begin() 呼叫完畢後將連線傳遞給sql.Tx型別物件，當.Commit()或.Rollback()方法呼叫後釋放連線。