golang操作mysql简单例子

==表结构==

CREATE TABLE `point_info` (
    `id` BIGINT(19) NOT NULL COMMENT '自增ID',
    `product_key` VARCHAR(50) NOT NULL COMMENT '产品标识' COLLATE 'utf8mb4_general_ci',
    `device_name` VARCHAR(50) NOT NULL COMMENT '设备名称' COLLATE 'utf8mb4_general_ci',
    `point_id` VARCHAR(50) NOT NULL COMMENT '测点ID' COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (`id`) USING BTREE
)
COMMENT='测点信息'
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

 

==下载依赖==

go get github.com/go-sql-driver/mysql

 

==代码样例==

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "time"
)

var MysqlDb *sql.DB
var MysqlDbErr error

type PointInfo struct {
    Id         int64  `db:"id"`
    ProductKey string `db:"product_key"`
    DeviceName string `db:"device_name"`
    PointId    string `db:"point_id"`
}

const (
    USER_NAME = "root"
    PASS_WORD = "Radfkadf"
    HOST      = "xx.xx.xx.xxx"
    PORT      = "3306"
    DATABASE  = "pulse"
    CHARSET   = "utf8"
)

// 初始化链接
func init() {
    dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", USER_NAME, PASS_WORD, HOST, PORT, DATABASE, CHARSET)

    // 打开连接失败
    MysqlDb, MysqlDbErr = sql.Open("mysql", dbDSN)
    //defer MysqlDb.Close();
    if MysqlDbErr != nil {
        fmt.Println("dbDSN: " + dbDSN)
        panic("数据源配置不正确: " + MysqlDbErr.Error())
    }

    // 最大连接数
    MysqlDb.SetMaxOpenConns(100)
    // 闲置连接数
    MysqlDb.SetMaxIdleConns(20)
    // 最大连接周期
    MysqlDb.SetConnMaxLifetime(100 * time.Second)

    if MysqlDbErr = MysqlDb.Ping(); nil != MysqlDbErr {
        panic("数据库链接失败: " + MysqlDbErr.Error())
    }
}

func main() {
    // 开启事务
    tx, err := MysqlDb.Begin()
    if err != nil {
        return
    }

    // 插入数据
    id, err := StructInsert()
    if err != nil {
        Rollback(tx)
        return
    }

    // 查询指定数据
    StructQueryField(id)

    // 查询所有数据
    StructQueryAllField()

    // 更新数据
    err = StructUpdate(id)
    if err != nil {
        Rollback(tx)
        return
    }

    // 删除数据
    err = StructDel(id)
    if err != nil {
        Rollback(tx)
        return
    }

    // 提交事务
    Commit(tx)

    // 关闭连接
    err = MysqlDb.Close()
    if err != nil {
        return
    }
}

func Commit(tx *sql.Tx) {
    err := tx.Commit()
    if err != nil {
        return
    }
}

func Rollback(tx *sql.Tx) {
    err := tx.Rollback()
    if err != nil {
        return
    }
}

// StructQueryField 查询数据，指定字段名
func StructQueryField(id int64) {
    var sqlStr = "SELECT id, product_key, device_name, point_id FROM point_info WHERE id = ?"
    row := MysqlDb.QueryRow(sqlStr, id)

    pointInfo := new(PointInfo)
    err := row.Scan(&pointInfo.Id, &pointInfo.ProductKey, &pointInfo.DeviceName, &pointInfo.PointId)
    if err != nil {
        fmt.Printf("scan failed, err:%v", err)
        return
    }
    fmt.Println(pointInfo.Id, pointInfo.ProductKey, pointInfo.DeviceName, pointInfo.PointId)
}

// StructQueryAllField 查询数据，取所有字段
func StructQueryAllField() {
    var sqlStr = "SELECT * FROM point_info limit ?"
    rows, err := MysqlDb.Query(sqlStr, 10)
    if err != nil {
        fmt.Println(err.Error())
        return
    }

    // 通过切片存储
    pointInfos := make([]PointInfo, 0)

    // 遍历
    var pointInfo PointInfo
    for rows.Next() {
        err := rows.Scan(&pointInfo.Id, &pointInfo.ProductKey, &pointInfo.DeviceName, &pointInfo.PointId)
        if err != nil {
            fmt.Println(err.Error())
            return
        }
        pointInfos = append(pointInfos, pointInfo)
    }

    fmt.Println(pointInfos)
}

// StructInsert 插入数据
func StructInsert() (int64, error) {
    var sqlStr = "insert INTO point_info(product_key, device_name, point_id) values(?, ?, ?)"
    ret, err := MysqlDb.Exec(sqlStr, "product1", "device1", "point1")
    if err != nil {
        fmt.Println(err.Error())
        return 0, err
    }

    lastInsertID, err := ret.LastInsertId()
    if err != nil {
        fmt.Println(err.Error())
        return 0, err
    }
    fmt.Println("插入数据主键 LastInsertID:", lastInsertID)

    rowsAffected, err := ret.RowsAffected()
    if err != nil {
        fmt.Println(err.Error())
        return 0, err
    }
    fmt.Println("插入影响行数 RowsAffected:", rowsAffected)
    return lastInsertID, nil
}

// StructUpdate 更新数据
func StructUpdate(id int64) error {
    var sqlStr = "UPDATE point_info set product_key = ?, device_name = ?, point_id = ? where id = ?"
    ret, err := MysqlDb.Exec(sqlStr, "product2", "device2", "point2", id)
    if err != nil {
        fmt.Println(err.Error())
        return err
    }

    updNums, err := ret.RowsAffected()
    if err != nil {
        fmt.Println(err.Error())
        return err
    }
    fmt.Println("更新影响行数 RowsAffected:", updNums)
    return nil
}

// StructDel 删除数据
func StructDel(id int64) error {
    var sqlStr = "delete from point_info where id = ?"
    ret, err := MysqlDb.Exec(sqlStr, id)
    if err != nil {
        fmt.Println(err.Error())
        return err
    }

    delNums, err := ret.RowsAffected()
    if err != nil {
        fmt.Println(err.Error())
        return err
    }
    fmt.Println("删除影响行数 RowsAffected:", delNums)
    return nil
}

 

==执行结果==

插入数据主键 LastInsertID: 5
插入影响行数 RowsAffected: 1
5 product1 device1 point1
[{5 product1 device1 point1}]
更新影响行数 RowsAffected: 1
删除影响行数 RowsAffected: 1