Ruby Connect Mysql - MySql2

Ruby Connect Mysql - MySql2

In the previous section, we covered the use of Ruby DBI. This section of our technology Ruby Connect Mysql more efficient drive mysql2 is also currently recommended to connect MySql in this way.

Install the mysql2 driver:

gem install mysql2

You need to configure the path of the mysql_config using -with-mysql-config, e.g. -with-mysql-config=/some/random/path/bin/mysql_config.

Connection

The connection database syntax is as follows:

# 更多参数可以查看 http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/MysqlAdapter.html
client = Mysql2::Client.new(:host => "localhost", :username => "root")

Inquire

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

Special character escape

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

Calculate the number returned by the result set:

results.count

Iterative result set:

results.each do |row|
  # row 是哈希
  # 键值是数据库字段
  # 值都是对应 MySQL中数据
  puts row["id"] # row["id"].class == Fixnum
  if row["dne"]  # 不存在则是 nil
    puts row["dne"]
  end
end

Instance

#!/usr/bin/ruby -w
require 'mysql2'

client = Mysql2::Client.new(
	:host     => '127.0.0.1', # 主机
	:username => 'root',      # 用户名
	:password => '123456',    # 密码
	:database => 'test',      # 数据库
	:encoding => 'utf8'       # 编码
	)
results = client.query("SELECT VERSION()")
results.each do |row|
  puts row
end

The above instance runs the output as:

{"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, see: http://www.rubydoc.info/gems/mysql2/0.2.3/frames.