sqlx操作mysql

go语言中MySQL驱动安装

使用第三方开源的mysql库:

http://github.com/go-sql-driver/mysql （mysql驱动）

http://github.com/jmoiron/sqlx（基于mysql驱动的封装）

分别执行如下命令行：

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

测试连接

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

var db *sqlx.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
	dsn := "root:lihuawei@tcp(127.0.0.1:3306)/haha?charset=utf8mb4&parseTime=True"
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB faile, err: %v\n", err)
		return
	}
	db.SetMaxOpenConns(100)
	db.SetMaxIdleConns(10)
	return
}

func main() {
	if err := initDB(); err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
	}
	defer db.Close()
	fmt.Println("connect to db success!")
}

操作数据库

插入一条数据

package main

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

var db *sqlx.DB

// 定义一个初始化数据库的函数
func initDB() (err error) {
	dsn := "root:lihuawei@tcp(127.0.0.1:3306)/haha?charset=utf8mb4&parseTime=True"
	db, err = sqlx.Connect("mysql", dsn)
	if err != nil {
		fmt.Printf("connect DB faile, err: %v\n", err)
		return
	}
	db.SetMaxOpenConns(100)
	db.SetMaxIdleConns(10)
	return
}

type user struct {
	ID   int    `db:"id"`
	Name string `db:"name"`
	Age  string `db:"age"`
}

// 插入数据
func insertDemo() {
	sqlStr := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sqlStr, "jack", 23)
	if err != nil {
		fmt.Printf("insert failed, err: %v\n", err)
		return
	}
	id, err := ret.LastInsertId()
	if err != nil {
		fmt.Printf("get lastinsert ID failed, err:%v\n", err)
		return
	}
	fmt.Printf("insert success,the id is %v\n", id)
}

func main() {
	if err := initDB(); err != nil {
		fmt.Printf("init db failed,err:%v\n", err)
	}
	defer db.Close()
	fmt.Println("connect to db success!")

	insertDemo()
}

查询一条数据

func queryRowDemo() {
   sqlStr := "select id ,name ,age from user where id=?"
	var u user
	err := db.Get(&u, sqlStr, 1)
	if err != nil {
		fmt.Printf("get failed,err%v\n", err)
		return
	}
	fmt.Printf("%v\n", u)
}

查询多条数据

func queryMultiRowDemo() {
	sqlStr := "select id ,name ,age from user where id>?"
	var users []user
	err := db.Select(&users, sqlStr, 0)
	if err != nil {
		fmt.Printf("get failed,err:%v\n", err)
		return
	}
	fmt.Printf("%v\n", users)
}

更新一条数据

func update() {
   sqlStr := "update user set age = ? where id =?"
	ret, err := db.Exec(sqlStr, 20, 6)
	if err != nil {
		fmt.Printf("update failed,err: %v\n", err)
		return
	}
	n, err := ret.RowsAffected()
	if err != nil {
		fmt.Printf("get RowsAffected failed, err: %v\n", err)
		return
	}
	fmt.Printf("update success, affected rows: %v\n", n)
}

删除一条数据

func deleteById() {
   sqlStr := "delete from user where id =?"
	ret, err := db.Exec(sqlStr, 6)
	if err != nil {
		fmt.Printf("delete failed,err: %v\n", err)
		return
	}
	n, err := ret.RowsAffected()
	if err != nil {
		fmt.Printf("get RowsAffected failed, err: %v\n", err)
		return
	}
	fmt.Printf("delete  success, affected rows: %v\n", n)
}