forked from roostAI/learning-go
-
Notifications
You must be signed in to change notification settings - Fork 1
/
mysql.go
122 lines (97 loc) · 2.15 KB
/
mysql.go
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
120
121
122
package main
import (
"database/sql"
"fmt"
"log"
"time"
_ "github.com/go-sql-driver/mysql"
)
func main() {
db, err := sql.Open("mysql", "root:root@(127.0.0.1:3306)/godb?parseTime=true")
if err != nil {
log.Fatal(err)
}
if err := db.Ping(); err != nil {
log.Fatal(err)
}
dropTable(db)
createTable(db)
username := "johndoe"
password := "secret"
insertUser(db, username, password)
queryUser(db, 1)
queryAllUser(db)
deleteUser(db, 1)
}
func dropTable(db *sql.DB) {
if _, err := db.Exec("DROP TABLE IF EXISTS `users`"); err != nil {
log.Fatal(err)
}
}
func createTable(db *sql.DB) {
query := `
CREATE TABLE users (
id INT AUTO_INCREMENT,
username TEXT NOT NULL,
password TEXT NOT NULL,
created_at DATETIME,
PRIMARY KEY (id)
);`
if _, err := db.Exec(query); err != nil {
log.Fatal(err)
}
}
func insertUser(db *sql.DB, username string, password string) {
createdAt := time.Now()
result, err := db.Exec(`INSERT INTO users (username, password, created_at) VALUES (?, ?, ?)`, username, password, createdAt)
if err != nil {
log.Fatal(err)
}
id, err := result.LastInsertId()
fmt.Println(id)
}
func queryUser(db *sql.DB, userID int) {
var (
id int
username string
password string
createdAt time.Time
)
query := "SELECT id, username, password, created_at FROM users WHERE id = ?"
if err := db.QueryRow(query, userID).Scan(&id, &username, &password, &createdAt); err != nil {
log.Fatal(err)
}
fmt.Println(id, username, password, createdAt)
}
func queryAllUser(db *sql.DB) {
type user struct {
id int
username string
password string
createdAt time.Time
}
rows, err := db.Query(`SELECT id, username, password, created_at FROM users`)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
var users []user
for rows.Next() {
var u user
err := rows.Scan(&u.id, &u.username, &u.password, &u.createdAt)
if err != nil {
log.Fatal(err)
}
users = append(users, u)
}
if err := rows.Err(); err != nil {
log.Fatal(err)
}
fmt.Printf("%#v", users)
}
func deleteUser(db *sql.DB, id int) {
_, err := db.Exec(`DELETE FROM users WHERE id = ?`, id)
if err != nil {
log.Fatal(err)
}
}