[Go][MySQL]使用方法

下載驅動

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

連接資料庫

package main

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

var db *sql.DB

//定義一個初始化資料庫的函數
func initDB() (err error) {
	//連接資料庫
	db, err = sql.Open("mysql", "root:root1234@tcp(127.0.0.1:3306)/hello")
	if err != nil {
		return err
	}
	//檢查連線
	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
	}
}

最大連線數

package main

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

func main() {
	db, err = sql.Open("mysql", "root:root1234@tcp(127.0.0.1:3306)/hello")
	if err != nil {
		return err
	}
	defer db.Close()
	db.DB().SetMaxOpenConns(100)
}

最大閒置連線數

package main

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

func main() {
	db, err = sql.Open("mysql", "root:root1234@tcp(127.0.0.1:3306)/hello")
	if err != nil {
		return err
	}
	defer db.Close()
	db.DB().SetMaxIdleConns(10)
}

查詢

package main

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

var db *sql.DB

type User struct {
	Uid   int
	Name  string
	Phone string
}

//定義一個全域變數
var u User

func queryRow() {
	//非常重要：確保QueryRow之後調用Scan方法，否則持有的資料庫連結不會被釋放
	err := db.QueryRow("select uid,name,phone from `user` where uid=?", 1).Scan(&u.Uid, &u.Name, &u.Phone)
	if err != nil {
		fmt.Printf("scan failed, err:%v\n", err)
		return
	}
	fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)
}

func main() {
	db, err = sql.Open("mysql", "root:root1234@tcp(127.0.0.1:3306)/hello")
	if err != nil {
		return err
	}
	queryRow()
}

查詢多條資料

package main

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

var db *sql.DB

type User struct {
	Uid   int
	Name  string
	Phone string
}

//定義一個全域變數
var u User

func queryMultiRow() {
	rows, err := db.Query("select uid,name,phone from `user` where uid > ?", 0)
	if err != nil {
		fmt.Printf("query failed, err:%v\n", err)
		return
	}
	//關閉rows釋放持有的資料庫鏈接
	defer rows.Close()
	//循環讀取結果集中的數據
	for rows.Next() {
		err := rows.Scan(&u.Uid, &u.Name, &u.Phone)
		if err != nil {
			fmt.Printf("scan failed, err:%v\n", err)
			return
		}
		fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)
	}
}

func main() {
	db, err = sql.Open("mysql", "root:root1234@tcp(127.0.0.1:3306)/hello")
	if err != nil {
		return err
	}
	queryMultiRow()
}

插入

package main

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

var db *sql.DB

type User struct {
	Uid   int
	Name  string
	Phone string
}

//定義一個全域變數
var u User

func insertRow() {
	ret, err := db.Exec("insert into user(username,phone) values (?,?)", "王五", 13988557766)
	if err != nil {
		fmt.Printf("insert failed, err:%v\n", err)
		return
	}
	uid, err := ret.LastInsertId() //取得新插入資料的uid
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success, the id is %d.\n", uid)
}

func main() {
	db, err = sql.Open("mysql", "root:root1234@tcp(127.0.0.1:3306)/hello")
	if err != nil {
		return err
	}
	insertRow()
}

更新

package main

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

var db *sql.DB

type User struct {
	Uid   int
	Name  string
	Phone string
}

//定義一個全域變數
var u User

func updateRow() {
	ret, err := db.Exec("update user set username=? where uid = ?", "张三", 3)
	if err != nil {
		fmt.Printf("update failed, err:%v\n", err)
		return
	}
	n, err := ret.RowsAffected() //操作影響的行數
	if err != nil {
		fmt.Printf("get RowsAffected failed, err:%v\n", err)
		return
	}
	fmt.Printf("update success, affected rows:%d\n", n)
}

func main() {
	db, err = sql.Open("mysql", "root:root1234@tcp(127.0.0.1:3306)/hello")
	if err != nil {
		return err
	}
	updateRow()
}

刪除

package main

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

var db *sql.DB

type User struct {
	Uid   int
	Name  string
	Phone string
}

//定義一個全域變數
var u User

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

func main() {
	db, err = sql.Open("mysql", "root:root1234@tcp(127.0.0.1:3306)/hello")
	if err != nil {
		return err
	}
	deleteRow()
}