Last active
June 10, 2019 10:53
-
-
Save xigh/e5db00adddfc2ace4d0b5b046cd16f92 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| package main | |
| import ( | |
| "database/sql" | |
| "flag" | |
| "fmt" | |
| "log" | |
| "time" | |
| // Yes ... this is required !!! | |
| _ "github.com/go-sql-driver/mysql" | |
| ) | |
| var ( | |
| adminUser = flag.String("user", "root", "set db admin username") | |
| adminPass = flag.String("pass", "admin", "set db admin password") | |
| dbHostname = flag.String("host", "tcp(127.0.0.1:3306)", "set db hostname") | |
| dbDName = flag.String("dname", "testgo", "set database name") | |
| dbTName = flag.String("tname", "example", "set table name") | |
| ) | |
| func main() { | |
| dbSrc := fmt.Sprintf("%s:%s@%s/?parseTime=true", *adminUser, *adminPass, *dbHostname) | |
| db, err := sql.Open("mysql", dbSrc) | |
| if err != nil { | |
| log.Fatalf("sql.Open failed: %v", err) | |
| } | |
| defer db.Close() | |
| // sql.Open() does not establish any connections to the database ... | |
| err = db.Ping() | |
| if err != nil { | |
| log.Fatalf("db.Ping failed: %v", err) | |
| } | |
| // CREATE DATABASE | |
| _, err = db.Exec("CREATE DATABASE IF NOT EXISTS " + *dbDName) | |
| if err != nil { | |
| log.Fatalf("db.Exec(CREATE DATABASE) failed: %v", err) | |
| } | |
| // USE DATABASE : should also create a user access and log with this credential | |
| _, err = db.Exec("USE " + *dbDName) | |
| if err != nil { | |
| log.Fatalf("db.Exec(USE) failed: %v", err) | |
| } | |
| // CREATE TABLE | |
| _, err = db.Exec(`CREATE TABLE IF NOT EXISTS ` + *dbTName + ` ( | |
| id INT(11) NOT NULL AUTO_INCREMENT, | |
| data VARCHAR(32) NOT NULL, | |
| creation DATETIME NOT NULL, | |
| PRIMARY KEY (id) | |
| )`) | |
| if err != nil { | |
| log.Fatalf("db.Exec(CREATE TABLE) failed: %v", err) | |
| } | |
| // INSERT | |
| res, err := db.Exec(`INSERT INTO `+*dbTName+` SET data=?, creation=NOW()`, "Hello World") | |
| if err != nil { | |
| log.Fatalf("db.Exec(INSERT) failed: %v", err) | |
| } | |
| rowCount, err := res.RowsAffected() | |
| if err != nil { | |
| log.Fatalf("res.RowsAffected failed: %v", err) | |
| } | |
| if rowCount != 1 { | |
| log.Fatalf("INSERT: expected to affect 1 row, affected %d", rowCount) | |
| } | |
| lastID, err := res.LastInsertId() | |
| if err != nil { | |
| log.Fatalf("res.LastInsertId failed: %v\n", err) | |
| } | |
| fmt.Printf("inserted %d\n", lastID) | |
| // SELECT | |
| rows, err := db.Query(` SELECT X.id, X.data, X.creation | |
| FROM `+*dbTName+` X | |
| WHERE X.id < ? | |
| `, 10) | |
| if err != nil { | |
| log.Fatalf("SELECT failed: %v\n", err) | |
| } | |
| defer rows.Close() | |
| for rows.Next() { | |
| var ( | |
| id uint64 | |
| data string | |
| creation time.Time | |
| ) | |
| err = rows.Scan(&id, &data, &creation) | |
| if err != nil { | |
| log.Fatalf("rows.Scan failed: %v\n", err) | |
| } | |
| fmt.Printf("%-5d | %-32s | %v\n", id, data, creation) | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment