Go操作MySQL-sqlx的使用

安装

go get github.com/jmoiron/sqlx

基本使用

新建表packagemainimport(

_"github.com/go-sql-driver/mysql"

"github.com/jmoiron/sqlx")

var CreateSchemaTablePlace=`Create table place(

country varchar(20),

city varchar(20) NULL,

telcodeint(20));

`

func main() {

dsn := "root:Jimstars@tcp(10.10.10.62:3306)/scott"db, err := sqlx.Connect("mysql", dsn)if err !=nil {

panic(err)

}

result, err :=db.Exec(CreateSchemaTablePlace)if err !=nil {

panic(err)

}

_, err=result.RowsAffected()if err !=nil {

panic(err)

}

}

单行查询使用 sqlx.Get()，多行查询使用 sqlx.Select()packagemainimport("database/sql"_"github.com/go-sql-driver/mysql"

"fmt"

"log"

"github.com/jmoiron/sqlx")

var dbConnect= "root:Jimstars@tcp(10.10.10.62:3306)/scott"type User struct {

Numint `db:"num"`

Name string `db:"name"`

}//单行查询

func QueryRow(db *sqlx.DB) {

var user User

err := db.Get(&user, "SELECT * FROM test where num=?", 2)if err ==sql.ErrNoRows {

log.Printf("not found data of the num:%d", 2)

}if err !=nil {

panic(err)

}

fmt.Printf("user: %#v\n", user)

}//多行查询, 查询不到任何记录也不会报错。

func Query(db *sqlx.DB) {

var users []*User

err := db.Select(&users, "SELECT * FROM test")if err !=nil {

panic(err)

}if err ==sql.ErrNoRows {

log.Printf("not found data")return}for _, user :=range users {

fmt.Println(user.Num, user.Name)

}

}

func main() {

db, err := sqlx.Connect("mysql", dbConnect)if err !=nil {

panic(err)

}

err=db.Ping()if err !=nil {

panic(err)

}

fmt.Printf("connect to db success\n")

QueryRow(db)

Query(db)

}

更新和插入使用sqlx.Exec()packagemainimport(

_"github.com/go-sql-driver/mysql"

"fmt"

"github.com/jmoiron/sqlx")

var dbConnect= "root:Jimstars@tcp(10.10.10.62:3306)/scott"type User struct {

Numint `db:"num"`

Name string `db:"name"`

}

func Update(db*sqlx.DB) {

name := "Miles"

//id := 2

Num := 2result, err := db.Exec("update test set name=? where Num=?", name, Num)if err !=nil {

panic(err)

}

rowsAffected, err :=result.RowsAffected()if err !=nil {

panic(err)

}

fmt.Printf("update Num:%d, affect rows:%d\n", Num, rowsAffected)

}

func Insert(db*sqlx.DB) {

name := "Lucy"Num := 18result, err := db.Exec("insert into test(num, name ) values (?,?)", Num, name)if err !=nil {

panic(err)

}

id, err :=result.LastInsertId()if err !=nil {

panic(err)

}

affected, err :=result.RowsAffected()if err !=nil {

panic(err)

}

fmt.Printf("last insert id:%d affect rows:%d\n", id, affected)

}

func main() {

db, err := sqlx.Connect("mysql", dbConnect)if err !=nil {

panic(err)

}

err=db.Ping()if err !=nil {

panic(err)

}

fmt.Printf("connect to db success\n")

Update(db)

Insert(db)

}

原则性操作packagemainimport(

_"github.com/go-sql-driver/mysql"

"fmt"

"github.com/jmoiron/sqlx")

func Transaction(db*sqlx.DB) {//开启事务

tx, err :=db.Begin()if err !=nil {

panic(err)

}

result, err := tx.Exec("insert into user(name, age)values(?,?)", "Jack", 98)if err !=nil {//失败回滚

tx.Rollback()

panic(err)

}

fmt.Println("result", result)

exec, err := tx.Exec("update user set name=?, age=? where id=?", "Jack", 98, 1)if err !=nil {//失败回滚

tx.Rollback()

panic(err)

}

fmt.Println("exec", exec)//提交事务

err =tx.Commit()if err !=nil {//失败回滚

tx.Rollback()

panic(err)

}

}

