go语言实现数据库访问

痛定思痛。 2022-04-10 06:41 146阅读 0赞

### 文章目录 ###

*   *  新建工程项目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 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhbmdiaW4xMjM_size_16_color_FFFFFF_t_70]

## 在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:password@tcp(localhost:5555)/dbname?charset=utf8`

const (
        db_driver = "root:r123456@tcp(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()
    }

## 输出结果 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhbmdiaW4xMjM_size_16_color_FFFFFF_t_70 1]

## 完整代码 ##

package main
    
    import (
    	"crypto/md5"
    	"database/sql"
    	"encoding/hex"
    	"fmt"
    	"strconv"
    	"time"
    
    	_ "github.com/go-sql-driver/mysql"
    )
    
    const (
    	db_driver = "root:r123456@tcp(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)
    	}
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhbmdiaW4xMjM_size_16_color_FFFFFF_t_70]: /images/20220402/0de66fc098a646f0bc3aa4b262ac62a7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nY2hhbmdiaW4xMjM_size_16_color_FFFFFF_t_70 1]: /images/20220402/57cc4fa2a0d74d0a9189b4b734d0ee8a.png