1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
| package main
import (
"database/sql"
"fmt"
"os"
"strconv"
_ "github.com/go-sql-driver/mysql"
)
// Person pojo
type Person struct {
UserID int `db:"user_id"`
UserName string `db:"username"`
Sex string `db:"sex"`
Email string `db:"email"`
}
func (p *Person) String() string {
return strconv.Itoa(p.UserID) + p.UserName + p.Sex + p.Email
}
// ErrHandle 错误处理
func ErrHandle(err error, where string) {
if err != nil {
fmt.Println("[ERR]:", err, where)
os.Exit(0)
}
}
func main() {
db, err := sql.Open("mysql", "root:toor@tcp(127.0.0.1:3306)/test?charset=utf8")
ErrHandle(err, "connection mysql faild")
defer func() {
err = db.Close()
ErrHandle(err, "db.close()")
}()
// 新增操作
// 准备sql语句
insertSQL := "insert into person (`username`,`sex`,`email`) VALUES (?,?,?)"
// 预执行语句，stmt的主要方法:Exec、Query、QueryRow、Close返回*Stmt声明句柄，stmt的主要方法:Exec、Query、QueryRow、Close
stmt, err := db.Prepare(insertSQL)
ErrHandle(err, "dn.prepar(insertSQL)")
// 执行
result, err := stmt.Exec("database/sql", "man", "[email protected]")
ErrHandle(err, "stmt.Exec(insertSQL)")
// 获取插入结果的id
id, err := result.LastInsertId()
ErrHandle(err, "LastInsertId()")
fmt.Println("LastInsertId:", id)
// 修改操作
updataSQL := "UPDATE PERSON SET sex = ? WHERE user_id = ?"
// 预执行语句
stmt, err = db.Prepare(updataSQL)
ErrHandle(err, "db.prepar(updataSQL)")
// 执行 修改上面新增的数据
result, err = stmt.Exec("woman", id)
ErrHandle(err, "stmt.Exec(updataSQL)")
id, err = result.RowsAffected()
ErrHandle(err, "RowsAffected()")
fmt.Println("RowsAffected:", id)
// 查询操作
// 1. 查询一条记录 必须定义一个用来接收查询结果的变量
resultPerson := new(Person)
// 准备SQL语句
querySQL := "SELECT person.username, person.sex, person.email FROM person WHERE user_id =?"
//查询一条，返回一条结果。并赋值到resultPerson这个结构体类型的变量中,就算查询到的是多条，返回的还是一条
err = db.QueryRow(querySQL, id).Scan(&resultPerson.UserName, &resultPerson.Sex, &resultPerson.Email)
ErrHandle(err, "db.QueryRow(querySQL)")
fmt.Printf("[query row]:%v\n", resultPerson)
// 2. 查询多条记录
querySQL = "SELECT * FROM person"
// 执行查询,返回多行
queryRows, err := db.Query(querySQL)
ErrHandle(err, "db.Query")
defer func() {
err = queryRows.Close()
ErrHandle(err, "queryRows.Close()")
}()
// 遍历结果集 *Rows.NEXT() bool
for queryRows.Next() {
err = queryRows.Scan(&resultPerson.UserID, &resultPerson.UserName, &resultPerson.Sex, &resultPerson.Email)
ErrHandle(err, "queryRows.Scan(&resultPerson)")
fmt.Printf("[query rows]:%v\n\n", resultPerson)
}
// 删除操作
delSQL := "delete from person where user_id = ?"
stmt, err = db.Prepare(delSQL)
ErrHandle(err, "db.Prepare(delSQL)")
// 执行 删除上面新增的数据
result, err = stmt.Exec(id)
ErrHandle(err, "stmt.Exec(delSQL)")
id, err = result.RowsAffected()
ErrHandle(err, "RowsAffected()")
fmt.Println("RowsAffected:", id)
// 事务操作
// 开启事务
tx, err := db.Begin()
ErrHandle(err, "db.Begin()")
// 开始事务操作
_, err1 := tx.Exec("UPDATE PERSON SET sex = ? WHERE user_id = ?", "woman", id)
_, err2 := tx.Exec("DELETE FROM person WHERE user_id = ?", id)
if err1 != nil || err2 != nil {
fmt.Println("事务操作出错,开始回滚")
// 事务回滚
tx.Rollback()
} else {
// 事务提交
tx.Commit()
}
}
|