go语言实现数据库访问

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› Go›Go教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

文章目录

新建工程项目Test-demo
在src下面新建文件mysql.go
下载驱动
引入包
新建数据库连接
操作数据库

新增
查询
修改
删除

其它定义方法
调用
输出结果
完整代码

前提新建数据库test，新建脚本

DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
  `autid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `departname` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `uid` varchar(255) DEFAULT NULL,
  `created` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`autid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

新建工程项目Test-demo

在src下面新建文件mysql.go

下载驱动

前提是配置好GOPATH，在cmd执行
go get github.com/go-sql-driver/mysql

引入包

import (
    "crypto/md5"  //md5加解密
    "database/sql" //数据库操作
    "encoding/hex" //转码
    "fmt"  //输出
    "strconv" //格式化
    "time"  //时间
    _ "github.com/go-sql-driver/mysql"  //mysql驱动
)

新建数据库连接

driver的格式为
user:[email protected](localhost:5555)/dbname?charset=utf8

const (
    db_driver = "root:[email protected](127.0.0.1:3306)/test?charset=utf8"
)
func OpenDB() (success bool, db *sql.DB) {
    var isOpen bool
    db, err := sql.Open("mysql", db_driver)
    if err != nil {
        isOpen = false
    } else {
        isOpen = true
    }
    checkErr(err)
    return isOpen, db
}

操作数据库

新增

func insertToDB(db *sql.DB) {
    uid := GetNowtimeMD5()
    nowTimeStr := GetTime()
    stmt, err := db.Prepare("insert userinfo set username=?,departname=?,created=?,password=?,uid=?")
    checkErr(err)
    res, err := stmt.Exec("zhang", "研发中心", nowTimeStr, "123456", uid)
    checkErr(err)
    id, err := res.LastInsertId()
    checkErr(err)
    if err != nil {
        fmt.Println("插入数据失败")
    } else {
        fmt.Println("插入数据成功：", id)
    }
}

查询

func QueryFromDB(db *sql.DB) {
    rows, err := db.Query("SELECT * FROM userinfo")
    checkErr(err)
    if err != nil {
        fmt.Println("error:", err)
    } else {
    }
    for rows.Next() {
        var uid string
        var username string
        var departmentname string
        var created string
        var password string
        var autid string
        checkErr(err)
        err = rows.Scan(&uid, &username, &departmentname, &created, &password, &autid)
        fmt.Println(autid)
        fmt.Println(username)
        fmt.Println(departmentname)
        fmt.Println(created)
        fmt.Println(password)
        fmt.Println(uid)
    }
 
}

修改


func UpdateDB(db *sql.DB, uid string) {
    stmt, err := db.Prepare("update userinfo set username=? where uid=?")
    checkErr(err)
    res, err := stmt.Exec("zhangqi", uid)
    affect, err := res.RowsAffected()
    fmt.Println("更新数据：", affect)
    checkErr(err)
}

删除

func DeleteFromDB(db *sql.DB, autid int) {
    stmt, err := db.Prepare("delete from userinfo where autid=?")
    checkErr(err)
    res, err := stmt.Exec(autid)
    affect, err := res.RowsAffected()
    fmt.Println("删除数据：", affect)
}

其它定义方法

func GetTime() string {
    const shortForm = "2006-01-02 15:04:05"
    t := time.Now()
    temp := time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), t.Second(), t.Nanosecond(), time.Local)
    str := temp.Format(shortForm)
    fmt.Println(t)
    return str
}
func GetMD5Hash(text string) string {
    haser := md5.New()
    haser.Write([]byte(text))
    return hex.EncodeToString(haser.Sum(nil))
}
func GetNowtimeMD5() string {
    t := time.Now()
    timestamp := strconv.FormatInt(t.UTC().UnixNano(), 10)
    return GetMD5Hash(timestamp)
}
func checkErr(errMasg error) {
    if errMasg != nil {
        panic(errMasg)
    }
}

