go 通过sql操作mysql
go 通过sql操作mysql
亲测、2022/08/15 20:53 北京朝阳
@
前言
表格
结果
go代码
package main
import (
"database/sql"
"fmt"
"time"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB //全局变量client
func initMySQL() (err error) {
dsn := "root:123456@tcp(127.0.0.1:3306)/test"
db, err = sql.Open("mysql", dsn)
if err != nil {
panic(err)
}
err = db.Ping() //检测是否连接成功
if err != nil {
return
}
db.SetMaxOpenConns(200) //最大连接数
db.SetMaxIdleConns(10) //连接池里最大空闲连接数。必须要比maxOpenConns小
db.SetConnMaxLifetime(time.Second * 10) //最大存活保持时间
db.SetConnMaxIdleTime(time.Second * 10) //最大空闲保持时间
return
}
func main() {
if err := initMySQL(); err != nil {
fmt.Printf("connect to db failed,err:%v\n", err)
} else {
fmt.Println("connect to db success")
}
sqlStr := "SELECT id, name FROM sys_user WHERE id=?"
var u user
//非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name)
if err != nil {
fmt.Printf("scan failed， err: %v\n", err)
return
}
fmt.Printf("id:%d,name:%s,age:%d\n", u.id, u.name)
defer db.Close()
}
//user结构体
type user struct {
id int
name string
}
sql
database: test
CREATE TABLE `sys_user` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
汇总-增删改查
结果：
package main
import (
"database/sql"
"fmt"
"time"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB
func initMySQL() (err error) {
dsn := "root:1234qwer@tcp(bj-cdb-mw08tjgs.sql.tencentcdb.com:60042)/test"
db, err = sql.Open("mysql", dsn)
if err != nil {
panic(err)
}
err = db.Ping()
if err != nil {
return
}
db.SetMaxOpenConns(200) //最大连接数
db.SetMaxIdleConns(10) //连接池里最大空闲连接数。必须要比maxOpenConns小
db.SetConnMaxLifetime(time.Second * 10) //最大存活保持时间
db.SetConnMaxIdleTime(time.Second * 10) //最大空闲保持时间
return
}
func main() {
if err := initMySQL(); err != nil {
fmt.Printf("connect to db failed,err:%v\n", err)
} else {
fmt.Println("connect to db success")
}
select_one()
queryMultiRowDemo()
insertRowDemo()
updateRowDemo()
deleteRowDemo()
defer db.Close()
}
type user struct {
id int
// age int
name string
}
//查询
func select_one() {
sqlStr := "SELECT id, name FROM sys_user WHERE id=?"
var u user
//非常重要：确保QueryRow之后调用Scan方法，否则持有的数据库链接不会被释放
err := db.QueryRow(sqlStr, 1).Scan(&u.id, &u.name)
if err != nil {
fmt.Printf("scan failed， err: %v\n", err)
return
}
fmt.Printf("id:%d,name:%s\n", u.id, u.name)
}
// 查询多条数据示例
func queryMultiRowDemo() {
sqlStr := "SELECT id,name FROM sys_user WHERE id>?"
var u user
rows, err := db.Query(sqlStr, 0)
if err != nil {
fmt.Printf("query failed, err:%v\n", err)
return
}
//非常重要，关闭rows释放持有的数据库链接
defer rows.Close()
for rows.Next() {
err := rows.Scan(&u.id, &u.name)
if err != nil {
fmt.Printf("scan failed, err: %v\n", err)
return
}
fmt.Printf("id:%d,name:%s\n", u.id, u.name)
}
}
//插入数据
func insertRowDemo() {
sqlStr := "INSERT INTO sys_user(id,name) VALUES(?,?)"
ret, err := db.Exec(sqlStr, 23, "javapub")
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
var theID int64
theID, err = ret.LastInsertId()
if err != nil {
fmt.Printf("get lastinsert ID failed,err:%v\n", err)
return
}
fmt.Printf("insert success, the id is %d\n", theID)
}
//更新数据
func updateRowDemo() {
sqlStr := "UPDATE sys_user SET name=? WHERE id=?"
ret, err := db.Exec(sqlStr, "i love javapub", 23)
if err != nil {
fmt.Printf("updated failed,err:%v\n", err)
return
}
var n int64
n, err = ret.RowsAffected()
if err != nil {
fmt.Printf("get rowsAffected failed,err:%v\b", err)
return
}
fmt.Printf("updated success, the rows affected is %d\n", n)
}
//删除数据
func deleteRowDemo() {
sqlStr := "DELETE FROM sys_user WHERE id=?"
ret, err := db.Exec(sqlStr, 1)
if err != nil {
fmt.Printf("delete failed,err:%v\n", err)
return
}
var n int64
n, err = ret.RowsAffected()
if err != nil {
fmt.Printf("get RowsAffected failed, err:%v\n", err)
return
}
fmt.Printf("delete success, affected rows:%d\n", n)
}
