golang mysql last_insert_id_Golang操作MySQL数据库

下载驱动

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

链接数据库

import (

"database/sql"

"log"

"time"

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

)

var db *sql.DB

func init() {

database, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test_db")

if err != nil {

log.Fatal(err)

}

db = database

db.SetConnMaxLifetime(time.Minute * 3)

db.SetMaxOpenConns(10)

db.SetMaxIdleConns(10)

err = db.Ping()

if err != nil {

log.Fatal(err)

}

}

func main() {

defer db.Close()

// TODO

}

查询列表

占位符：?

func queryForList() {

rows, err := db.Query("select id, name from tb_user where id = ?", 1)

if err != nil {

if err == sql.ErrNoRows {

log.Println("无数据")

}

log.Fatal(err)

}

defer rows.Close()

var result = make(map[string]interface{})

for rows.Next() {

var (

id int

name string

)

err := rows.Scan(&id, &name)

if err != nil {

log.Fatal(err)

}

result[name] = id

}

if err := rows.Err(); err != nil {

log.Println("rows err: ", err)

}

log.Println("queryForList#", result)

}

查询单条

func queryForObject() {

var (

id int

name string

)

row := db.QueryRow("select id, name from tb_user where id = ? limit 1", 1)

err := row.Scan(&id, &name)

if err != nil {

log.Fatal(err)

}

log.Println("queryForObject#", id, name)

}

插入数据

func insert() {

stmt, err := db.Prepare("insert into tb_user(id,name) values(?,?)")

if err != nil {

log.Fatal(err)

}

result, err := stmt.Exec(1, "test_name")

if err != nil {

log.Fatal(err)

}

lastId, err := result.LastInsertId()

if err != nil {

log.Fatal(err)

}

rowCnt, err := result.RowsAffected()

log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)

}

事务

func transaction() {

tx, err := db.Begin()

if err != nil {

log.Fatal(err)

}

defer tx.Rollback()

stmt, err := tx.Prepare("insert into tb_user(id,name) values(?,?)")

if err != nil {

log.Fatal(err)

}

defer stmt.Close()

_, err = stmt.Exec(1, "test_name2")

if err != nil {

log.Fatal(err)

}

err = tx.Commit()

if err != nil {

log.Fatal(err)

}

}

驱动列表

参考