go服务端----mysql简单示例

mysql简单示例

趁现在公司的事情没那么多，多写几篇吧，虽然都比较基础，但是写了总比不写好。
mysql大家应该都了解，一款非常流行的开源关系数据库应用，而go语言中mysql的库不是很多，其中使用的人最多的应该就是go-sql-driver/mysql了吧，我的项目中同样也用了这个库，同样贴出它的github仓库地址
go-sql-driver/mysql
下面贴一下用go-sql-driver/mysql操作数据库的简单例子的代码，非常基础，直接看注释就好。

package main

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

func main() {
    //db配置信息
    user := "root"
    psw := "jiandanmima"
    host := "localhost"
    port := 3306
    dbname := "testdb"
    charset := "utf8"
    config := fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=%s", user, psw, host, port, dbname, charset)
    //连接数据库，获取连接实例
    db, err := sql.Open("mysql", config)
    if err != nil {
        fmt.Println("open db error", err)
    }
    //设置最大闲置连接和最大打开连接，具体数值按需指定
    db.SetMaxIdleConns(32)
    db.SetMaxOpenConns(32)

    //增删改查操作
    err = Insert(db, "刘小明", "男", 18)
    if err != nil {
        fmt.Println("insert err", err)
    }
    err = Delete(db, 1)
    if err != nil {
        fmt.Println("delete err", err)
    }
    err = Update(db, 2, 22)
    if err != nil {
        fmt.Println("update err", err)
    }
    err = Select(db, 3)
    if err != nil {
        fmt.Println("select err", err)
    }
    err = SelectAll(db)
    if err != nil {
        fmt.Println("select all err", err)
    }
}

//增
func Insert(db *sql.DB, name string, sex string, age int) error {
    sen := "INSERT INTO people(name, sex, age) VALUES (?, ?, ?)"
    stmt, err := db.Prepare(sen)
    if err != nil {
        return err
    }
    defer stmt.Close() //预处理记得关闭连接，使用defer在函数return前关闭
    rst, err := stmt.Exec(name, sex, age)
    if err != nil {
        return err
    }
    lastId, err := rst.LastInsertId()
    if err != nil {
        return err
    }
    fmt.Printf("last insert id %d.\n", lastId)
    return nil
}

//删
func Delete(db *sql.DB, id int) error {
    sen := "DELETE FROM people WHERE id = ?"
    stmt, err := db.Prepare(sen)
    if err != nil {
        return err
    }
    defer stmt.Close()
    rst, err := stmt.Exec(id)
    if err != nil {
        return err
    }
    rowsAffected, err := rst.RowsAffected()
    if err != nil {
        return err
    }
    fmt.Printf("%d records has been deleted.\n", rowsAffected)
    return nil
}

//改
func Update(db *sql.DB, id int, age int) error {
    sen := "UPDATE people SET age = ? WHERE id = ?"
    stmt, err := db.Prepare(sen)
    if err != nil {
        return err
    }
    defer stmt.Close()
    rst, err := stmt.Exec(age, id)
    if err != nil {
        return err
    }
    rowsAffected, err := rst.RowsAffected()
    if err != nil {
        return err
    }
    fmt.Printf("%d records has been updated.\n", rowsAffected)
    return nil
}

//查
func Select(db *sql.DB, id int) error {
    sen := "SELECT name, sex, age FROM people WHERE id = ?"
    stmt, err := db.Prepare(sen)
    if err != nil {
        return err
    }
    defer stmt.Close()
    row := stmt.QueryRow(id)
    var name, sex string
    var age int
    row.Scan(&name, &sex, &age)
    fmt.Printf("the information of id %d: name = '%s', sex = '%s', age = %d.\n", id, name, sex, age)
    return nil
}

//查询多条记录
func SelectAll(db *sql.DB) error {
    sen := "SELECT id, name, sex, age FROM people ORDER BY id"
    stmt, err := db.Prepare(sen)
    if err != nil {
        return err
    }
    defer stmt.Close()
    rows, err := stmt.Query()
    if err != nil {
        return err
    }
    defer rows.Close() //查询多条记录的rows适配器也需要及时关闭
    fmt.Println("all information of table people:")
    fmt.Println("id   name   sex   age")
    for rows.Next() {
        var id, age int
        var name, sex string
        rows.Scan(&id, &name, &sex, &age)
        fmt.Printf("%d   %s   %s   %d\n", id, name, sex, age)
    }
    return nil
}

程序运行前的数据表数据

程序运行的输出结果

最后附上数据表创建和数据插入语句

CREATE TABLE `people` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT '姓名',
  `sex` varchar(8) NOT NULL DEFAULT '男' COMMENT '性别',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into people(name, sex, age) values('张全蛋', '男' , 18), ('赵铁根', '男', 19), ('李小花', '女', 20), ('王尼玛', '男', 36);

golang简单的mysql操作就在这了。