下載安裝，執(zhí)行下面兩個命令：

• 下載：go get github.com/Go-SQL-Driver/MySQL
• 安裝：go install github.com/Go-SQL-Driver/MySQL

準備

建立數(shù)據(jù)庫school和四張表并插入一些數(shù)據(jù)?

--學(xué)生表 CREATE TABLE `Student`(`s_id` VARCHAR(20),`s_name` VARCHAR(20) NOT NULL DEFAULT '',`s_birth` VARCHAR(20) NOT NULL DEFAULT '',`s_sex` VARCHAR(10) NOT NULL DEFAULT '',PRIMARY KEY(`s_id`) ); --課程表 CREATE TABLE `Course`(`c_id` ?VARCHAR(20),`c_name` VARCHAR(20) NOT NULL DEFAULT '',`t_id` VARCHAR(20) NOT NULL,PRIMARY KEY(`c_id`) ); --教師表 CREATE TABLE `Teacher`(`t_id` VARCHAR(20),`t_name` VARCHAR(20) NOT NULL DEFAULT '',PRIMARY KEY(`t_id`) ); --成績表 CREATE TABLE `Score`(`s_id` VARCHAR(20),`c_id` ?VARCHAR(20),`s_score` INT(3),PRIMARY KEY(`s_id`,`c_id`) );?

登錄mysql

