· 6 years ago · Jun 21, 2019, 01:14 PM
1package main
2
3import (
4 "database/sql"
5 "flag"
6 "fmt"
7 "log"
8 "time"
9
10 // Yes ... this is required !!!
11 _ "github.com/go-sql-driver/mysql"
12)
13
14var (
15 adminUser = flag.String("user", "root", "set db admin username")
16 adminPass = flag.String("pass", "admin", "set db admin password")
17 dbHostname = flag.String("host", "tcp(127.0.0.1:3306)", "set db hostname")
18 dbDName = flag.String("dname", "testgo", "set database name")
19 dbTName = flag.String("tname", "example", "set table name")
20)
21
22func main() {
23 dbSrc := fmt.Sprintf("%s:%s@%s/?parseTime=true", *adminUser, *adminPass, *dbHostname)
24
25 db, err := sql.Open("mysql", dbSrc)
26 if err != nil {
27 log.Fatalf("sql.Open failed: %v", err)
28 }
29 defer db.Close()
30
31 // sql.Open() does not establish any connections to the database ...
32
33 err = db.Ping()
34 if err != nil {
35 log.Fatalf("db.Ping failed: %v", err)
36 }
37
38 // CREATE DATABASE
39
40 _, err = db.Exec("CREATE DATABASE IF NOT EXISTS " + *dbDName)
41 if err != nil {
42 log.Fatalf("db.Exec(CREATE DATABASE) failed: %v", err)
43 }
44
45 // USE DATABASE : should also create a user access and log with this credential
46
47 _, err = db.Exec("USE " + *dbDName)
48 if err != nil {
49 log.Fatalf("db.Exec(USE) failed: %v", err)
50 }
51
52 // CREATE TABLE
53
54 _, err = db.Exec(`CREATE TABLE IF NOT EXISTS ` + *dbTName + ` (
55 id INT(11) NOT NULL AUTO_INCREMENT,
56 data VARCHAR(32) NOT NULL,
57 creation DATETIME NOT NULL,
58 PRIMARY KEY (id)
59 )`)
60 if err != nil {
61 log.Fatalf("db.Exec(CREATE TABLE) failed: %v", err)
62 }
63
64 // INSERT
65
66 res, err := db.Exec(`INSERT INTO `+*dbTName+` SET data=?, creation=NOW()`, "Hello World")
67 if err != nil {
68 log.Fatalf("db.Exec(INSERT) failed: %v", err)
69 }
70
71 rowCount, err := res.RowsAffected()
72 if err != nil {
73 log.Fatalf("res.RowsAffected failed: %v", err)
74 }
75
76 if rowCount != 1 {
77 log.Fatalf("INSERT: expected to affect 1 row, affected %d", rowCount)
78 }
79
80 lastID, err := res.LastInsertId()
81 if err != nil {
82 log.Fatalf("res.LastInsertId failed: %v\n", err)
83 }
84
85 fmt.Printf("inserted %d\n", lastID)
86
87 // SELECT
88
89 rows, err := db.Query(` SELECT X.id, X.data, X.creation
90 FROM `+*dbTName+` X
91 WHERE X.id < ?
92 `, 10)
93 if err != nil {
94 log.Fatalf("SELECT failed: %v\n", err)
95 }
96 defer rows.Close()
97
98 for rows.Next() {
99 var (
100 id uint64
101 data string
102 creation time.Time
103 )
104 err = rows.Scan(&id, &data, &creation)
105 if err != nil {
106 log.Fatalf("rows.Scan failed: %v\n", err)
107 }
108
109 fmt.Printf("%-5d | %-32s | %v\n", id, data, creation)
110 }
111}