mysqldriver

package module
v0.0.0-...-31336c0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Aug 18, 2020 License: MIT Imports: 8 Imported by: 1

README

mysqldriver-go

Build Status GoDoc

Table of contents

Motivation

There are many MySQL drivers that implement the database/sql interface. However, using this generic interface, especially in the Scan method, requires the storage of many objects in the heap.

Reading a massive number of records from a DB can significantly increase the Garbage Collection (GC) pause-time that can be very sensitive for high-throughput, low-latency applications.

Because of the above and the need for a GC-friendly MySQL driver, we've decided not to follow the database/sql interface and write this driver.

The following Benchmark was run on a MacBook Pro (Retina, 13-inch, Late 2013), 2.8 GHz Intel Core i7, 16 GB 1600 MHz DDR3 using Go 1.5.2:

comparison

➜  benchmarks git:(master) ✗ go run main.go 
mysqldriver: records read 100  HEAP 129  time 722.293µs
go-sql-driver: records read 100  HEAP 335  time 716.416µs
mysqldriver: records read 1000  HEAP 1015  time 633.537µs
go-sql-driver: records read 1000  HEAP 3010  time 798.109µs
mysqldriver: records read 10000  HEAP 10092  time 3.137886ms
go-sql-driver: records read 10000  HEAP 30010  time 3.377241ms

Goal

The main goals of this library are: performance over flexibility, simplicity over complexity. Any new feature shouldn't decrease the performance of the existing code base.

Any improvements to productivity are always welcome. There is no plan to convert this library into an ORM. The plan is to keep it simple and, still keep supporting all of the MySQL features.

Documentation

  1. API Reference
  2. Official MySQL Protocol Documentation

Dependencies

  1. pubnative/mysqlproto-go MySQL protocol implementation

Installation

go get github.com/pubnative/mysqldriver-go

Quick Start

package main

import (
	"fmt"
	"strconv"

	"github.com/pubnative/mysqldriver-go"
)

type Person struct {
	Name    string
	Age     int
	Married bool
}

func main() {
	// initialize DB pool of 10 connections
	db := mysqldriver.NewDB("root@tcp(127.0.0.1:3306)/test", 10)

	// obtain connection from the pool
	conn, err := db.GetConn()
	if err != nil {
		panic(err)
	}

	if _, err := conn.Exec(`CREATE TABLE IF NOT EXISTS people (
        id int NOT NULL AUTO_INCREMENT,
    	name varchar(255),
    	age int,
        married tinyint,
        PRIMARY KEY (id)
    )`); err != nil {
		panic(err)
	}

	for i := 0; i < 10; i++ {
		num := strconv.Itoa(i)
		_, err := conn.Exec(`
            INSERT INTO people(name,age,married) 
            VALUES("name` + num + `",` + num + `,` + strconv.Itoa(i%2) + `)
        `)
		if err != nil {
			panic(err)
		}
	}

	rows, err := conn.Query("SELECT name,age,married FROM people")
	if err != nil {
		panic(err)
	}

	for rows.Next() { // switch cursor to the next unread row
		person := Person{
			Name:    rows.String(),
			Age:     rows.Int(),
			Married: rows.Bool(),
		}
		fmt.Printf("%#v\n", person)
	}

	// always should be checked if there is an error during reading rows
	if err := rows.LastError(); err != nil {
		panic(err)
	}

	// return connection to the pool for further reuse
	if err := db.PutConn(conn); err != nil {
		panic(err)
	}

	if errors := db.Close(); errors != nil { // close the pool and all connections in it
	    for _, err := range errors {
	        _ = err // handle error
        }
	}
}

Documentation

Overview

Package mysqldriver is a GC optimized MySQL driver

Concurrency

DB struct manages pool of connections to MySQL. Connection itself isn't thread-safe, so it should be obtained per every go-routine. It's important to return a connection back to the pool when it's not needed for further reuse. 

db := mysqldriver.NewDB("root@tcp(127.0.0.1:3306)/test", 10)
for i := 0; i < 10; i++ {
	go func() {
		conn, err := db.GetConn()
		if err != nil {
			// handle error
		}
		defer db.PutConn(conn) // return connection to the pool
		// perform queries
	}()
}

Reading rows

mysqldriver reads data from the DB in a sequential order which means the whole result set of first query must be read before executing another one.

Number of read column's values and their types must match with the number of columns in a query. 

rows, err := conn.Query("SELECT id, name, married FROM people")
if err != nil {
	// handle error
}
for rows.Next() { // always read all rows
	id := rows.Int()       // order of columns must be preserved
	name := rows.String()  // type of the column must match with DB type
	married := rows.Bool() // all column's values must be read
}
if err = rows.LastError(); err != nil {
	// Handle error if any occurred during reading packets from DB.

	// When error occurred during reading from the stream
 	// connection must be manually closed to prevent further reuse.
 	conn.Close()
}

When there is no need to read the whole result set, for instance when error occurred during parsing data, connection must be closed to prevent further reuse as it's in invalid state. 

conn, err := db.GetConn()
if err != nil {
	// handle error
}

// It's safe to return closed connection to the pool.
// It will be discarded and won't be reused.
defer db.PutConn(conn)

rows, err := db.Query("SELECT name FROM people")
if err != nil {
	// handle error
}

for rows.Next() {
	rows.Int() // causes type error
}

if err = rows.LastError(); err != nil {
	// Close the connection to make sure
	// it won't be reused by the pool.
	conn.Close()
}

Index

Examples

Constants

This section is empty.

Variables

View Source 
var ErrClosedDB = errors.New("mysqldriver: can't get connection from the closed DB")

Functions

This section is empty.

Types

type Conn 

type Conn struct {
	// contains filtered or unexported fields
}

Conn represents connection to MySQL server

func NewConn 

func NewConn(username, password, protocol, address, database string, readTimeout time.Duration) (*Conn, error)

NewConn establishes a connection to the DB. After obtaining the connection, it sends "SET NAMES utf8" command to the DB

func NewConnContext 

func NewConnContext(ctx context.Context, username, password, protocol, address,
	database string, readTimeout time.Duration) (*Conn, error)

NewConnContext establishes a connection to the DB. After obtaining the connection, it sends "SET NAMES utf8" command to the DB

Go Context is only used to establish a TCP connection. TODO use Go Context to establish a MySQL connection.

func (*Conn) Close 

func (c *Conn) Close() error

Close closes the connection

func (*Conn) Exec 

func (c *Conn) Exec(sql string) (mysqlproto.OKPacket, error)

Exec executes queries or other commands which expect to return OK_PACKET including INSERT/UPDATE/DELETE queries. For SELECT query see func (Conn) Query 

 okPacket, err := conn.Exec("DELETE FROM dogs WHERE id = 1")
	if err == nil {
 	return nil // query was performed successfully
 }
 if errPacket, ok := err.(mysqlproto.ERRPacket); ok {
 	return errPacket // retrieve more information about the error
 } else {
 	return err // generic error
 }

func (*Conn) Query 

func (c *Conn) Query(sql string) (*Rows, error)

Query function is used only for SELECT query. For all other queries and commands see func (c Conn) Exec

Example (Default) 
db := NewDB("root@tcp(127.0.0.1:3306)/test", 10, time.Duration(0))
conn, err := db.GetConn()
if err != nil {
	// handle error
}
rows, err := conn.Query("SELECT id,badge,age,honors,length,weight,height,male,name,info FROM dogs")
if err != nil {
	// handle error
}
for rows.Next() {
	_ = rows.Int()     // id
	_ = rows.Int8()    // badge
	_ = rows.Int16()   // age
	_ = rows.Int32()   // honors
	_ = rows.Int64()   // length
	_ = rows.Float32() // weight
	_ = rows.Float64() // height
	_ = rows.Bool()    // male
	_ = rows.String()  // name
	_ = rows.Bytes()   // info
}
if err = rows.LastError(); err != nil {
	// handle error

	// when error occurred during reading from the stream
	// connection must be manually closed to prevent further reuse
	conn.Close()
}

