Go搭配mysql_golang对mysql的基本操作

golang 操作mysql

导入数据库驱动

import (

"database/sql"

"fmt"

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

"time"

)

mysql连接配置

const (

USERNAME = "root"

PASSWORD = "*******"

NETWORK = "tcp"

SERVER = "localhost"

PORT = 3306

DATABASE = "blog"

)

建立连接

dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s",USERNAME,PASSWORD,NETWORK,SERVER,PORT,DATABASE)

DB,err := sql.Open("mysql",dsn)

if err != nil{

fmt.Printf("Open mysql failed,err:%v\n",err)

return

}

DB.SetConnMaxLifetime(100*time.Second) //最大连接周期，超过时间的连接就close

DB.SetMaxOpenConns(100)//设置最大连接数

DB.SetMaxIdleConns(16) //设置闲置连接数

数据模型

type User struct {

ID int64 `db:"id"`

Name sql.NullString `db:"name"` //由于在mysql的users表中name没有设置为NOT NULL,所以name可能为null,在查询过程中会返回nil，如果是string类型则无法接收nil,但sql.NullString则可以接收nil值

Age int `db:"age"`

}

数据表结构

15318255510282.jpg

查询单行

func queryOne(DB *sql.DB){

fmt.Println("query times:",i)

user := new(User)

row := DB.QueryRow("select * from users where id=?",1)

//row.scan中的字段必须是按照数据库存入字段的顺序，否则报错

if err :=row.Scan(&user.ID,&user.Name,&user.Age); err != nil{

fmt.Printf("scan failed, err:%v",err)

return

}

fmt.Println(*user)

}

}

注意：

row必须scan，不然会导致连接无法关闭，会一直占用连接，直到超过设置的生命周期

下面是未scan导致连接被占用的，最大连接数为100

func queryOne(DB *sql.DB){

for i:=0;i< 150;i++ {

fmt.Println("query times:",i)

user := new(User)

row := DB.QueryRow("select * from users where id=?",1)

continue

if err :=row.Scan(&user.ID,&user.Name,&user.Age); err != nil{

fmt.Printf("scan failed, err:%v",err)

return

}

fmt.Println(*user)

}

}

执行结果：

15318261414512.jpg

在执行到100后会发生阻塞，等待连接池释放。

查询多行

func queryMulti(DB *sql.DB){

user := new(User)

rows, err := DB.Query("select * from users where id > ?", 1)

defer func() {

if rows != nil {

rows.Close() //可以关闭掉未scan连接一直占用

}

}()

if err != nil {

fmt.Printf("Query failed,err:%v", err)

return

}

for rows.Next() {

err = rows.Scan(&user.ID, &user.Name, &user.Age) //不scan会导致连接不释放

if err != nil {

fmt.Printf("Scan failed,err:%v", err)

return

}

fmt.Print(*user)

}

}

插入数据

func insertData(DB *sql.DB){

result,err := DB.Exec("insert INTO users(name,age) values(?,?)","YDZ",23)

if err != nil{

fmt.Printf("Insert failed,err:%v",err)

return

}

lastInsertID,err := result.LastInsertId() //插入数据的主键id

if err != nil {

fmt.Printf("Get lastInsertID failed,err:%v",err)

return

}

fmt.Println("LastInsertID:",lastInsertID)

rowsaffected,err := result.RowsAffected() //影响行数

if err != nil {

fmt.Printf("Get RowsAffected failed,err:%v",err)

return

}

fmt.Println("RowsAffected:",rowsaffected)

}

更新数据

func updateData(DB *sql.DB){

result,err := DB.Exec("UPDATE users set age=? where id=?","30",3)

if err != nil{

fmt.Printf("Insert failed,err:%v",err)

return

}

rowsaffected,err := result.RowsAffected()

if err != nil {

fmt.Printf("Get RowsAffected failed,err:%v",err)

return

}

fmt.Println("RowsAffected:",rowsaffected)

}

注意：更新数据不返回LastInsertID,所以result.LastInsertID一直为0

删除数据