import ("database/sql"_"github.com/Go-SQL-Driver/MySQL") func main() { //open函數(shù)僅僅填入?yún)?shù)，不馬上建立連接。后面query和exec時建立連接db, err := sql.Open("mysql","user:password@tcp(127.0.0.1:3306)/school") //登錄到數(shù)據(jù)庫school中if err != nil {log.Fatal(err)}defer db.Close() //避免頻繁連接斷開 }

可以進一步測試是否連接成功

err = db.Ping() if err != nil {// do something here }

查詢

方式一、直接查詢 （優(yōu)先用這個方法）

var id, name stringrows, err := db.Query("select s_id, s_name from student where s_sex = ?", "男")if err != nil{ //如果結(jié)果為0行，不會返回錯誤if driverErr, ok := err.(*mysql.MySQLError); ok { if driverErr.Number == 1045 { //訪問被拒絕... }if...}}defer rows.Close()for rows.Next() { //next需要與scan配合完成讀取，取第一行也要先nexterr := rows.Scan(&id, &name)if err != nil { //每一次迭代檢查錯誤是必要的log.Fatal(err)}log.Println(id, name)}err = rows.Err() //返回迭代過程中出現(xiàn)的錯誤if err != nil {log.Fatal(err)}

方式二、預(yù)備陳述（mysql高并發(fā)工作時，此方法會產(chǎn)生過多連接出現(xiàn)性能問題）

此方法方便復(fù)用陳述句，且根據(jù)不同需要填入?yún)?shù)值

stmt, err := db.Prepare("select s_id, s_name from student where s_sex = ?")if err != nil {log.Fatal(err)}defer stmt.Close()rows, err := stmt.Query("男")if err != nil {log.Fatal(err)}defer rows.Close()for rows.Next() {.......}

方式三、只選取一行時

//方法一、直接讀取var name stringerr = db.QueryRow("select s_name from student where s_id = ?", "01").Scan(&name)if err != nil {if err == sql.ErrNoRows { //如果未查詢到對應(yīng)字段則...... } else {log.Fatal(err)}}fmt.Println(name)//方法二、預(yù)備陳述stmt, err := db.Prepare("select s_name from student where s_id = ?")if err != nil {log.Fatal(err)}defer stmt.Close()var name stringerr = stmt.QueryRow("01").Scan(&name)if err != nil {...}fmt.Println(name)

修改（INSERT,?UPDATE,?DELETE）

stmt, err := db.Prepare("INSERT INTO teacher(t_id,t_name) VALUES(?,?)")if err != nil {log.Fatal(err)}res, err := stmt.Exec("04","孔子") //mysql中的NULL類型，可用nil賦值if err != nil {log.Fatal(err)}lastId, err := res.LastInsertId() //LastInsertId只在自增列時有效if err != nil {log.Fatal(err)}rowCnt, err := res.RowsAffected()if err != nil {log.Fatal(err)}log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)

?事務(wù)

同一個事務(wù)中只產(chǎn)生一個連接，預(yù)備陳述在高并發(fā)時不會有性能問題

tx, err := db.Begin() //開始事務(wù)if err != nil {log.Fatal(err)}defer tx.Rollback() //發(fā)生異常事務(wù)回滾stmt, err := db.Prepare("INSERT INTO teacher(t_id,t_name) VALUES(?,?)")if err != nil {log.Fatal(err)}defer stmt.Close()_, err = stmt.Exec("05","老子")if err != nil {log.Fatal(err)}_, err = stmt.Exec("06","墨子")if err != nil {log.Fatal(err)}err = tx.Commit() //事務(wù)確認if err != nil {log.Fatal(err)}

Nullable類型

方法一、對于可為空的字段可以，如下處理

for rows.Next() {var s sql.NullStringerr := rows.Scan(&s)// check errif s.Valid {fmt.Println(s.String)} else {fmt.Println("NULL")} }

方法二、使用SQL中的函數(shù)COALESCE()

rows, err := db.Query(`SELECT s_id, COALESCE(s_name, '') FROM STUDENT WHERE s_id = ?`, "01") //如果選到s_id="01"時s_name為NULL時，s_name的取值返回空字符串

處理未知字段數(shù)和字段類型

rows, err := db.Query(`SELECT s_id, s_score FROM score WHERE s_id = ?`, "01")cols, err := rows.Columns() // Remember to check err afterwardsvals := make([]interface{}, len(cols))for i := range cols {vals[i] = new(sql.RawBytes)}for rows.Next() {err = rows.Scan(vals...)for _,i := range vals{s := string(*i.(*sql.RawBytes))fmt.Printf("%s ",s)}fmt.Println()}

連接池

func (db *DB) SetMaxOpenConns(n int)

如果n不設(shè)置默認為0，小于等于0表示沒有限制，如果新設(shè)的maxopenconns小于maxidleconns，那么后者會減小以符合前者的值

func (db *DB) SetMaxIdleConns(n int)

如果n不設(shè)置默認為2，小于等于0表示不留閑置連接，如果新設(shè)的maxidleconns大于maxopenconns，那么前者會減小以符合后者的值

func (db *DB) SetConnMaxLifetime(d time.Duration)

d小于等于0表示連接一直可復(fù)用

//每次完整的查詢及取值后都需要close db.SetMaxOpenConns(1) rows, err := db.Query(`SELECT s_id, s_score FROM score WHERE s_id = ? `, "01") do something... //rows.Close() //第一次查詢?nèi)绻麤]有close，由于最大連接數(shù)是1，下面查詢時會發(fā)生阻塞rows, err = db.Query(`SELECT s_id, s_score FROM score WHERE s_id = ? `, "01") do something... rows.Close()//在rows.close之后連接斷開，無法再使用rows.Columns()、rows.Next()等操作取值。 func (db *DB) Stats() DBStats

返回連接池的狀態(tài)

type DBStats struct {MaxOpenConnections int // Maximum number of open connections to the database.// Pool StatusOpenConnections int // The number of established connections both in use and idle.InUse int // The number of connections currently in use.Idle int // The number of idle connections.// CountersWaitCount int64 // The total number of connections waited for.WaitDuration time.Duration // The total time blocked waiting for a new connection.MaxIdleClosed int64 // The total number of connections closed due to SetMaxIdleConns.MaxLifetimeClosed int64 // The total number of connections closed due to SetConnMaxLifetime. }

其他測試代碼

package?mainimport?(//"fmt""database/sql"_"github.com/Go-SQL-Driver/MySQL")type?userinfo?struct?{username??? stringdepartname? stringcreated???? string }func?main(){db, err := sql.Open("mysql",?"root:111111@tcp(127.0.0.1:3306)/test?charset=utf8")checkErr(err)//insert//stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")//checkErr(err)//res, err := stmt.Exec("zhja", "研發(fā)", "2016-06-17")//checkErr(err)//id, err := res.LastInsertId()//checkErr(err)//fmt.Println(id)?//result, err := db.Exec("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)","lily","銷售","2016-06-21")//checkErr(err)//ids, err := result.LastInsertId()//fmt.Println(ids)//db.Exec("DELETE FROM userinfo WHERE uid=?", 1)//checkErr(err)//stmt, err := db.Prepare("DELETE FROM userinfo WHERE uid=?")//stmt.Exec(2)//var username, departname, created string//err = db.QueryRow("SELECT username,departname,created FROM userinfo WHERE uid=?", 3).Scan(&username, &departname, &created)//fmt.Println(username)//fmt.Println(departname)//fmt.Println(created)rows, err := db.Query("SELECT username,departname,created FROM userinfo WHERE username=?",?"zhja")checkErr(err)for?rows.Next() {var?username, departname, created stringif?err := rows.Scan(&username, &departname, &created); err == nil {fmt.Println(err)}fmt.Println(username)fmt.Println(departname)fmt.Println(created)}tx, err := db.Begin()checkErr(err)stmt, err1 := tx.Prepare("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)")checkErr(err1)_, err2 := stmt.Exec("test",?"測試",?"2016-06-20")checkErr(err2)//err3 := tx.Commit()err3 := tx.Rollback()checkErr(err3)}func?checkErr(err error){if?err != nil {panic(err)} }

?

總結(jié)

以上是生活随笔為你收集整理的golang操作mysql数据库（Go-SQL-Driver/MySQL）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。