go mysql详解

常用方法

//driverName参数为数据库驱动名称。
//dataSourceName是连接参数,参数格式如下:
//user:password@tcp(host:port)/dbname?charset=utf8
func Open(driverName, dataSourceName string) (*DB, error)

//Prepare为后续查询或执行操作创建一个准备SQL
func (db *DB) Prepare(query string) (*Stmt, error)

//使用给定参数执行准备的SQL语句
func (s *Stmt) Exec(args ...interface{}) (Result, error)

//执行SQL操作,query为SQL语句,可以接收可变参数,用于填充SQL语句的某些字段值。
func (db *DB) Exec(query string, args ...interface{}) (Result, error)

//使用给定参数执行准备的SQL查询语句
func (s *Stmt) Query(args ...interface{}) (*Rows, error)

//执行SQL查询操作,可以接收多个参数
func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

导入数据库驱动

import (
    "database/sql"
    _ "github.com/go-sql-driver/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
}
// 尝试与数据库建立连接(校验dsn是否正确),也可以省略
err = db.Ping()
if err != nil {
	return err
}
DB.SetConnMaxLifetime(100*time.Second)  //最大连接周期,超过时间的连接就close
DB.SetMaxOpenConns(100)//设置最大连接数
DB.SetMaxIdleConns(16) //设置闲置连接数

查询

//指定条件,指定参数查询
rows, err := conn.Query("select `name` from stu where id=?", 3)
if nil != err{
	fmt.Println("query db error: ", err.Error())
	return
}
fmt.Println(rows)
for rows.Next() {
    var name string
    //将值存入变量name中
    err= rows.Scan(&name)
    if err != nil{
        panic(err.Error())
    }
	fmt.Println(name)
}

////查询所有
rows, _ := db.Query("SELECT * FROM user")
 
for rows.Next() {
    var userId int
    var userName string
    var userAge int
    var userSex int
    rows.Columns()
    err = rows.Scan(&userId, &userName, &userAge, &userSex)
    checkErr(err)
    fmt.Println(userId)
    fmt.Println(userName)
    fmt.Println(userAge)
    fmt.Println(userSex)
}
或:
rows, _ = conn.Query("select * from stu")
//查看所有列名
cols, _:=rows.Columns()
for _, col := range cols{
	println("col:", col)
}
vals := make([]sql.RawBytes, len(cols))
//vals转换为interface, 查看https://github.com/golang/go/wiki/InterfaceSlice
scanArgs := make([]interface{}, len(vals))
for i := range vals {
	scanArgs[i] = &vals[i]
}
for rows.Next() {
    rows.Scan(scanArgs...)
    for _, val := range vals{
         print(string(val)," ")
    }
    println()
}

插入

//保存数据(修改和删除操作)
stmt, _ := conn.Prepare("insert into stu(id, no, name) values(?, ?, ?)")
rs, _ := stmt.Exec(1, "003", "banana")
//获取最后插入的id
id, _ := rs.LastInsertId()
fmt.Println("id:", id)
//获取影响的行数
affectNum, _ := rs.RowsAffected()
fmt.Println("affectNum:", affectNum)

修改

stmt, err := db.Prepare(`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)
checkErr(err)
res, err := stmt.Exec(21, 2, 1)
checkErr(err)
num, err := res.RowsAffected()
checkErr(err)
fmt.Println(num)

删除

stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)
checkErr(err)
res, err := stmt.Exec(1)
checkErr(err)
num, err := res.RowsAffected()
checkErr(err)
fmt.Println(num)

实例

package main
 
import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)
 
func main() {
    insert()
}
 
//插入demo
func insert() {
    db, err := sql.Open("mysql", "root:@/test?charset=utf8")
    checkErr(err)
 
    stmt, err := db.Prepare(`INSERT user (user_name,user_age,user_sex) values (?,?,?)`)
    checkErr(err)
    res, err := stmt.Exec("tony", 20, 1)
    checkErr(err)
    id, err := res.LastInsertId()
    checkErr(err)
    fmt.Println(id)
}
 
//查询demo
func query() {
    db, err := sql.Open("mysql", "root:@/test?charset=utf8")
    checkErr(err)
 
    rows, err := db.Query("SELECT * FROM user")
    checkErr(err)
 
    //普通demo
    //for rows.Next() {
    //    var userId int
    //    var userName string
    //    var userAge int
    //    var userSex int
 
    //    rows.Columns()
    //    err = rows.Scan(&userId, &userName, &userAge, &userSex)
    //    checkErr(err)
 
    //    fmt.Println(userId)
    //    fmt.Println(userName)
    //    fmt.Println(userAge)
    //    fmt.Println(userSex)
    //}
 
    //字典类型
    //构造scanArgs、values两个数组,scanArgs的每个值指向values相应值的地址
    columns, _ := rows.Columns()
    scanArgs := make([]interface{}, len(columns))
    values := make([]interface{}, len(columns))
    for i := range values {
        scanArgs[i] = &values[i]
    }
 
    for rows.Next() {
        //将行数据保存到record字典
        err = rows.Scan(scanArgs...)
        record := make(map[string]string)
        for i, col := range values {
            if col != nil {
                record[columns[i]] = string(col.([]byte))
            }
        }
        fmt.Println(record)
    }
}
 
//更新数据
func update() {
    db, err := sql.Open("mysql", "root:@/test?charset=utf8")
    checkErr(err)
 
    stmt, err := db.Prepare(`UPDATE user SET user_age=?,user_sex=? WHERE user_id=?`)
    checkErr(err)
    res, err := stmt.Exec(21, 2, 1)
    checkErr(err)
    num, err := res.RowsAffected()
    checkErr(err)
    fmt.Println(num)
}
 
//删除数据
func remove() {
    db, err := sql.Open("mysql", "root:@/test?charset=utf8")
    checkErr(err)
 
    stmt, err := db.Prepare(`DELETE FROM user WHERE user_id=?`)
    checkErr(err)
    res, err := stmt.Exec(1)
    checkErr(err)
    num, err := res.RowsAffected()
    checkErr(err)
    fmt.Println(num)
}
 
func checkErr(err error) {
    if err != nil {
        panic(err)
    }
}
# mysql  

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×