Go MySQL example

gistfile1.go

package main

import (
	"database/sql"
	"errors"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"log"
)

type Row map[string]sql.Scanner

type Rows struct {
	*sql.Rows
	columns  []string
	scanners []interface{}
	row      Row
}

type scanner struct {
	rows   *Rows
	column string
}

func (s *scanner) Scan(src interface{}) error {
	column, ok := s.rows.row[s.column]
	if !ok {
		return nil // ignore unknown columns
	}
	return column.Scan(src)
}

func NewRows(sqlRows *sql.Rows) (*Rows, error) {
	columns, err := sqlRows.Columns()
	if err != nil {
		return nil, err
	}
	r := Rows{sqlRows, nil, nil, nil}
	scanners := make([]interface{}, len(columns))
	for i := range scanners {
		scanners[i] = &scanner{rows: &r, column: columns[i]}
	}
	r.columns = columns
	r.scanners = scanners
	return &r, nil
}

func (r *Rows) Scan(row Row) error {
	if row == nil {
		return errors.New("row is nil")
	}
	r.row = row
	err := r.Rows.Scan(r.scanners...)
	r.row = nil
	return err
}

func RowsToMap(sqlRows *sql.Rows, newRow func() Row) ([]Row, error) {
	rows, err := NewRows(sqlRows)
	if err != nil {
		return nil, err
	}
	rets := make([]Row, 0)
	for rows.Next() {
		row := newRow()
		err = rows.Scan(row)
		if err != nil {
			return nil, err
		}
		rets = append(rets, row)
	}
	return rets, nil
}

func main() {
	// [username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]
	// user:password@tcp(localhost:5555)/dbname?tls=skip-verify&autocommit=true
	db, err := sql.Open("mysql", "root:root@tcp(localhost:8889)/dbname?charset=utf8mb4")
	if err != nil {
		log.Fatal(err)
		return
	}
	defer db.Close()

	err = db.Ping()
	if err != nil {
		log.Fatal(err)
		return
	}

	sqlRows, err := db.Query("SELECT * FROM `foo`")
	if err != nil {
		log.Fatal(err)
		return
	}

	rows, err := RowsToMap(sqlRows, func() Row {
		return Row{
			"id":         &sql.NullInt64{},
			"memo":       &sql.NullString{},
			"created":    &sql.NullInt64{},
		}
	})
	if err != nil {
		log.Fatal(err)
		return
	}

	for _, row := range rows {
		fmt.Println("{")
		for k, v := range row {
			fmt.Printf("  %v: %v,\n", k, v)
		}
		fmt.Println("}\n")
	}
}

呼び出し元に map[string]sql.Scanner を生成させ、そこにデータを詰め込んでいく例。mapのスライスが得られる。

行ごとにカラム名を持っているのが、メモリの無駄ではある。