func deleteData(DB *sql.DB){

result,err := DB.Exec("delete from users where id=?",1)

if err != nil{

fmt.Printf("Insert failed,err:%v",err)

return

}

lastInsertID,err := result.LastInsertId()

if err != nil {

fmt.Printf("Get lastInsertID failed,err:%v",err)

return

}

fmt.Println("LastInsertID:",lastInsertID)

rowsaffected,err := result.RowsAffected()

if err != nil {

fmt.Printf("Get RowsAffected failed,err:%v",err)

return

}

fmt.Println("RowsAffected:",rowsaffected)

}

注意：更新数据不返回LastInsertID,所以result.LastInsertID一直为0

最终代码

/**

*FileName: mysql

*Create on 2018/7/17 下午4:57

*Create by mok

*golang中mysql的用法

*/

package main

import (

"database/sql"

"fmt"

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

"time"

)

type User struct {

ID int64 `db:"id"`

Name sql.NullString `db:"name"`

Age int `db:"age"`

}

const (

USERNAME = "root"

PASSWORD = "chen19950210"

NETWORK = "tcp"

SERVER = "localhost"

PORT = 3306

DATABASE = "blog"

)

func main() {

dsn := fmt.Sprintf("%s:%s@%s(%s:%d)/%s", USERNAME, PASSWORD, NETWORK, SERVER, PORT, DATABASE)

DB, err := sql.Open("mysql", dsn)

if err != nil {

fmt.Printf("Open mysql failed,err:%v\n", err)

return

}

DB.SetConnMaxLifetime(100 * time.Second)

DB.SetMaxOpenConns(100)

DB.SetMaxIdleConns(16)

queryOne(DB)

queryMulti(DB)

insertData(DB)

updateData(DB)

deleteData(DB)

}

//查询单行

func queryOne(DB *sql.DB) {

user := new(User)

row := DB.QueryRow("select * from users where id=?", 1)

if err := row.Scan(&user.ID, &user.Name, &user.Age); err != nil {

fmt.Printf("scan failed, err:%v", err)

return

}

fmt.Println(*user)

}

//查询多行

func queryMulti(DB *sql.DB) {

user := new(User)

rows, err := DB.Query("select * from users where id > ?", 1)

defer func() {

if rows != nil {

rows.Close()

}

}()

if err != nil {

fmt.Printf("Query failed,err:%v", err)

return

}

for rows.Next() {

err = rows.Scan(&user.ID, &user.Name, &user.Age)

if err != nil {

fmt.Printf("Scan failed,err:%v", err)

return

}

fmt.Print(*user)

}

}

//插入数据

func insertData(DB *sql.DB){

result,err := DB.Exec("insert INTO users(name,age) values(?,?)","YDZ",23)

if err != nil{

fmt.Printf("Insert failed,err:%v",err)

return

}

lastInsertID,err := result.LastInsertId()

if err != nil {

fmt.Printf("Get lastInsertID failed,err:%v",err)

return

}

fmt.Println("LastInsertID:",lastInsertID)

rowsaffected,err := result.RowsAffected()

if err != nil {

fmt.Printf("Get RowsAffected failed,err:%v",err)

return

}

fmt.Println("RowsAffected:",rowsaffected)

}

//更新数据

func updateData(DB *sql.DB){

result,err := DB.Exec("UPDATE users set age=? where id=?","30",3)

if err != nil{

fmt.Printf("Insert failed,err:%v",err)

return

}

rowsaffected,err := result.RowsAffected()

if err != nil {

fmt.Printf("Get RowsAffected failed,err:%v",err)

return

}

fmt.Println("RowsAffected:",rowsaffected)

}

//删除数据

func deleteData(DB *sql.DB){

result,err := DB.Exec("delete from users where id=?",1)

if err != nil{

fmt.Printf("Insert failed,err:%v",err)

return

}

rowsaffected,err := result.RowsAffected()

if err != nil {

fmt.Printf("Get RowsAffected failed,err:%v",err)

return

}

fmt.Println("RowsAffected:",rowsaffected)

}