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

时间：2025-02-27 14:15:50

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

  • 下载：go get /Go-SQL-Driver/MySQL
  • 安装：go install /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"
        _"/Go-SQL-Driver/MySQL"
)

func main() {
//open函数仅仅填入参数，不马上建立连接。后面query和exec时建立连接
	db, err := ("mysql","user:password@tcp(127.0.0.1:3306)/school")  //登录到数据库school中
	if err != nil {
		(err)
	}
	defer ()  //避免频繁连接断开
}

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

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

查询

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

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

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

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

        stmt, err := ("select s_id, s_name from student where s_sex = ?")
	if err != nil {
		(err)
	}
	defer ()
	rows, err := ("男")
	if err != nil {
		(err)
	}
	defer ()
	for () {
	        .......
	}

方式三、只选取一行时

//方法一、直接读取
        var name string
	err = ("select s_name from student where s_id = ?", "01").Scan(&name)
		if err != nil {
		    if err ==  {  //如果未查询到对应字段则...
		    	...  
		    } else {
		    	(err)
	    	    }
	        }
	(name)
 
//方法二、预备陈述
	stmt, err := ("select s_name from student where s_id = ?")
	if err != nil {
		(err)
	}
	defer ()
	var name string
	err = ("01").Scan(&name)
	if err != nil {
		...
	}
	(name)

修改（INSERT, UPDATE, DELETE）

        stmt, err := ("INSERT INTO teacher(t_id,t_name) VALUES(?,?)")
	if err != nil {
		(err)
	}
	res, err := ("04","孔子") //mysql中的NULL类型，可用nil赋值
	if err != nil {
		(err)
	}
	lastId, err := ()  //LastInsertId只在自增列时有效
	if err != nil {
		(err)
	}
	rowCnt, err := ()
	if err != nil {
		(err)
	}
	("ID = %d, affected = %d\n", lastId, rowCnt)

 事务

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

        tx, err := ()  //开始事务
	if err != nil {
		(err)
	}
	defer ()  //发生异常事务回滚
	stmt, err := ("INSERT INTO teacher(t_id,t_name) VALUES(?,?)")
	if err != nil {
		(err)
	}
	defer ()
	_, err = ("05","老子")
	if err != nil {
		(err)
	}
	_, err = ("06","墨子")
	if err != nil {
		(err)
	}
	err = ()  //事务确认
	if err != nil {
		(err)
	}

Nullable类型

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

for () {
	var s 
	err := (&s)
	// check err
	if  {
	   ()
	} else {
	   ("NULL")
	}
}

方法二、使用SQL中的函数COALESCE()

	rows, err := (`SELECT s_id, COALESCE(s_name, '') FROM STUDENT WHERE s_id = ?`, "01")
//如果选到s_时s_name为NULL时，s_name的取值返回空字符串

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

rows, err := (`SELECT s_id, s_score FROM score WHERE s_id = ?`, "01")
	cols, err := () // Remember to check err afterwards
	vals := make([]interface{}, len(cols))
	for i := range cols {
		vals[i] = new()
	}
	for () {
		err = (vals...)
		for _,i := range vals{
			s := string(*i.(*))
			("%s ",s)
		}
		()
	}

连接池

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 )

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

//每次完整的查询及取值后都需要close
(1)
rows, err := (`SELECT s_id, s_score FROM score WHERE s_id = ? `, "01")
do something...
//()  //第一次查询如果没有close，由于最大连接数是1，下面查询时会发生阻塞
 
rows, err = (`SELECT s_id, s_score FROM score WHERE s_id = ? `, "01")
do something...
()
 
//在之后连接断开，无法再使用()、()等操作取值。
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       // 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"
    _"/Go-SQL-Driver/MySQL"

)

 
type userinfo struct {
    username    string
    departname  string
    created     string
}

 

func main(){
    db, err := ("mysql", "root:111111@tcp(127.0.0.1:3306)/test?charset=utf8")
    checkErr(err)

 
    //insert
    //stmt, err := ("INSERT userinfo SET username=?,departname=?,created=?")
    //checkErr(err)

 
    //res, err := ("zhja", "研发", "2016-06-17")
    //checkErr(err)
 

    //id, err := ()
    //checkErr(err)
 

    //(id) 

    //result, err := ("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)","lily","销售","2016-06-21")
    //checkErr(err)
 

    //ids, err := ()
    //(ids)


    //("DELETE FROM userinfo WHERE uid=?", 1)
    //checkErr(err)

 
    //stmt, err := ("DELETE FROM userinfo WHERE uid=?")
    //(2)

 
    //var username, departname, created string

    //err = ("SELECT username,departname,created FROM userinfo WHERE uid=?", 3).Scan(&username, &departname, &created)
    //(username)
    //(departname)
    //(created)

    rows, err := ("SELECT username,departname,created FROM userinfo WHERE username=?", "zhja")

    checkErr(err)

    for () {
        var username, departname, created string
        if err := (&username, &departname, &created); err == nil {
            (err)
        }

        (username)
        (departname)
        (created)
    }

    tx, err := ()
    checkErr(err)

    stmt, err1 := ("INSERT INTO userinfo (username, departname, created) VALUES (?, ?, ?)")
    checkErr(err1)

    _, err2 := ("test", "测试", "2016-06-20")
    checkErr(err2)

    //err3 := ()
    err3 := ()
    checkErr(err3)

}


func checkErr(err error){
    if err != nil {
        panic(err)
    }
}

相关文章