Ruby Connect to Mysql - MySql2

In the previous chapter, we introduced the use of Ruby DBI. In this chapter, we introduce the more efficient driver mysql2 for connecting Ruby to MySQL. This method is also currently recommended for connecting to MySQL.

Installing the mysql2 Driver:

gem install mysql2

You need to configure the path of mysql_config using --with-mysql-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")

You can view more parameters athttp://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/MysqlAdapter.html。

Query

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

Escaping Special Characters

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

Calculate the number of rows returned by the result set:

results.count

Iterating the Result Set:

results.each do |row| #row is a hash #The keys are database fields #The values correspond to the data in MySQL puts row["id"] # row["id"].class == Fixnum if row["dne"] #If it does not exist, it is nil puts row["dne"] end end

Example

#!/usr/bin/ruby -w require 'mysql2' 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 output of the above example is:

{"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 = '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 )

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