前言:
本文主要介绍如何通过database/sql包操作mysql
一.引入包
import ( "database/sql" _ "github.com/go-sql-driver/mysql" //mysql驱动 )
二.创建连接
func main() { dsn := "root:root@127.0.0.1:3306/d_liudutui?charset=utf-8" conn, err := sql.Open("mysql",dsn) db = conn if err != nil { panic(err) } defer db.Close() }
1.dsn格式: username:password@tcp(ip:port)/dbname
2.程序结束时关闭连接
三.CURD
1.添加 sql预编译->exec()执行
func Create() { stmt, err := db.Prepare("INSERT INTO t_user (`name`,gender) VALUES (?,?)") defer stmt.Close() if err != nil { panic(err) } ret, err := stmt.Exec("kunkun", 1) if err != nil { panic(err) } fmt.Println(ret.RowsAffected()) }
2.删除 sql预编译->exec()执行
func Delete() { stmt, err := db.Prepare("DELETE FROM t_user WHERE id = ?") if err != nil { panic(err) } ret, err := stmt.Exec("1") if err != nil { panic(err) } fmt.Println(ret.RowsAffected()) }
3.修改 sql预编译->exec()执行
func Update() { stmt, err := db.Prepare("UPDATE t_user SET `name` = ? WHERE id = ?") if err != nil { panic(err) } ret, err := stmt.Exec("new_name", 1) if err != nil { panic(err) } fmt.Println(ret.RowsAffected()) }
4.查询
sql预编译->Query()查询->Next()遍历查询结果->Scan()获取查询结果
func Query() { stmt, err := db.Prepare("SELECT * FROM t_user WHERE id in (?,?,?)") if err != nil { panic(err) } rows, err := stmt.Query(1,2,3) if err != nil { panic(err) } type User struct { ID string Name string Gender int } var list []*User for rows.Next() { user := User{} err := rows.Scan(&user.ID, &user.Name, &user.Gender) if err != nil { panic(err) } list = append(list, &user) } }