Ruby Connect Mysql Mysql2

In the previous chapter, we introduced the usage of Ruby DBI. In this chapter, we will discuss a more efficient driver, mysql, for Ruby to connect to Mysql.2, and it is also recommended to use this method to connect to MySql at present.

Install mysql2 Driver:

gem install mysql2

You need to use '–with'-mysql-Configure the path of mysql_config with 'config', for example: –with-mysql-config=/some/random/path/bin/mysql_config.

Connect

The syntax for connecting to the database is as follows:

client　=　Mysql2::Client.new(:host => "localhost", :username => "root")

More parameters can be viewed http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/MysqlAdapter.html.

Query

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

Special character escaping

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

Calculate the number of returned result sets:

results.count

Iterate over the result set:

results.each do |row|
　　#　row is a hash
　　#　Keys are database fields
　　#　Values are corresponding to MySQL data
　　puts row["id"]　#　row["id"].class　==　Fixnum
　　if row["dne"]　#　Does not exist is nil
　　　　puts row["dne"]
　　end
end

Online Example

#!/usr/bin/ruby　-w
require 'mysql'2'
　
client　=　Mysql2::Client.new(
　　　　:host　=>　'127.0.0.1',　#　Host
　　　　:username　=>　'root',　#　Username
　　　　:password　=>　'123456',　#　Password
　　　　:database　=>　'test',　#　Database
　　　　:encoding　=>　'utf8'Encoding
　　　　)
results　=　client.query("SELECT　VERSION()")
results.each do |row|
　　puts row
end

The above example runs and outputs the following result:

{"VERSION()"=>"5.6.21"}

Connection Options

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 = 'utf'8',
　　: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
　　)

For more information, please refer to:http://www.rubydoc.info/gems/mysql2/0.2.3/frames.