Ruby Mysql


In the previous chapter we introduced the use of Ruby DBI. In this chapter, our technical Ruby connection to Mysql uses the more efficient driver mysql2. It is currently recommended to use this method to connect to MySql.

Install mysql2 driver: 

gem install mysql2

You need to use –with-mysql-config to configure the path of mysql_config, such as: –with-mysql-config=/some/random/path/bin/mysql_config .

Connection

The syntax for connecting to the database is as follows: 

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

Query

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

Special character escape

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

Calculate the number returned by the result set :

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

Iteration result set: 

#!/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

Example

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

The above example running output result is: 

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
  )

Connection options

rrreee

For more information, see: http://www.rubydoc.info/gems/mysql2/0.2.3/frames.