Output:
Example (Null) 
db := NewDB("root@tcp(127.0.0.1:3306)/test", 10, time.Duration(0))
conn, err := db.GetConn()
if err != nil {
	// handle error
}
rows, err := conn.Query("SELECT id,badge,age,honors,length,weight,height,male,name,info FROM dogs")
if err != nil {
	// handle error
}
for rows.Next() {
	_, _ = rows.NullInt()     // id
	_, _ = rows.NullInt8()    // badge
	_, _ = rows.NullInt16()   // age
	_, _ = rows.NullInt32()   // honors
	_, _ = rows.NullInt64()   // length
	_, _ = rows.NullFloat32() // weight
	_, _ = rows.NullFloat64() // height
	_, _ = rows.NullBool()    // male
	_, _ = rows.NullString()  // name
	_, _ = rows.NullBytes()   // info
}
if err = rows.LastError(); err != nil {
	// handle error

	// when error occurred during reading from the stream
	// connection must be manually closed to prevent further reuse
	conn.Close()
}

Output:

func (*Conn) Stats 

func (c *Conn) Stats() Stats

Stats returns statistics about the connection

type DB 

type DB struct {
	OnDial func(conn *Conn) error // called when new connection is established
	// contains filtered or unexported fields
}

DB manages pool of connection

func NewDB 

func NewDB(dataSource string, pool int, readTimeout time.Duration) *DB

NewDB initializes pool of connections but doesn't establishes connection to DB.

Pool size is fixed and can't be resized later. DataSource parameter has the following format: [username[:password]@][protocol[(address)]]/dbname

Example

Initializes the pool for 10 connections 

NewDB("root@tcp(127.0.0.1:3306)/test", 10, time.Duration(0))

Output:

func (*DB) Close 

func (db *DB) Close() []error

Close closes all connections in a pool and doesn't allow to establish new ones to DB any more. Returns slice of errors if any occurred.

func (*DB) GetConn 

func (db *DB) GetConn() (*Conn, error)

GetConn gets connection from the pool if there is one or establishes a new one.This method always returns the connection regardless the pool size. When DB is closed, this method returns ErrClosedDB error.

func (*DB) PutConn 

func (db *DB) PutConn(conn *Conn) (err error)

PutConn returns connection to the pool. When pool is reached, connection is closed and won't be further reused. If connection is already closed, PutConn will discard it so it's safe to return closed connection to the pool.

type Row 

type Row struct {
	// contains filtered or unexported fields
}

Row represents a single DB row of the query results

func (Row) Bool 

func (r Row) Bool(col string) bool

