package main
import (
"database/sql"
"fmt"
// 初始化驱动
_ "github.com/go-sql-driver/mysql"
"log"
)
// 执行 SQL,可以用来执行不需要返回结果集的 SQL
func execSql(conn *sql.DB, sql string, args ...interface{}) {
execResp, err := conn.Exec(sql, args...)
if err != nil {
log.Fatalf("exec sql [%s] failed, err: %v\n", sql, err)
}
// 查看受影响的行数
fmt.Println(execResp.RowsAffected())
}
// 多行查询
func querySql(conn *sql.DB, sql string) {
var (
id int
name string
age int
)
queryResult, err := conn.Query(sql)
if err != nil {
log.Fatalf("query sql [%s] failed, err: %v\n", sql, err)
}
for queryResult.Next() {
queryResult.Scan(&id, &name, &age)
fmt.Printf("id: %d, name: %s, age: %d\n", id, name, age)
}
}
// 单行查询
func queryOneSql(conn *sql.DB, sql string, args ...interface{}) {
var (
id int
name string
age int
)
query := conn.QueryRow(sql, args...)
err := query.Scan(&id, &name, &age)
if err != nil {
log.Fatalf("exec query raw sql [%s] failed, err: %v", sql, err)
}
fmt.Printf("id: %d, name: %s, age: %d", id, name, age)
}
func changeMoney(tx *sql.Tx, accountId int, money float32) error {
_, err := tx.Exec("update account set money = money + ? where id = ?", money, accountId)
return err
}
// 事务示例
func execTrans(conn *sql.DB) {
execSql(conn, `
create table if not exists account(
id int primary key auto_increment,
name varchar(24),
money float
) engine=innodb default charset utf8mb4
`)
//execSql(conn, "insert into account(name, money) values('u1', 100)")
//execSql(conn, "insert into account(name, money) values('u2', 100)")
tx, err := conn.Begin()
if err != nil {
log.Fatalf("start transaction failed, err: %v", err)
}
err1 := changeMoney(tx, 1, -10)
err2 := changeMoney(tx, 2, 10)
if err1 == nil && err2 == nil {
err := tx.Commit()
if err != nil {
log.Fatalf("commit transaction failed, err: %v", err)
}
fmt.Println("trans money success!")
} else {
err := tx.Rollback()
if err != nil {
log.Fatalf("rollback transaction failed, err: %v", err)
}
fmt.Println("trans money failed!")
}
}
// 预解析 SQL
func prepareSql(conn *sql.DB) {
sqlStr := "insert into t1(name, age) values(?, ?)"
prepareStatment, err := conn.Prepare(sqlStr)
if err != nil {
log.Fatalf("prepare sql [%s] failed, err: %v", sqlStr, err)
}
for i := 0; i < 10; i++ {
name := fmt.Sprintf("u%d", i)
age := 10 + i
_, err := prepareStatment.Exec(name, age)
if err != nil {
log.Fatalf("exec prepare statment with params [name: %s, age %d]", name, age)
}
}
}
func main() {
dsn := "root:123456@tcp(172.18.80.77:3306)/golang?charset=utf8mb4&loc=PRC"
conn, err := sql.Open("mysql", dsn)
//关闭数据库连接
defer conn.Close()
if err != nil {
log.Fatalf("open mysql conn failed, err: %v", err)
}
// 检查数据库是否可以正常建立连接
err = conn.Ping()
if err != nil {
log.Fatalf("ping failed, err: %v", err)
}
execSql(conn, `
create table if not exists t1(
id int primary key auto_increment,
name varchar(24),
age tinyint
) engine=innodb default charset utf8mb4
`)
execSql(conn, "insert into t1(name, age) values(?, ?);", "zze3", 18)
querySql(conn, "select id, name, age from t1")
queryOneSql(conn, "select id, name, age from t1 where id = ?", 2)
execTrans(conn)
prepareSql(conn)
}
评论区