0.3.x series [current]
0.2.x series [deprecated]
0.1.x series [obsolete]
The most complete and stable MySQL client library written completely in Go. The aim of this project is to provide a library with a high level of usability, good internal error handling and to emulate similar libraries available for other languages to provide an easy migration of MySQL based systems into the Go language.
For discussions, ideas, suggestions, comments, please visit the Google Group: https://groups.google.com/group/gomysql
Please report bugs via the GitHub issue tracker: https://github.com/Philio/GoMySQL/issues
GoMySQL 0.3 is governed by a BSD-style license that can be found in the LICENSE file.
GoMySQL 0.1.x and 0.2.x is licensed under a Creative Commons Attribution-Share Alike 2.0 UK: England & Wales License.
Implements the MySQL protocol version 4.0- and 4.1+
Tested on versions of MySQL 4.x, 5.x (including 5.5), MariaDB and Percona.
As of version 0.3, the thread safe functionality was removed from the library, but the inherited functions from sync.Mutex were retained. The reasons for this is that the inclusions of locking/unlocking within the client itself conflicted with the new functionality that had been added and it was clear that locking should be performed within the calling program and not the library. For convenience to the programmer, the mutex functions were retained allowing for Client.Lock() and Client.Unlock() to be used for thread safe operations.
In older versions of the client from 0.1.8 - 0.2.x internal locking remains, however it is not recommended to use these versions as version 0.3.x is a much better implementation.
There are 2 methods to install GoMySQL
goinstall github.com/Philio/GoMySQL
The library will be installed in the same path locally so the import must be the same:
import "github.com/Philio/GoMySQL"
Clone the git repository:
git clone git://github.com/Philio/GoMySQL.git
Build / install:
cd GoMySQL
make
make install
This installs the package as 'mysql' so can be imported as so:
import "mysql"
Version 0.3 is a complete rewrite of the library and a vast improvement on the 0.2 branch, because of this there are considerable changes to available methods and usage. Please see the migration guide below for more information.
Client.VERSION - The current library version.
Client.DEFAULT_PORT - The default port for MySQL (3306).
Client.DEFAULT_SOCKET - The default socket for MySQL, valid for Debian/Ubuntu systems.
Client.MAX_PACKET_SIZE - The maximum size of packets that will be used.
Client.PROTOCOL_41 - Used to indicate that the 4.1+ protocol should be used to connect to the server.
Client.PROTOCOL_40 - Used to indicate that the 4.0- protocol should be used to connect to the server.
Client.DEFAULT_PROTOCOL - An alias for Client.PROTOCOL_41
Client.TCP - Used to indicate that a TCP connection should be used.
Client.UNIX - Used to indicate that a unix socket connection should be used (this is faster when connecting to localhost).
Client.LOG_SCREEN - Send log messages to stdout.
Client.LOG_FILE - Send log messages to a provided file pointer.
Client.LogLevel - The level of logging to provide to stdout, valid values are 0 (none), 1 (essential information), 2 (extended information), 3 (all information).
Client.LogType - The type of logging to use, should be one of mysql.LOG_SCREEN or mysql.LOG_FILE, the default is mysql.LOG_SCREEN.
Client.LogFile - A pointer for logging to file, can be used to log to any source that implements os.File.
Client.AffectedRows - The number of affected rows for the last operation (if applicable).
Client.LastInsertId - The insert id of the last operation (if applicable).
Client.Warnings - The number of warnings generated by the last operation (if applicable).
Client.Reconnect - Set to true to enable automatic reconnect for dropped connections.
*mysql.NewClient(protocol ...uint8) (c Client)** - Get a new client instance, optionally specifying the protocol.
*mysql.DialTCP(raddr, user, passwd string, dbname ...string) (c Client, err os.Error)** - Connect to the server using TCP.
*mysql.DialUnix(raddr, user, passwd string, dbname ...string) (c Client, err os.Error)** - Connect to the server using unix socket.
Client.Connect(network, raddr, user, passwd string, dbname ...string) (err os.Error) - Connect to the server using the provided details.
Client.Close() (err os.Error) - Close the connection to the server.
Client.ChangeDb(dbname string) (err os.Error) - Change database.
Client.Query(sql string) (err os.Error) - Perform an SQL query.
*Client.StoreResult() (result Result, err os.Error)** - Store the complete result set and return a pointer to the result.
*Client.UseResult() (result Result, err os.Error)** - Use the result set but do not store the result, data is read from the server one row at a time via Result.Fetch functions (see below).
Client.FreeResult() (err os.Error) - Traditionally this function would free the memory used by the result set, in GoMySQL this removes the reference to allow the GC to clean up the memory. All results must be freed before more queries can be performed at present. FreeResult also reads and discards any remaining row packets received for the result set.
Client.MoreResults() bool - Check if more results are available.
Client.NextResult() (more bool, err os.Error) - Get the next result set from the server.
Client.SetAutoCommit(state bool) (err os.Error) - Set the auto commit state of the connection.
Client.Start() (err os.Error) - Start a new transaction.
Client.Commit() (err os.Error) - Commit the current transaction.
Client.Rollback() (err os.Error) - Rollback the current transaction.
Client.Escape(s string) (esc string) - Escape a string.
*Client.InitStmt() (stmt Statement, err os.Error)** - Initialise a new statement.
*Client.Prepare(sql string) (stmt Statement, err os.Error)** - Initialise and prepare a new statement using the supplied query.
Result.FieldCount() uint64 - Get the number of fields in the result set.
*Result.FetchField() Field** - Get the next field in the result set.
*Result.FetchFields() []Field** - Get all fields in the result set.
Result.RowCount() uint64 - Get the number of rows in the result set, works for stored results only, used result always return 0.
Result.FetchRow() Row - Get the next row in the result set.
Result.FetchMap() Map - Get the next row in the result set and convert to a map with field names as keys.
Result.FetchRows() []Row - Get all rows in the result set, works for stored results only, used results always return nil.
Statement.AffectedRows - The number of affected rows for the last statement operation (if applicable).
Statement.LastInsertId - The insert id of the last statement operation (if applicable).
Statement.Warnings - The number of warnings generated by the last statement operation (if applicable).
Statement.Prepare(sql string) (err os.Error) - Prepare a new statement using the supplied query.
Statement.ParamCount() uint16 - Get the number of parameters.
Statement.BindParams(params ...interface{}) (err os.Error) - Bind parameters to the statement.
Statement.SendLongData(num int, data []byte) (err os.Error) - Send a parameter as long data. The data can be > than the maximum packet size and will be split automatically.
Statement.Execute() (err os.Error) - Execute the statement.
Statement.FieldCount() uint64 - Get the number of fields in the statement result set.
*Statement.FetchColumn() Field** - Get the next field in the statement result set.
*Statement.FetchColumns() []Field** - Get all fields in the statement result set.
Statement.BindResult(params ...interface{}) (err os.Error) - Bind the result, parameters passed to this functions should be pointers to variables which will be populated with the data from the fetched row. If a column value is not needed a nil can be used. Parameters should be of a "similar" type to the actual column value in the MySQL table, e.g. for an INT field, the parameter can be any integer type or a string and the relevant conversion is performed. Using integer sizes smaller than the size in the table is not recommended. The number of parameters bound can be equal or less than the number of fields in the table, providing more parameters than actual columns will result in a crash.
Statement.RowCount() uint64 - Get the number of rows in the result set, works for stored results only, otherwise returns 0.
Statement.Fetch() (eof bool, err os.Error) - Fetch the next row in the result, values are populated into parameters bound using BindResult.
Statement.StoreResult() (err os.Error) - Store all rows for a result set,
Statement.FreeResult() (err os.Error) - Remove the result pointer, allowing the memory used for the result to be garbage collected.
Statement.MoreResults() bool - Check if more results are available.
Statement.NextResult() (more bool, err os.Error) - Get the next result set from the server.
Statement.Reset() (err os.Error) - Reset the statement.
Statement.Close() (err os.Error) - Close the statement.
A simple query
// Connect to database
db, err := mysql.DialUnix(mysql.DEFAULT_SOCKET, "user", "password", "database")
if err != nil {
os.Exit(1)
}
// Perform query
err = db.Query("select * from my_table")
if err != nil {
os.Exit(1)
}
// Get result set
result, err := db.UseResult()
if err != nil {
os.Exit(1)
}
// Get each row from the result and perform some processing
for {
row := result.FetchRow()
if row == nil {
break
}
// ADD SOME ROW PROCESSING HERE
} Prepared statement
// Define a struct to hold row data
type MyRow struct {
Id uint64
Name string
Description string
}
// Connect to database
db, err := mysql.DialUnix(mysql.DEFAULT_SOCKET, "user", "password", "database")
if err != nil {
os.Exit(1)
}
// Prepare statement
stmt, err := db.Prepare("select * from my_table where name = ?")
if err != nil {
os.Exit(1)
}
// Bind params
err = stmt.BindParams("param")
if err != nil {
os.Exit(1)
}
// Execute statement
err = stmt.Execute()
if err != nil {
os.Exit(1)
}
// Define a new row to hold result
var myrow MyRow
// Bind result
stmt.BindResult(&myrow.Id, &myrow.Name, &myrow.Description)
// Get each row from the result and perform some processing
for {
eof, err := stmt.Fetch()
if err != nil {
os.Exit(1)
}
if eof {
break
}
// ADD SOME ROW PROCESSING HERE
} As of version 0.3.0 the library can detect network failures and try and reconnect automatically. Any methods that use the network connection support reconnect but may still return a network error (as the process is too complicated to recover) while a number of core methods are able to attempt to reconnect and recover the operation. The default setting for the feature is OFF.
Methods supporting recovery:
This section is less relevant to the 0.3 client as it has full binary support and excellent type support but has been retained for reference.
When using prepared statements the data packets sent to/from the server are in binary format (normal queries send results as text).
Prior to version 0.2.7 there were a number of unsupported data types in the library which limited the use of prepared statement selects to the most common field types.
As of version 0.2.7 all currently supported MySQL data types are fully supported, as well as a wide range of support of Go types for binding parameters. There are some minor limitations in the usage of unsigned numeric types, as Go does not natively support unsigned floating point numbers unsigned floats and doubles are limited to the maximum value of a signed float or double.
Supported parameter types:
Integer types: int, uint, int8, uint8, int16, uint16, int32, uint32, int64, uint64
Float types: float, float32, float64
Strings/other tyes: string
Go row data formats:
| MySQL data type | Native Go type |
|---|---|
| TINYINT | int8 |
| TINYINT (unsigned) | uint8 |
| SMALLINT | int16 |
| SMALLINT (unsigned) | uint16 |
| MEDIUMINT | int32 |
| MEDIUMINT (unsigned) | uint32 |
| INT | int32 |
| INT (unsigned) | uint32 |
| BIGINT | int64 |
| BIGINT (unsigned) | uint64 |
| TIMESTAMP | string |
| DATE | string |
| TIME | string |
| DATETIME | string |
| YEAR | string |
| VARCHAR | string |
| TINYTEXT | string |
| MEDIUMTEXT | string |
| LONGTEXT | string |
| TEXT | string |
| TINYBLOB | string |
| MEDIUMBLOB | string |
| LONGBLOB | string |
| BLOB | string |
| BIT | []byte |
| GEOMETRY | []byte |
As of version 0.3.0 all functions return a ClientError or ServerError struct which contains a MySQL error code and description. The original Errno and Error public properties are deprecated.
As of version 0.2.0 all functions return os.Error. If the command succeeded the return value will be nil, otherwise it will contain the error.
If returned value is not nil then MySQL error code and description can then be retrieved from Errno and Error properties for additional info/debugging.
Prepared statements have their own copy of the Errno and Error properties.
Generated errors attempt to follow MySQL protocol/specifications as closely as possible.
The original 'New()' method has gone and in it's place are a number of Go-style constructors: NewClient, DialTCP and DialUnix. This offers greater flexibility (such as the ability to connect to localhost via TCP) and simplified usage. If features such as logging are required or the 4.0 protocol, the NewClient constructor can be used to set options before connect, then Connect can be called as with previous versions.
As the Query method no longer returns a result set an extra step is needed here, either UseResult or StoreResult. UseResult allows you to read rows 1 at a time from the buffer which can reduce memory requirements for large result sets considerably. It is currently also a requirement to call FreeResult once you have finished with a result set, this may change with a later release. The MultiQuery method has been removed as the Query method can now support multiple queries, when using this feature you should check MoreResults and use NextResult to move to the next result set. You must free the previous result before calling NextResult, although again this may change later as we feel it would be more intuitive to free the result automatically.
The main changes for statements are that you must now bind the result parameters before fetching a row, this means type conversion is automated and there is no longer a need for type assertions on the rows. The bound result parameters can be individual vars or struct properties or really anything that can be passed as a pointer.