调用

func main() {
    opend, db := OpenDB()
    if opend {
        fmt.Println("open success")
    } else {
        fmt.Println("open faile:")
    }
    //DeleteFromDB(db, 10)
    //DeleteFromDB(db, 1)
    //UpdateDB(db, 5)
    insertToDB(db)
    QueryFromDB(db)
    //UpdateUID(db, 5)
    //UpdateTime(db, 4)
    db.Close()
}

输出结果

完整代码

package main

import (
	"crypto/md5"
	"database/sql"
	"encoding/hex"
	"fmt"
	"strconv"
	"time"

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

const (
	db_driver = "root:[email protected](127.0.0.1:3306)/test?charset=utf8"
)

func main() {
	opend, db := OpenDB()
	if opend {
		fmt.Println("open success")
	} else {
		fmt.Println("open faile:")
	}
	//DeleteFromDB(db, 10)
	//DeleteFromDB(db, 1)
	//UpdateDB(db, 5)
	insertToDB(db)
	QueryFromDB(db)
	//UpdateUID(db, 5)
	//UpdateTime(db, 4)
	db.Close()

}

func OpenDB() (success bool, db *sql.DB) {
	var isOpen bool
	db, err := sql.Open("mysql", db_driver)
	if err != nil {
		isOpen = false
	} else {
		isOpen = true
	}
	checkErr(err)
	return isOpen, db
}

func insertToDB(db *sql.DB) {
	uid := GetNowtimeMD5()
	nowTimeStr := GetTime()
	stmt, err := db.Prepare("insert userinfo set username=?,departname=?,created=?,password=?,uid=?")
	checkErr(err)
	res, err := stmt.Exec("zhang", "研发中心", nowTimeStr, "123456", uid)
	checkErr(err)
	id, err := res.LastInsertId()
	checkErr(err)
	if err != nil {
		fmt.Println("插入数据失败")
	} else {
		fmt.Println("插入数据成功：", id)
	}
}

func QueryFromDB(db *sql.DB) {
	rows, err := db.Query("SELECT * FROM userinfo")
	checkErr(err)
	if err != nil {
		fmt.Println("error:", err)
	} else {
	}
	for rows.Next() {
		var uid string
		var username string
		var departmentname string
		var created string
		var password string
		var autid string
		checkErr(err)
		err = rows.Scan(&uid, &username, &departmentname, &created, &password, &autid)
		fmt.Println(autid)
		fmt.Println(username)
		fmt.Println(departmentname)
		fmt.Println(created)
		fmt.Println(password)
		fmt.Println(uid)
	}
}

func UpdateDB(db *sql.DB, uid string) {
	stmt, err := db.Prepare("update userinfo set username=? where uid=?")
	checkErr(err)
	res, err := stmt.Exec("zhangqi", uid)
	affect, err := res.RowsAffected()
	fmt.Println("更新数据：", affect)
	checkErr(err)
}

func DeleteFromDB(db *sql.DB, autid int) {
	stmt, err := db.Prepare("delete from userinfo where autid=?")
	checkErr(err)
	res, err := stmt.Exec(autid)
	affect, err := res.RowsAffected()
	fmt.Println("删除数据：", affect)
}
func GetTime() string {
	const shortForm = "2006-01-02 15:04:05"
	t := time.Now()
	temp := time.Date(t.Year(), t.Month(), t.Day(), t.Hour(), t.Minute(), t.Second(), t.Nanosecond(), time.Local)
	str := temp.Format(shortForm)
	fmt.Println(t)
	return str
}

func GetMD5Hash(text string) string {
	haser := md5.New()
	haser.Write([]byte(text))
	return hex.EncodeToString(haser.Sum(nil))
}

func GetNowtimeMD5() string {
	t := time.Now()
	timestamp := strconv.FormatInt(t.UTC().UnixNano(), 10)
	return GetMD5Hash(timestamp)
}

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