数据库创建demo表

CREATE TABLE `demo` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称',
  `age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `sex` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '性别：1男，2女',
  `mobile` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '手机号',
  `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '邮箱',
  `qq` int(11) unsigned NOT NULL COMMENT 'QQ',
  `is_delete` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否删除：1正常，2删除',
  `status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '状态：1正常，2禁用，3异常。。。',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`) USING BTREE COMMENT '唯一主键'
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 COMMENT='用户表';

创建DB连接，创建结构体Demo

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

var MysqlDb *sql.DB
var Err error

const (
    USERNAME = ""
    PASSWORD = ""
    HOST      = ""
    PORT      = ""
    DATABASE  = ""
    CHARSET   = ""
)

func init() {
    dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=%s", USERNAME, PASSWORD, HOST, PORT, DATABASE, CHARSET)
    // 打开连接失败
    MysqlDb, Err = sql.Open("mysql", dbDSN)
    if Err != nil {
        panic("数据源配置错误: " + Err.Error())
    }
    // 最大连接数
    MysqlDb.SetMaxOpenConns(100)
    // 闲置连接数
    MysqlDb.SetMaxIdleConns(20)
    // 最大连接周期
    MysqlDb.SetConnMaxLifetime(100 * time.Second)
    if Err = MysqlDb.Ping(); nil != Err {
        panic("数据库链接失败: " + Err.Error())
    }
    // defer MysqlDb.Close();
}

type Demo struct {
    Id int `json:"id"`
    Name string `json:"name"`
    Age int `json:"age"`
    Status int `json:"status"`
    Sex int `json:"sex"`
    Email string `json:"email"`
    Mobile string `json:"mobile"`
    Qq string `json:"qq"`
}

使用QueryRow查询

func MysqlQueryRow() {
    var demo Demo
    // 查询 QueryRow 返回一条
    row := MysqlDb.QueryRow("SELECT `id`, `name` FROM  demo WHERE `status` = ?", 1)
    Err = row.Scan(&demo.Id, &demo.Name)
    if Err == nil {
        fmt.Println(demo.Name, demo.Id)
    } else {
        fmt.Println(Err.Error())
    }
}

使用Query查询

func MysqlQuery() {
    // 查询 Query
    var demos []Demo
    var demo Demo
    rows, Err := MysqlDb.Query("SELECT `id`, `name` FROM  demo WHERE `status` = ?", 1)
    if Err == nil {
        // 依次循环取
        for rows.Next() {
            rows.Scan(&demo.Id, &demo.Name)
            demos = append(demos, demo)
        }
        fmt.Println(demos)
    } else {
        fmt.Println(Err.Error())
    }
}

新增INSERT

func MysqlInsert() {
    res, Err := MysqlDb.Exec("INSERT INTO demo(`name`, `age`, `status`, `sex`, `email`, `mobile`, `qq`) " +
        "VALUES(?, ?, ?, ?, ?, ?, ?)", "孙策", "20", "2", "1", "jiangdong@126.com", "13366666666", "555333999")
    if Err == nil {
        // 获取最后插入ID
        num, Err := res.LastInsertId()
        if Err == nil {
            fmt.Println(num)
        } else {
            fmt.Println(Err.Error())
        }
    } else {
        fmt.Println(Err.Error())
    }
}

更新UPDATE

func MysqlUpdate() {
    res, Err := MysqlDb.Exec("UPDATE demo SET `name` = ? WHERE `id` = ?","马超", 1)
    if Err == nil {
        // 获取影响行
        num, Err := res.RowsAffected()
        if Err == nil {
            fmt.Println(num)
        } else {
            fmt.Println(Err.Error())
        }
    } else {
        fmt.Println(Err.Error())
    }
}

删除DELETE

func MysqlDelete() {
    res, Err := MysqlDb.Exec("DELETE FROM demo WHERE `id` = ?", 37)
    if Err == nil {
        // 获取影响行
        num, Err := res.RowsAffected()
        if Err == nil {
            fmt.Println(num)
        } else {
            fmt.Println(Err.Error())
        }
    } else {
        fmt.Println(Err.Error())
    }
}

事务处理

func MysqlTx() {
    // 开启事务
    tx, Err := MysqlDb.Begin()
    if Err == nil {
        res1, _ := tx.Exec("UPDATE demo SET `name` = ? WHERE `id` = ?","赵云", 35)
        res2, _ := tx.Exec("UPDATE demo SET `name` = ? WHERE `id` = ?","赵云", 38)
        num1, _ := res1.RowsAffected()
        num2, _ := res2.RowsAffected()
        if num1 > 0 && num2 > 0{
            // 提交
            Err = tx.Commit()
            if Err == nil {
                fmt.Println("Success")
            } else {
                fmt.Println(Err.Error())
            }
        } else {
            // 回滚
            Err = tx.Rollback()
            if Err == nil {
                fmt.Println("Fail")
            } else {
                fmt.Println(Err.Error())
            }
        }
    } else {
        fmt.Println(Err.Error())
    }
}

有疑问加站长微信联系（非本文作者）