README.rdoc

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, unless Encoding.default_internal is set then it'll
convert from UTF-8 to that encoding] 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-config=/some/random/path/bin/mysql_config

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")

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

Cascading config

The default config hash is at:

Mysql2::Client.default_query_options

which defaults to:

{:async => false, :as => :hash, :symbolize_keys => false}

that can be used as so:

# these are the defaults all Mysql2::Client instances inherit
Mysql2::Client.default_query_options.merge!(:as => :array)

or

# this will change the defaults for all future results returned by the #query method _for this connection only_
c = Mysql2::Client.new
c.query_options.merge!(:symbolize_keys => true)

or

# this will set the options for the Mysql2::Result instance returned from the #query method
c = Mysql2::Client.new
c.query(sql, :symbolize_keys => true)

Result types

Array of Arrays

Pass the :as => :array option to any of the above methods of
configuration

Array of Hashes

The default result type is set to :hash, but you can override a previous
setting to something else with :as => :hash

Others…

I may add support for :as => :csv or even :as => :json to allow for
much more efficient generation of those data types from result sets.
If you'd like to see either of these (or others), open an issue and
start bugging me about it ;)

Timezones

Mysql2 now supports two timezone options:

:database_timezone - this is the timezone Mysql2 will assume fields are already stored as, and will use this when creating the initial Time objects in ruby
:application_timezone - this is the timezone Mysql2 will convert to before finally handing back to the caller

In other words, if :database_timezone is set to :utc - Mysql2 will create
the Time objects using Time.utc(…) from the raw value libmysql hands over
initially. Then, if :application_timezone is set to say - :local - Mysql2
will then convert the just-created UTC Time object to local time.

Both options only allow two values - :local or :utc - with the exception
that :application_timezone can be [and defaults to] nil

Casting “boolean” columns

You can now tell Mysql2 to cast tinyint(1) fields to boolean values in Ruby
with the :cast_booleans option.

Async

Mysql2::Client takes advantage of the MySQL C API's (undocumented)
non-blocking function mysql_send_query for all queries. But, in
order to take full advantage of it in your Ruby code, you can do:

client.query("SELECT sleep(5)", :async => true)

Which will return nil immediately. At this point you'll probably want
to use some socket monitoring mechanism like EventMachine or even
IO.select. Once the socket becomes readable, you can do:

# result will be a Mysql2::Result instance
result = client.async_result

NOTE: Because of the way MySQL's query API works, this method will
block until the result is ready. So if you really need things to stay
async, it's best to just monitor the socket with something like
EventMachine. If you need multiple query concurrency take a look at using a
connection pool.

Row Caching

By default, Mysql2 will cache rows that have been created in Ruby (since
this happens lazily). This is especially helpful since it saves the cost of
creating the row in Ruby if you were to iterate over the collection again.

If you only plan on using each row once, then it's much more efficient
to disable this behavior by setting the :cache_rows option to false. This
would be helpful if you wanted to iterate over the results in a streaming
manner. Meaning the GC would cleanup rows you don't need anymore as
you're iterating over the result set.

ActiveRecord

To use the ActiveRecord driver, all you should need to do is have this gem
installed and set the adapter in your database.yml to “mysql2”. That was
easy right? :)

Lazy Everything

Well… almost ;)

Field name strings/symbols are shared across all the rows so only one
object is ever created to represent the field name for an entire dataset.

Rows themselves are lazily created in ruby-land when an attempt to yield it
is made via #each. For example, if you were to yield 4 rows from a 100 row
dataset, only 4 hashes will be created. The rest will sit and wait in
C-land until you want them (or when the GC goes to cleanup your
Mysql2::Result instance). Now say you were to iterate over that same
collection again, this time yielding 15 rows - the 4 previous rows that had
already been turned into ruby hashes would be pulled from an internal
cache, then 11 more would be created and stored in that cache. Once the
entire dataset has been converted into ruby objects, Mysql2::Result will
free the Mysql C result object as it's no longer needed.

This caching behavior can be disabled by setting the :cache_rows option to
false.

As for field values themselves, I'm workin on it - but expect that
soon.

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 - broken at the moment, working with the rbx team for a solution

The ActiveRecord driver should work on 2.3.5 and 3.0

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.