Bool returns value as a bool. NULL value is represented as false. Bool method uses strconv.ParseBool to convert string into bool. (see https://golang.org/pkg/strconv/#ParseBool)

func (Row) Bytes 

func (r Row) Bytes(col string) []byte

Bytes returns value as slice of bytes. NULL value is represented as empty slice.

func (Row) Float32 

func (r Row) Float32(col string) float32

Float32 returns value as a float32. NULL value is represented as 0.0. Float32 method uses strconv.ParseFloat to convert string into float32. (see https://golang.org/pkg/strconv/#ParseFloat)

func (Row) Float64 

func (r Row) Float64(col string) float64

Float64 returns value as a float64. NULL value is represented as 0.0. Float64 method uses strconv.ParseFloat to convert string into float64. (see https://golang.org/pkg/strconv/#ParseFloat)

func (Row) Int 

func (r Row) Int(col string) int

Int returns value as an int. NULL value is represented as 0. Int method uses strconv.Atoi to convert string into int. (see https://golang.org/pkg/strconv/#Atoi)

func (Row) Int16 

func (r Row) Int16(col string) int16

Int16 returns value as an int16. NULL value is represented as 0. Int16 method uses strconv.ParseInt to convert string into int16. (see https://golang.org/pkg/strconv/#ParseInt)

func (Row) Int32 

func (r Row) Int32(col string) int32

Int32 returns value as an int32. NULL value is represented as 0. Int32 method uses strconv.ParseInt to convert string into int32. (see https://golang.org/pkg/strconv/#ParseInt)

func (Row) Int64 

func (r Row) Int64(col string) int64

Int64 returns value as an int64. NULL value is represented as 0. Int64 method uses strconv.ParseInt to convert string into int64. (see https://golang.org/pkg/strconv/#ParseInt)

func (Row) Int8 

func (r Row) Int8(col string) int8

Int8 returns value as an int8. NULL value is represented as 0. Int8 method uses strconv.ParseInt to convert string into int8. (see https://golang.org/pkg/strconv/#ParseInt)

func (Row) NullBool 

func (r Row) NullBool(col string) (bool, bool)

NullBool returns value as a bool and NULL indicator. When value is NULL, second parameter is true. NullBool method uses strconv.ParseBool to convert string into bool. (see https://golang.org/pkg/strconv/#ParseBool)

func (Row) NullBytes 

func (r Row) NullBytes(col string) ([]byte, bool)

NullBytes returns value as a slice of bytes and NULL indicator. When value is NULL, second parameter is true.

IMPORTANT. This function panics if it can't find the column by the name.

All other type-specific functions are based on this one.

func (Row) NullFloat32 

func (r Row) NullFloat32(col string) (float32, bool)

NullFloat32 returns value as a float32 and NULL indicator. When value is NULL, second parameter is true. NullFloat32 method uses strconv.ParseFloat to convert string into float32. (see https://golang.org/pkg/strconv/#ParseFloat)

func (Row) NullFloat64 

func (r Row) NullFloat64(col string) (float64, bool)

NullFloat64 returns value as a float64 and NULL indicator. When value is NULL, second parameter is true. NullFloat64 method uses strconv.ParseFloat to convert string into float64. (see https://golang.org/pkg/strconv/#ParseFloat)

func (Row) NullInt 

func (r Row) NullInt(col string) (int, bool)

NullInt returns value as an int and NULL indicator. When value is NULL, second parameter is true. NullInt method uses strconv.Atoi to convert string into int. (see https://golang.org/pkg/strconv/#Atoi)

func (Row) NullInt16 

func (r Row) NullInt16(col string) (int16, bool)

NullInt16 returns value as an int8 and NULL indicator. When value is NULL, second parameter is true. NullInt16 method uses strconv.ParseInt to convert string into int16. (see https://golang.org/pkg/strconv/#ParseInt)

func (Row) NullInt32 

func (r Row) NullInt32(col string) (int32, bool)

NullInt32 returns value as an int32 and NULL indicator. When value is NULL, second parameter is true. NullInt32 method uses strconv.ParseInt to convert string into int32. (see https://golang.org/pkg/strconv/#ParseInt)

func (Row) NullInt64 

func (r Row) NullInt64(col string) (int64, bool)

NullInt64 returns value as an int64 and NULL indicator. When value is NULL, second parameter is true. NullInt64 method uses strconv.ParseInt to convert string into int64. (see https://golang.org/pkg/strconv/#ParseInt)

func (Row) NullInt8 

func (r Row) NullInt8(col string) (int8, bool)

NullInt8 returns value as an int8 and NULL indicator. When value is NULL, second parameter is true. NullInt8 method uses strconv.ParseInt to convert string into int8. (see https://golang.org/pkg/strconv/#ParseInt)

func (Row) NullString 

func (r Row) NullString(col string) (string, bool)

NullString returns string as a value and NULL indicator. When value is NULL, second parameter is true.

func (Row) String 

func (r Row) String(col string) string

String returns value as a string. NULL value is represented as an empty string.

type Rows 

type Rows struct {
	// contains filtered or unexported fields
}

Rows represents result set of SELECT query

func (*Rows) Bool 

func (r *Rows) Bool() bool

Bool returns value as a bool. NULL value is represented as false. Bool method uses strconv.ParseBool to convert string into bool. (see https://golang.org/pkg/strconv/#ParseBool)

func (*Rows) Bytes 

func (r *Rows) Bytes() []byte

Bytes returns value as slice of bytes. NULL value is represented as empty slice.

func (*Rows) Float32 

func (r *Rows) Float32() float32

Float32 returns value as a float32. NULL value is represented as 0.0. Float32 method uses strconv.ParseFloat to convert string into float32. (see https://golang.org/pkg/strconv/#ParseFloat)

func (*Rows) Float64 

func (r *Rows) Float64() float64

Float64 returns value as a float64. NULL value is represented as 0.0. Float64 method uses strconv.ParseFloat to convert string into float64. (see https://golang.org/pkg/strconv/#ParseFloat)

func (*Rows) Int 

func (r *Rows) Int() int

Int returns value as an int. NULL value is represented as 0. Int method uses strconv.Atoi to convert string into int. (see https://golang.org/pkg/strconv/#Atoi)

func (*Rows) Int16 

func (r *Rows) Int16() int16

Int16 returns value as an int16. NULL value is represented as 0. Int16 method uses strconv.ParseInt to convert string into int16. (see https://golang.org/pkg/strconv/#ParseInt)

func (*Rows) Int32 

func (r *Rows) Int32() int32

Int32 returns value as an int32. NULL value is represented as 0. Int32 method uses strconv.ParseInt to convert string into int32. (see https://golang.org/pkg/strconv/#ParseInt)

func (*Rows) Int64 

func (r *Rows) Int64() int64

Int64 returns value as an int64. NULL value is represented as 0. Int64 method uses strconv.ParseInt to convert string into int64. (see https://golang.org/pkg/strconv/#ParseInt)

func (*Rows) Int8 

func (r *Rows) Int8() int8

Int8 returns value as an int8. NULL value is represented as 0. Int8 method uses strconv.ParseInt to convert string into int8. (see https://golang.org/pkg/strconv/#ParseInt)

func (*Rows) LastError 

func (r *Rows) LastError() error

LastError returns the error if any occurred during reading result set of SELECT query. This method should be always called after reading all rows. 

rows, err := conn.Query("SELECT * FROM dogs")
if err != nil {
	// handle error
}
for rows.Next() {
	// read values
}
if err = rows.LastError(); err != nil {
	// handle error
}

func (*Rows) Next 

func (r *Rows) Next() bool

Next moves cursor to the next unread row. It returns false when there are no more rows left or an error occurred during reading rows (see LastError() function) This function must be called before reading first row and continue being called until it returns false. 

rows, _ := conn.Query("SELECT * FROM people LIMIT 2")
rows.Next() // move cursor to the first row
// read values from the first row
rows.Next() // move cursor to the second row
// read values from the second row
rows.Next() // drain the stream

Best practice is to call Next() function in a loop: 

rows, _ := conn.Query("SELECT * FROM people")
for rows.Next() {
	// read values from the row
}

It's required to read all rows before performing another query because connection contains sequential stream of rows. 

rows, _ := conn.Query("SELECT name FROM dogs LIMIT 1")
rows.Next()   // move cursor to the first row
rows.String() // dog's name
rows, _ = conn.Query("SELECT name FROM cats LIMIT 2")
rows.Next()   // move cursor to the second row of first query
rows.String() // still dog's name
rows.Next()   // returns false. closes the first stream of rows
rows.Next()   // move cursor to the first row of second query
rows.String() // cat's name
rows.Next()   // returns false. closes the second stream of rows

func (*Rows) NullBool 

func (r *Rows) NullBool() (bool, bool)

NullBool returns value as a bool and NULL indicator. When value is NULL, second parameter is true. NullBool method uses strconv.ParseBool to convert string into bool. (see https://golang.org/pkg/strconv/#ParseBool)

func (*Rows) NullBytes 

func (r *Rows) NullBytes() ([]byte, bool)

NullBytes returns value as a slice of bytes and NULL indicator. When value is NULL, second parameter is true. All other type-specific functions are based on this one. NullBytes shouldn't be invoked after all columns are read. Calling it after reading all values of the row will return nil value with NULL flag

func (*Rows) NullFloat32 

func (r *Rows) NullFloat32() (float32, bool)

NullFloat32 returns value as a float32 and NULL indicator. When value is NULL, second parameter is true. NullFloat32 method uses strconv.ParseFloat to convert string into float32. (see https://golang.org/pkg/strconv/#ParseFloat)

func (*Rows) NullFloat64 

func (r *Rows) NullFloat64() (float64, bool)

NullFloat64 returns value as a float64 and NULL indicator. When value is NULL, second parameter is true. NullFloat64 method uses strconv.ParseFloat to convert string into float64. (see https://golang.org/pkg/strconv/#ParseFloat)

func (*Rows) NullInt 

func (r *Rows) NullInt() (int, bool)

NullInt returns value as an int and NULL indicator. When value is NULL, second parameter is true. NullInt method uses strconv.Atoi to convert string into int. (see https://golang.org/pkg/strconv/#Atoi)

func (*Rows) NullInt16 

func (r *Rows) NullInt16() (int16, bool)

NullInt16 returns value as an int8 and NULL indicator. When value is NULL, second parameter is true. NullInt16 method uses strconv.ParseInt to convert string into int16. (see https://golang.org/pkg/strconv/#ParseInt)

func (*Rows) NullInt32 

func (r *Rows) NullInt32() (int32, bool)

NullInt32 returns value as an int32 and NULL indicator. When value is NULL, second parameter is true. NullInt32 method uses strconv.ParseInt to convert string into int32. (see https://golang.org/pkg/strconv/#ParseInt)

func (*Rows) NullInt64 

func (r *Rows) NullInt64() (int64, bool)

NullInt64 returns value as an int64 and NULL indicator. When value is NULL, second parameter is true. NullInt64 method uses strconv.ParseInt to convert string into int64. (see https://golang.org/pkg/strconv/#ParseInt)

func (*Rows) NullInt8 

func (r *Rows) NullInt8() (int8, bool)

NullInt8 returns value as an int8 and NULL indicator. When value is NULL, second parameter is true. NullInt8 method uses strconv.ParseInt to convert string into int8. (see https://golang.org/pkg/strconv/#ParseInt)

func (*Rows) NullString 

func (r *Rows) NullString() (string, bool)

NullString returns string as a value and NULL indicator. When value is NULL, second parameter is true.

func (*Rows) Row 

func (r *Rows) Row() Row

Row reads the entire row. This function is identical to read each column successively. 

 rows, _ := conn.Query("SELECT id, name FROM people")
 for rows.Next() {
		row := rows.Row() // reads both columns
		// the same as reading each column separately
 	// rows.Bytes()
 	// rows.Bytes()
 }

It is possible to call Row() function after reading some of the columns. In this case, Row() will read the rest of the columns of the row. 

	rows, _ := conn.Query("SELECT id, name, age FROM people")
 for rows.Next() {
		rows.Int() // read ID
		row := rows.Row() // reads name, age and return the full row
		fmt.Println(row.Int("id"), row.String("name"), row.Int("age"))
 }

func (*Rows) String 

func (r *Rows) String() string

String returns value as a string. NULL value is represented as an empty string.

type Stats 

type Stats struct {
	Syscalls int // number of system calls performed to read all packets
}

Contains connection statistics

func (Stats) Add 

func (s Stats) Add(stats Stats) Stats

Add sum ups all stats

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.