gem install mysql2

require 'mysql2'

conn = Mysql2::Client.new({ 
  host: '192.168.200.73',
  username: 'root',
  password: 'P@ssword1!'
})

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,
  :connect_attrs = {:program_name => $PROGRAM_NAME, ...},
  :reconnect = true/false,
  :local_infile = true/false,
  :secure_auth = true/false,
  :ssl_mode = :disabled / :preferred / :required / :verify_ca / :verify_identity,
  :default_file = '$HOME/.my.cnf',   #=> 從文件讀取連接信息
  :default_group = 'my.cfg section', #=> 選擇.my.cnf中的section
  :default_auth = 'authentication_windows_client',
  :init_command => SQL_Statement  #=>主要用于設置本次連接時的某些變量
)

conn. query('create databse mytest')
conn.select_ db( 'mytest' )
# conn.query('use mytest' )
conn. query('create table tb(name varchar(1024),age int)')
conn. query <<-SQL
    insert into tb values 
    ('junmajinlong', 23), 
    ( 'woniu', 25),
    ('fairy', 26)
SQL

conn.ping

conn.query("show databases").each do |row| pp row end
=begin
{"Database"=>"information_schema"}
{"Database"=>"mysql"}
{"Database"=>"mytest"}
{"Database"=>"performance_schema"}
{"Database"=>"sys"}
=end

conn.query("select * from mytest.tb").each do |row|
  pp row
  pp row["name"]
end
=begin
{"name"=>"junmajinlong", "age"=>23}
"junmajinlong"
{"name"=>"woniu", "age"=>25}
"woniu"
{"name"=>"fairy", "age"=>26}
"fairy"
=end

conn.query("create table mytest.t1(id int)")
#=> nil
res = conn.query("select * from mytest.tb")
p res

#<Mysql2::Result:0x00007fffe833a230
 @query_options=
  {:as=>:hash,
   :async=>false,
   :cast_booleans=>false,
   :symbolize_keys=>false,
   :database_timezone=>:local,
   :application_timezone=>nil,
   :cache_rows=>true,
   :connect_flags=>2148540933,
   :cast=>true,
   :default_file=>nil,
   :default_group=>nil,
   :host=>"192.168.200.73",
   :username=>"root",
   :password=>"P@ssword1!"},
 @server_flags=
   {:no_good_index_used=>false, 
     :no_index_used=>true, 
     :query_was_slow=>false}>

conn.query("select * from mytest.tb", symbolize_keys: true).each do |row|
  pp row
end

conn.query("select * from mytest.tb").each(symbolize_keys: true) do |row|
  pp row
end

{:name=>"junmajinlong", :age=>23}
{:name=>"woniu", :age=>25}
{:name=>"fairy", :age=>26}

sql = 'select * from mytest.tb'
res = conn.query(sql)
res.each(as: :array) do |row|
  p row.join(",")
end

"junmajinlong,23"
"woniu,25"
"fairy,26"

#count ? Object (also: #size)
#each(*args) ? Object
#fields ? Object
#free ? Object

sql = 'select * from mytest.tb'
res = conn.query(sql)
res.first
#=>{"name"=>"junmajinlong", "age"=>23}

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

Mysql2::Client.default_query_options
=begin
{:as=>:hash,
 :async=>false,
 :cast_booleans=>false,
 :symbolize_keys=>false,
 :database_timezone=>:local,
 :application_timezone=>nil,
 :cache_rows=>true,
 :connect_flags=>2148540933,
 :cast=>true,
 :default_file=>nil,
 :default_group=>nil}
=end

Mysql2::Client.default_query_options
#=> {:as=>:hash, ...}

Mysql2::Client.default_query_options.merge!(:as => :array)
#=> {:as=>:array, ...}

res_sql = conn.prepare('select * from mytest.tb where age >= ? and name like ?')
res = res_sql.execute(20, '%junma%', as: :array)
res.first

conn = Mysql2::Client.new({
  host: "192.168.200.73",
  username: "root",
  password: "P@ssword1!",
  flags: Mysql2::Client::MULTI_STATEMENTS
})

res = conn.query('select 1;select 2;select 3')

res.first   #=> {"1"=>1}

conn.next_result
res = conn.store_result
res.first #=> {"2"=>2}

conn.next_result
res = conn.store_result
res.first #=> {"3"=>3}

conn.next_result  #=> false

res = conn.query('select 1;select 2;select 3')
loop do
  p res.first
  break unless conn.next_result
  res = conn.store_result
end

# 或者
p res.first
while conn.next_result
  res = conn.store_result
  p res.first
end

{"1"=>1}
{"2"=>2}
{"3"=>3}

# res和res1都只保存第一個結果集
# 但結果集隊列中保留的是select 5和select 6的結果集
res = conn.query('select 1;select 2;select 3')
conn.abandon_results!  # 丟棄所有剩余結果集
res1 = conn.query('select 4;select 5;select 6')

res = conn.query('select 1;select 2;select A;select 3')
loop do
  p res.first
  break unless conn.next_result
  res = conn.store_result
end

{"1"=>1}
{"2"=>2}
Mysql2::Error: Unknown column 'A' in 'field list'

require 'mysql2/em'

EM.run do
  client1 = Mysql2::EM::Client.new
  defer1 = client1.query "SELECT sleep(3) as first_query"
  defer1.callback do |result|
    puts "Result: #{result.to_a.inspect}"
  end

  client2 = Mysql2::EM::Client.new
  defer2 = client2.query "SELECT sleep(1) second_query"
  defer2.callback do |result|
    puts "Result: #{result.to_a.inspect}"
  end
end

require 'sequel'

# 創(chuàng)建數(shù)據(jù)庫實例
DB = Sequel.connect(
  adapter: :mysql2,
  user: 'root',
  password: 'P@ssword1!',
  host: '192.168.200.73',
  port: 3306,
  database: 'mytest'
)

# 創(chuàng)建數(shù)據(jù)集，數(shù)據(jù)集表示的是一張表或表部分數(shù)據(jù)
# 此時不會去查詢數(shù)據(jù)，會推遲到需要數(shù)據(jù)時才查詢
dataset = DB[:tb]

# 迭代表數(shù)據(jù)
dataset.each do |row|
  pp row
end

# 條件查詢
pp dataset.where(name: 'junmajinlong', age: 23).first
pp dataset.where { name =~ "junmajinlong" and age =~ 23 }.first