golang操作mysql数据库（Go-SQL-Driver/MySQL）

下载安装，执行下面两个命令：

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

准备

建立数据库school和四张表并插入一些数据 

--学生表
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函数仅仅填入参数，不马上建立连接。后面query和exec时建立连接
	db, err := sql.Open("mysql","user:password@tcp(127.0.0.1:3306)/school")  //登录到数据库school中
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()  //避免频繁连接断开
}

可以进一步测试是否连接成功

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

查询

方式一、直接查询 （优先用这个方法）

        var	id, name string
	rows, err := db.Query("select s_id, s_name from student where s_sex = ?", "男")
        if err != nil{  //如果结果为0行，不会返回错误
            if driverErr, ok := err.(*mysql.MySQLError); ok { 
	        if driverErr.Number == 1045 {  //访问被拒绝
	    	    ... 
	        }
                if...
            }
        }
	defer rows.Close()
	for rows.Next() {  //next需要与scan配合完成读取，取第一行也要先next
		err := rows.Scan(&id, &name)
		if err != nil {  //每一次迭代检查错误是必要的
			log.Fatal(err)
		}
		log.Println(id, name)
	}
	err = rows.Err()  //返回迭代过程中出现的错误
	if err != nil {
		log.Fatal(err)
	}

方式二、预备陈述（mysql高并发工作时，此方法会产生过多连接出现性能问题）

此方法方便复用陈述句，且根据不同需要填入参数值

        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 string
	err = db.QueryRow("select s_name from student where s_id = ?", "01").Scan(&name)
		if err != nil {
		    if err == sql.ErrNoRows {  //如果未查询到对应字段则...
		    	...  
		    } else {
		    	log.Fatal(err)
	    	    }
	        }
	fmt.Println(name)
 
//方法二、预备陈述
	stmt, err := db.Prepare("select s_name from student where s_id = ?")
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()
	var name string
	err = 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)

 事务

同一个事务中只产生一个连接，预备陈述在高并发时不会有性能问题

        tx, err := db.Begin()  //开始事务
	if err != nil {
		log.Fatal(err)
	}
	defer tx.Rollback()  //发生异常事务回滚
	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()  //事务确认
	if err != nil {
		log.Fatal(err)
	}

Nullable类型

方法一、对于可为空的字段可以，如下处理

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

方法二、使用SQL中的函数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的取值返回空字符串

处理未知字段数和字段类型

rows, err := db.Query(`SELECT s_id, s_score FROM score WHERE s_id = ?`, "01")
	cols, err := rows.Columns() // Remember to check err afterwards
	vals := 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不设置默认为0，小于等于0表示没有限制，如果新设的maxopenconns小于maxidleconns，那么后者会减小以符合前者的值

func (db *DB) SetMaxIdleConns(n int)

如果n不设置默认为2，小于等于0表示不留闲置连接，如果新设的maxidleconns大于maxopenconns，那么前者会减小以符合后者的值

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

d小于等于0表示连接一直可复用

//每次完整的查询及取值后都需要close
db.SetMaxOpenConns(1)
rows, err := db.Query(`SELECT s_id, s_score FROM score WHERE s_id = ? `, "01")
do something...
//rows.Close()  //第一次查询如果没有close，由于最大连接数是1，下面查询时会发生阻塞
 
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 

返回连接池的状态

type DBStats struct {
   MaxOpenConnections int // Maximum number of open connections to the database.
 
   // Pool Status
   OpenConnections 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.
 
   // Counters
   WaitCount         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 main

 
import (
    //"fmt"
    "database/sql"
    _"github.com/Go-SQL-Driver/MySQL"

)

 
type userinfo struct {
    username    string
    departname  string
    created     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", "研发", "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 string
        if 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)
    }
}