安装第三方库

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

package mainimport ("database/sql""fmt"_ "github.com/go-sql-driver/mysql" // init()"time"
)var db *sql.DB //定义全局的数据库连接池对象type user struct {id, age intname    string
}

初始化数据库连接

func initDB() (err error) {dataSourceName := "root:12345678@tcp(localhost:3306)/test_db"db, err = sql.Open("mysql", dataSourceName)if err != nil {return}err = db.Ping() // 测试连接if err != nil {return}// See "Important settings" section.db.SetConnMaxLifetime(time.Minute * 3) //连接池⾥⾯的连接最⼤存活时长;// maxLifeTime必须要⽐mysql服务器设置的wait_timeout⼩，否则会导致golang连接池依然保留已被mysql服务器关闭了的连接。db.SetMaxOpenConns(10) //设置与数据库建立连接的最大数量,如果n<=0,代表无限大db.SetMaxIdleConns(10) //设置连接池中最大的闲置连接数量,如果n<=0,不会保留闲置连接fmt.Println("database connection is successful")return
}

查询单行

func queryOne(id int) {//1.查询单条记录sqlStr := "select id,name,age from user where id=?;"//2.拿到结果保存到u1中var u1 user// scan会自动释放连接池的占用err := db.QueryRow(sqlStr, id).Scan(&u1.id, &u1.name, &u1.age) // 从连接池取一个连接去执行这个sql语句if err != nil {fmt.Println(err)return}fmt.Printf("u1:%#v", u1)
}

查询多行

func queryMore(id int) {sqlStr := `select id,name,age from user where id>?;`rowsObj, err := db.Query(sqlStr, id)if err != nil {return}// 一定要关闭defer func(rowsObj *sql.Rows) {err := rowsObj.Close()if err != nil {return}}(rowsObj)for rowsObj.Next() {var u2 usererr := rowsObj.Scan(&u2.id, &u2.name, &u2.age)if err != nil {return}fmt.Printf("u2:%#v\n", u2)}
}

插入数据

func insert(name string, age int) {sqlStr := `insert into user(name,age) values(?,?);`ret, err := db.Exec(sqlStr, name, age)if err != nil {return}theID, err := ret.LastInsertId() // 获取新插入数据的IDif err != nil {return}fmt.Println("theID:", theID)
}

更新数据

func update(id int, age int) {sqlStr := `update user  set age=? where id=?;`ret, err := db.Exec(sqlStr, age, id)if err != nil {return}n, err := ret.RowsAffected() // 获取影响的行数if err != nil {return}fmt.Println("affect rows no:", n)
}

删除数据

func delete(name string) {sqlStr := `delete from user where name=?;`ret, err := db.Exec(sqlStr, name)if err != nil {return}n, err := ret.RowsAffected() // 获取影响的行数if err != nil {return}fmt.Println("delete rows no:", n)
}

执行

func main() {err := initDB()if err != nil {fmt.Printf("Initialized database connection failed:%v:", err)return}//关闭数据库defer func(db *sql.DB) {err := db.Close()if err != nil {return}}(db)queryOne(1)queryMore(0)//insert("yangmi", 35)//queryMore(0)update(1, 86)queryMore(0)delete("yangmi")queryMore(0)
}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！