go 连接操作MySQL

连接Mysql

1. 访问此网站搜索MySQL
2. 第一个就是按照指引运行

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

3. 导入包建立连接

package mainimport ("database/sql""fmt""time"_ "github.com/go-sql-driver/mysql"
)var db *sql.DBfunc initdb() (err error) {db, err = sql.Open("mysql", "ellis:ellis@tcp(192.168.214.134:3306)/go_db?charset=utf8mb4&parseTime=True")if err != nil {return err}//尝试与数据库建立连接err = db.Ping()if err != nil {return err}// See "Important settings" section.db.SetConnMaxLifetime(time.Minute * 3)db.SetMaxOpenConns(10)//最大空闲db.SetMaxIdleConns(10)return nil
}// ...func main() {err := initdb()if err != nil {panic(err)}}

插入数据

func(db *DB) Exec(query string,args...interface{})(Result,error)package mainimport ("database/sql""fmt""time"_ "github.com/go-sql-driver/mysql"
)var db *sql.DBfunc initdb() (err error) {db, err = sql.Open("mysql", "ellis:ellis@tcp(192.168.214.134:3306)/go_db?charset=utf8mb4&parseTime=True")if err != nil {return err}//尝试与数据库建立连接err = db.Ping()if err != nil {return err}// See "Important settings" section.db.SetConnMaxLifetime(time.Minute * 3)db.SetMaxOpenConns(10)//最大空闲db.SetMaxIdleConns(10)return nil
}func insertValue(name string, password string) (result sql.Result, err error) {query := "insert into user_tbl(name,password) values(?,?)"result, err = db.Exec(query, name, password)if err != nil {fmt.Printf("err: %v\n", err)}fmt.Printf("result: %v\n", result)// 最后插入的IDfmt.Println(result.LastInsertId())return
}// ...func main() {err := initdb()if err != nil {panic(err)}insertValue("ellis", "ellis")
}

查询

• 单行查询使用QueryRow,详见selectOne函数

package mainimport ("database/sql""fmt""time"_ "github.com/go-sql-driver/mysql"
)var db *sql.DBfunc initdb() (err error) {db, err = sql.Open("mysql", "ellis:ellis@tcp(192.168.214.134:3306)/go_db?charset=utf8mb4&parseTime=True")if err != nil {return err}//尝试与数据库建立连接err = db.Ping()if err != nil {return err}// See "Important settings" section.db.SetConnMaxLifetime(time.Minute * 3)db.SetMaxOpenConns(10)//最大空闲db.SetMaxIdleConns(10)return nil
}type User struct {id       intname     stringpassword string
}func searchOne() {var user Userdb.QueryRow("select * from user_tbl where id=1").Scan(&user.id, &user.name, &user.password)fmt.Printf("user.id: %v\n", user.id)
}// ...func main() {err := initdb()if err != nil {panic(err)}searchOne()
}

• 查询多行

package mainimport ("database/sql""fmt""time"_ "github.com/go-sql-driver/mysql"
)var db *sql.DBfunc initdb() (err error) {db, err = sql.Open("mysql", "ellis:ellis@tcp(192.168.214.134:3306)/go_db?charset=utf8mb4&parseTime=True")if err != nil {return err}//尝试与数据库建立连接err = db.Ping()if err != nil {return err}// See "Important settings" section.db.SetConnMaxLifetime(time.Minute * 3)db.SetMaxOpenConns(10)//最大空闲db.SetMaxIdleConns(10)return nil
}type User struct {id       intname     stringpassword string
}func searchAll() {query := "select * from user_tbl"r, err := db.Query(query)defer r.Close()if err != nil {fmt.Printf("err: %v\n", err)} else {// 一行行的读for r.Next() {var u Userr.Scan(&u.id, &u.name, &u.password)fmt.Printf("u: %v\n", u)}}
}// ...func main() {err := initdb()if err != nil {panic(err)}fmt.Printf("db: %v\n", db)searchAll()
}

更新

package mainimport ("database/sql""fmt""time"_ "github.com/go-sql-driver/mysql"
)var db *sql.DBfunc initdb() (err error) {db, err = sql.Open("mysql", "ellis:ellis@tcp(192.168.214.134:3306)/go_db?charset=utf8mb4&parseTime=True")if err != nil {return err}//尝试与数据库建立连接err = db.Ping()if err != nil {return err}// See "Important settings" section.db.SetConnMaxLifetime(time.Minute * 3)db.SetMaxOpenConns(10)//最大空闲db.SetMaxIdleConns(10)return nil
}type User struct {id       intname     stringpassword string
}func update() {sql := "update user_tbl set name=? where id=?"r, err := db.Exec(sql, "vv", 1)if err != nil {fmt.Printf("err: %v\n", err)} else {//打印影响行数fmt.Println(r.RowsAffected())}
}func main() {err := initdb()if err != nil {panic(err)}update()
}

删除

package mainimport ("database/sql""fmt""time"_ "github.com/go-sql-driver/mysql"
)var db *sql.DBfunc initdb() (err error) {db, err = sql.Open("mysql", "ellis:ellis@tcp(192.168.214.134:3306)/go_db?charset=utf8mb4&parseTime=True")if err != nil {return err}//尝试与数据库建立连接err = db.Ping()if err != nil {return err}// See "Important settings" section.db.SetConnMaxLifetime(time.Minute * 3)db.SetMaxOpenConns(10)//最大空闲db.SetMaxIdleConns(10)return nil
}type User struct {id       intname     stringpassword string
}func delete() {sql := "delete from user_tbl where id=?"r, err := db.Exec(sql, 1)if err != nil {fmt.Printf("err: %v\n", err)} else {//打印影响行数fmt.Println(r.RowsAffected())}
}func main() {err := initdb()if err != nil {panic(err)}delete()
}