func main() {

dsn := "root:123456@tcp(172.16.65.200:3306)/golang"db, err := sqlx.Connect("mysql", dsn)if err !=nil {

panic(err)

}

err=db.Ping()if err !=nil {

panic(err)

}

Transaction(db)

}

更多用法packagemainimport("database/sql"

"fmt"

"log"_"github.com/lib/pq"

"github.com/jmoiron/sqlx")

var schema=`

CREATE TABLE person (

first_name text,

last_name text,

email text

);

CREATE TABLE place (

country text,

city text NULL,

telcode integer

)`

type Person struct {

FirstName string `db:"first_name"`

LastName string `db:"last_name"`

Email string

}

type Place struct {

Country string

City sql.NullString

TelCodeint}

func main() {//this Pings the database trying to connect, panics on error//use sqlx.Open() for sql.Open() semantics

db, err := sqlx.Connect("postgres", "user=foo dbname=bar sslmode=disable")if err !=nil {

log.Fatalln(err)

}//exec the schema or fail; multi-statement Exec behavior varies between//database drivers; pq will exec them all, sqlite3 won't, ymmv

db.MustExec(schema)

tx :=db.MustBegin()

tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "Jason", "Moiron", "jmoiron@jmoiron.net")

tx.MustExec("INSERT INTO person (first_name, last_name, email) VALUES ($1, $2, $3)", "John", "Doe", "johndoeDNE@gmail.net")

tx.MustExec("INSERT INTO place (country, city, telcode) VALUES ($1, $2, $3)", "United States", "New York", "1")

tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Hong Kong", "852")

tx.MustExec("INSERT INTO place (country, telcode) VALUES ($1, $2)", "Singapore", "65")//Named queries can use structs, so if you have an existing struct (i.e. person := &Person{}) that you have populated, you can pass it in as &person

tx.NamedExec("INSERT INTO person (first_name, last_name, email) VALUES (:first_name, :last_name, :email)", &Person{"Jane", "Citizen", "jane.citzen@example.com"})

tx.Commit()//Query the database, storing results in a []Person (wrapped in []interface{})

people :=[]Person{}

db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC")

jason, john := people[0], people[1]

fmt.Printf("%#v\n%#v", jason, john)//Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}//Person{FirstName:"John", LastName:"Doe", Email:"johndoeDNE@gmail.net"}//You can also get a single result, a la QueryRow

jason =Person{}

err= db.Get(&jason, "SELECT * FROM person WHERE first_name=$1", "Jason")

fmt.Printf("%#v\n", jason)//Person{FirstName:"Jason", LastName:"Moiron", Email:"jmoiron@jmoiron.net"}//if you have null fields and use SELECT *, you must use sql.Null* in your struct

places :=[]Place{}

err= db.Select(&places, "SELECT * FROM place ORDER BY telcode ASC")if err !=nil {

fmt.Println(err)return}

usa, singsing, honkers := places[0], places[1], places[2]

fmt.Printf("%#v\n%#v\n%#v\n", usa, singsing, honkers)//Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}//Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}//Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}//Loop through rows using only one struct

place :=Place{}

rows, err := db.Queryx("SELECT * FROM place")forrows.Next() {

err := rows.StructScan(&place)if err !=nil {

log.Fatalln(err)

}

fmt.Printf("%#v\n", place)

}//Place{Country:"United States", City:sql.NullString{String:"New York", Valid:true}, TelCode:1}//Place{Country:"Hong Kong", City:sql.NullString{String:"", Valid:false}, TelCode:852}//Place{Country:"Singapore", City:sql.NullString{String:"", Valid:false}, TelCode:65}//Named queries, using `:name` as the bindvar. Automatic bindvar support//which takes into account the dbtype based on the driverName on sqlx.Open/Connect

_, err =db.NamedExec(`INSERT INTO person (first_name,last_name,email) VALUES (:first,:last,:email)`,

map[string]interface{}{"first": "Bin","last": "Smuth","email": "bensmith@allblacks.nz",

})//Selects Mr. Smith from the database

rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:fn`, map[string]interface{}{"fn": "Bin"})//Named queries can also use structs. Their bind names follow the same rules//as the name -> db mapping, so struct fields are lowercased and the `db` tag//is taken into consideration.

rows, err = db.NamedQuery(`SELECT * FROM person WHERE first_name=:first_name`, jason)

}