以下介绍两种方式 ,go内置函数,和 sqlx
定义结构体
type Goods struct {
Id int `db:"id"`
GmtCreateSys string `db:"gmt_create_sys"`
GmtUpdateSys string `db:"gmt_update_sys"`
GoodsName string `db:"goods_name"`
GoodsPrice string `db:"goods_price"`
}
go内置函数database/sql
导入包
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"time"
)
初始化连接
var Db *sql.DB
func init() {
database, err := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/study")
if err!=nil {
fmt.Println(";连接数据库失败",err)
return
}
db=database
}
执行新增
func insertGoods() {
stmt, _ := db.Prepare("insert into goods(goods_name, goods_price)values(?, ?)")
res, _ := stmt.Exec("testGoods2", 200)
fmt.Println(res)
}
执行查询
func queryGoods() {
result, _ := db.Query("select id,goods_name,goods_price from goods where goods_price>?", 1)
defer result.Close()
var id int
var goodsName string
var goodsPrice string
for result.Next() {
_ = result.Scan(&id,&goodsName,&goodsPrice)
fmt.Println(id,goodsName,goodsPrice)
}
goods := &Goods{
Id: id,
GmtCreateSys: "",
GmtUpdateSys: "",
GoodsName: goodsName,
GoodsPrice: goodsPrice,
}
fmt.Println(goods.GoodsName)
fmt.Println(goods.GoodsPrice)
}
执行修改
func updateGoods() {
stmt, _ := db.Prepare("update goods set goods_name=? where id=?")
res, _ := stmt.Exec("test", 1)
num, _ := res.RowsAffected()
fmt.Println(num)
}
执行删除
func deleteGoods() {
stmt, _ := db.Prepare("delete from goods where id=?")
res, _ := stmt.Exec(13)
num, _ := res.RowsAffected()
fmt.Println(num)
}
sqlx 方式连接mysql
导包
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
"time"
)
初始化链接
var Db *sqlx.DB
func init() {
database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/study")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = database
}
执行新增
func sqlXInsertGoods() {
now := time.Now().Format("2006-01-02 15:04:05")
r, err := Db.Exec("insert into goods(gmt_create_sys,gmt_update_sys,goods_name, goods_price)values(?,?,?,?)", now, now, "testGoods1", 100)
if err != nil {
fmt.Println("exec failed, ", err)
return
}
id, err := r.LastInsertId()
if err != nil {
fmt.Println("exec failed, ", err)
return
}
fmt.Println("insert succ:", id)
}
执行查询
func sqlXQueryGoods() {
var goods []Goods
err := Db.Select(&goods,"select id,gmt_create_sys,gmt_update_sys,goods_name,goods_price from goods where goods_price>?", 1)
if err!=nil {
fmt.Println(err)
}
fmt.Println(goods)
}
执行修改和执行删除与 go内置函数一致