Mysql2 - A modern, simple and very fast Mysql library for Ruby - binding to libmysql

The Mysql2 gem is meant to serve the extremely common use-case of connecting, querying and iterating on results. Some database libraries out there serve as direct 1:1 mappings of the already complex C API’s available. This one is not.

It also forces the use of UTF-8 [or binary] for the connection [and all strings in 1.9] and uses encoding-aware MySQL API calls where it can.

The API consists of two clases:

Mysql2::Client - your connection to the database

Mysql2::Result - returned from issuing a #query on the connection. It includes Enumerable.

Installing

gem install mysql2

You may have to specify –with-mysql-lib=/usr/local/lib/mysql or your path to libmysql (I’ll do my best to not require that)

Usage

Connect to a database:

# this takes a hash of options, almost all of which map directly
# to the familiar database.yml in rails
# See http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/MysqlAdapter.html
client = Mysql2::Client.new(:host => "localhost", :username => "root")

Then query it:

results = client.query("SELECT * FROM users WHERE group='githubbers'")

Need to escape something first?

escaped = client.escape("gi'thu\"bbe\0r's")
results = client.query("SELECT * FROM users WHERE group='#{escaped}'")

Finally, iterate over the results:

results.each do |row|
  # conveniently, row is a hash
  # the keys are the fields, as you'd expect
  # the values are pre-built ruby primitives mapped from their corresponding field types in MySQL
  # Here's an otter: http://farm1.static.flickr.com/130/398077070_b8795d0ef3_b.jpg
end

Or, you might just keep it simple:

client.query("SELECT * FROM users WHERE group='githubbers'").each do |row|
  # do something with row, it's ready to rock
end

How about with symbolized keys?

# NOTE: the :symbolize_keys and future options will likely move to the #query method soon
client.query("SELECT * FROM users WHERE group='githubbers'").each(:symbolize_keys => true) do |row|
  # do something with row, it's ready to rock
end

Compatibility

The specs pass on my system (SL 10.6.3, x86_64) in these rubies:

• 1.8.7-p249

• ree-1.8.7-2010.01

• 1.9.1-p378

• ruby-trunk

• rbx-head

Yeah… but why?

Someone: Dude, the Mysql gem works fiiiiiine.

Me: It sure does, but it only hands you nil and strings for field values. Leaving you to convert them into proper Ruby types in Ruby-land - which is slow as balls.

Someone: OK fine, but do_mysql can already give me back values with Ruby objects mapped to MySQL types.

Me: Yep, but it’s API is considerably more complex and is 2-3x slower.

Benchmarks

Performing a basic “SELECT * FROM” query on a table with 30k rows and fields of nearly every Ruby-representable data type, then iterating over every row using an #each like method yielding a block:

# And remember, the Mysql gem only gives back nil and strings.
 user       system     total     real
Mysql2
 2.080000   0.790000   2.870000 (  3.418861)
Mysql
 1.210000   0.790000   2.000000 (  4.527824)
do_mysql
 5.450000   0.980000   6.430000 (  7.001563)