Examples

Prepared Statements

Assume an empty table with the layout

+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| number       | int(11) | NO   | PRI | NULL    |       |
| squareNumber | int(11) | NO   |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+

In this example we prepare two statements - one for inserting tuples (rows) and one to query.

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/Go-SQL-Driver/MySQL"
)

func main() {
	db, err := sql.Open("mysql", "root:root@/database")
	if err != nil {
		panic(err)
	}

	// Prepare statement for inserting data
	stmtIns, err := db.Prepare("INSERT INTO squareNum VALUES( ?, ? )") // ? = placeholder
	if err != nil {
		panic(err)
	}

	// Prepare statement for reading data
	stmtOut, err := db.Prepare("SELECT squareNumber FROM squarenum WHERE number = ?")

	// Insert square numbers for 0-24 in the database
	for i := 0; i < 25; i++ {
		_, err = stmtIns.Exec(i, (i * i)) // Insert tuples (i, i^2)
		if err != nil {
			panic(err)
		}
	}

	// Query the square-number of 13
	var squareNum int
	err = stmtOut.QueryRow(13).Scan(&squareNum) // WHERE number = 13
	if err != nil {
		panic(err)
	}
	fmt.Printf("The square number of 13 is: %d", squareNum)
}

time.Time from DATETIMEvalue

Due to Issue #9 Go-MySQL-Driver currently doesn't support Scan(*time.Time). Here is a simple workaround:

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/Go-SQL-Driver/MySQL"
)

func main() {
	// Open database connection
	db, err := sql.Open("mysql", "user:password@/dbname?charset=utf8")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// Execute the query
	rows, err := db.Query("SELECT timestamp FROM table")
	if err != nil {
		panic(err)
	}

	var ts string
	var tt time.Time

	// Fetch rows
	for rows.Next() {
		// Scan the value to string
		err = rows.Scan(&ts)
		if err != nil {
			panic(err)
		}

		// Parse the string to time.Time
		tt, err = time.Parse("2006-01-02 15:04:05", ts)
		if err != nil {
			panic(err)
		}

		// Do something... here we just print the value
		fmt.Println(tt.String())
	}
}

RawBytes

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/Go-SQL-Driver/MySQL"
)

func main() {
	// Open database connection
	db, err := sql.Open("mysql", "user:password@/dbname?charset=utf8")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	// Execute the query
	rows, err := db.Query("SELECT * FROM table")
	if err != nil {
		panic(err)
	}

	// Get column names
	columns, err := rows.Columns()
	if err != nil {
		panic(err)
	}

	// Make a slice for the values
	values := make([]*sql.RawBytes, len(columns))

	// rows.Scan wants '[]interface{}' as an argument, so we must copy the
	// references into such a slice
	// See http://code.google.com/p/go-wiki/wiki/InterfaceSlice for details
	scanArgs := make([]interface{}, len(values))
	for i := range values {
		scanArgs[i] = &values[i]
	}

	// Fetch rows
	for rows.Next() {
		// get RawBytes from data
		err = rows.Scan(scanArgs...)
		if err != nil {
			panic(err)
		}

		// Now do something with the data.
		// Here we just print each column as a string.
		var value string
		for i, col := range values {
			// Since RawBytes might be nil, we must check this before
			// converting to string
			if col == nil {
				value = "NULL"
			} else {
				value = string(*col)
			}
			fmt.Println(columns[i], ": ", value)
		}
		fmt.Println("-----------------------------------")
	}
}

Feel free to contribute your own examples!