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

[![Build Status](https://travis-ci.org/brianmario/mysql2.png)](https://travis-ci.org/brianmario/mysql2)

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 classes:

`Mysql2::Client` - your connection to the database

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

## Installing
### OSX / Linux
``` sh
gem install mysql2
```

This gem links against MySQL's `libmysqlclient` C shared library. You may need to install a package such as `libmysqlclient-dev`, `mysql-devel`, or other appropriate package for your system.

By default, the mysql2 gem will try to find a copy of MySQL in this order:

* Option `--with-mysql-dir`, if provided (see below).
* Option `--with-mysql-config`, if provided (see below).
* Several typical paths for `msyql_config` (default for the majority of users).
* The directory `/usr/local`.

### Configuration options

Use these options by `gem install mysql2 -- [--optionA] [--optionB=argument]`.

* `--with-mysql-dir[=/path/to/mysqldir]` -
Specify the directory where MySQL is installed. The mysql2 gem will not use
`mysql_config`, but will instead look at `mysqldir/lib` and `mysqldir/include`
for the library and header files.
This option is mutually exclusive with `--with-mysql-config`.

* `--with-mysql-config[=/path/to/mysql_config]` -
Specify a path to the `mysql_config` binary provided by your copy of MySQL. The
mysql2 gem will ask this `mysql_config` binary about the compiler and linker
arguments needed.
This option is mutually exclusive with `--with-mysql-dir`.

* `--with-mysql-rpath=/path/to/mysql/lib` / `--without-mysql-rpath` -
Override the runtime path used to find the MySQL libraries.
This may be needed if you deploy to a system where these libraries
are located somewhere different than on your build system.
This overrides any rpath calculated by default or by the options above.

### Windows
First, make sure you have the DevKit installed (http://rubyinstaller.org/downloads/) and its variables
are loaded by running devkit\devktvars.bat .

Next, you need a MySQL library to link against. If you have MySQL loaded on your development machine,
you can use that. If not, you will need to either copy the MySQL directory from your server, or else
obtain a copy of the MySQL C connector: http://dev.mysql.com/downloads/connector/c/

If you're using the connector, I recommend just getting the .zip file and unzipping it someplace convenient.

Now you can install mysql2. You must use the `--with-mysql-dir` option to tell gem where your MySQL library
files are. For example, if you unzipped the connector to c:\mysql-connector-c-6.1.1-win32 you would install
the gem like this:

    gem install mysql2 -- --with-mysql-dir=c:\mysql-connector-c-6.1.1-win32

Finally, you must copy libmysql.dll from the lib subdirectory of your MySQL or MySQL connector directory into
your ruby\bin directory. In the above example, libmysql.dll would be located at
c:\mysql-connector-c-6.1.1-win32\lib .

## Usage

Connect to a database:

``` ruby
# 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:

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

Need to escape something first?

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

You can get a count of your results with `results.count`.

Finally, iterate over the results:

``` ruby
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:

``` ruby
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?

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

You can get the headers and the columns in the order that they were returned
by the query like this:

``` ruby
headers = results.fields # <= that's an array of field names, in order
results.each(:as => :array) do |row|
# Each row is an array, ordered the same as the query results
# An otter's den is called a "holt" or "couch"
end
```

## Connection options

You may set the following connection options in Mysql2::Client.new(...):

``` ruby
Mysql2::Client.new(
  :host,
  :username,
  :password,
  :port,
  :database,
  :socket = '/path/to/mysql.sock',
  :flags = REMEMBER_OPTIONS | LONG_PASSWORD | LONG_FLAG | TRANSACTIONS | PROTOCOL_41 | SECURE_CONNECTION | MULTI_STATEMENTS,
  :encoding = 'utf8',
  :read_timeout = seconds,
  :write_timeout = seconds,
  :connect_timeout = seconds,
  :reconnect = true/false,
  :local_infile = true/false,
  :secure_auth = true/false,
  :default_file = '/path/to/my.cfg',
  :default_group = 'my.cfg section'
  )
```

### SSL options

Setting any of the following options will enable an SSL connection, but only if
your MySQL client library and server have been compiled with SSL support.
MySQL client library defaults will be used for any parameters that are left out
or set to nil. Relative paths are allowed, and may be required by managed
hosting providers such as Heroku.<commit_msg>Update README with information about the init_command option<commit_after># Mysql2 - A modern, simple and very fast Mysql library for Ruby - binding to libmysql

[![Build Status](https://travis-ci.org/brianmario/mysql2.png)](https://travis-ci.org/brianmario/mysql2)

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 classes:

`Mysql2::Client` - your connection to the database

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

## Installing
### OSX / Linux
``` sh
gem install mysql2
```

This gem links against MySQL's `libmysqlclient` C shared library. You may need to install a package such as `libmysqlclient-dev`, `mysql-devel`, or other appropriate package for your system.

By default, the mysql2 gem will try to find a copy of MySQL in this order:

* Option `--with-mysql-dir`, if provided (see below).
* Option `--with-mysql-config`, if provided (see below).
* Several typical paths for `msyql_config` (default for the majority of users).
* The directory `/usr/local`.

### Configuration options

Use these options by `gem install mysql2 -- [--optionA] [--optionB=argument]`.

* `--with-mysql-dir[=/path/to/mysqldir]` -
Specify the directory where MySQL is installed. The mysql2 gem will not use
`mysql_config`, but will instead look at `mysqldir/lib` and `mysqldir/include`
for the library and header files.
This option is mutually exclusive with `--with-mysql-config`.

* `--with-mysql-config[=/path/to/mysql_config]` -
Specify a path to the `mysql_config` binary provided by your copy of MySQL. The
mysql2 gem will ask this `mysql_config` binary about the compiler and linker
arguments needed.
This option is mutually exclusive with `--with-mysql-dir`.

* `--with-mysql-rpath=/path/to/mysql/lib` / `--without-mysql-rpath` -
Override the runtime path used to find the MySQL libraries.
This may be needed if you deploy to a system where these libraries
are located somewhere different than on your build system.
This overrides any rpath calculated by default or by the options above.

### Windows
First, make sure you have the DevKit installed (http://rubyinstaller.org/downloads/) and its variables
are loaded by running devkit\devktvars.bat .

Next, you need a MySQL library to link against. If you have MySQL loaded on your development machine,
you can use that. If not, you will need to either copy the MySQL directory from your server, or else
obtain a copy of the MySQL C connector: http://dev.mysql.com/downloads/connector/c/

If you're using the connector, I recommend just getting the .zip file and unzipping it someplace convenient.

Now you can install mysql2. You must use the `--with-mysql-dir` option to tell gem where your MySQL library
files are. For example, if you unzipped the connector to c:\mysql-connector-c-6.1.1-win32 you would install
the gem like this:

    gem install mysql2 -- --with-mysql-dir=c:\mysql-connector-c-6.1.1-win32

Finally, you must copy libmysql.dll from the lib subdirectory of your MySQL or MySQL connector directory into
your ruby\bin directory. In the above example, libmysql.dll would be located at
c:\mysql-connector-c-6.1.1-win32\lib .

## Usage

Connect to a database:

``` ruby
# 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:

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

Need to escape something first?

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

You can get a count of your results with `results.count`.

Finally, iterate over the results:

``` ruby
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:

``` ruby
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?

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

You can get the headers and the columns in the order that they were returned
by the query like this:

``` ruby
headers = results.fields # <= that's an array of field names, in order
results.each(:as => :array) do |row|
# Each row is an array, ordered the same as the query results
# An otter's den is called a "holt" or "couch"
end
```

## Connection options

You may set the following connection options in Mysql2::Client.new(...):

``` ruby
Mysql2::Client.new(
  :host,
  :username,
  :password,
  :port,
  :database,
  :socket = '/path/to/mysql.sock',
  :flags = REMEMBER_OPTIONS | LONG_PASSWORD | LONG_FLAG | TRANSACTIONS | PROTOCOL_41 | SECURE_CONNECTION | MULTI_STATEMENTS,
  :encoding = 'utf8',
  :read_timeout = seconds,
  :write_timeout = seconds,
  :connect_timeout = seconds,
  :reconnect = true/false,
  :local_infile = true/false,
  :secure_auth = true/false,
  :default_file = '/path/to/my.cfg',
  :default_group = 'my.cfg section',
  :init_command => sql
  )
```

### init_command

If you specify the init_command option, the SQL string you provide will be executed after the connection is established.

If `:reconnect` is set to `true`, init_command will also be executed after a successful reconnect.

It is useful if you want to provide session options which survive reconnection.

``` ruby
Mysql2::Client.new(:init_command => "SET @@SESSION.sql_mode = 'STRICT_ALL_TABLES'")
```

### SSL options

Setting any of the following options will enable an SSL connection, but only if
your MySQL client library and server have been compiled with SSL support.
MySQL client library defaults will be used for any parameters that are left out
or set to nil. Relative paths are allowed, and may be required by managed
hosting providers such as Heroku.
{'fraction_non_alphanumeric': 0.09755907960199005, 'fraction_numerical': 0.009406094527363185, 'mean_word_length': 4.227549776513612, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 13, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '435814', 'n_tokens_mistral': 3954, 'n_tokens_neox': 3727, 'n_words': 1649}