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)
}
有疑问加站长微信联系（非本文作者）
