go-mysql-test.go

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)
	}
}