Ruby使用Mysql2連線操作MySQL

2022-04-18 22:02:25

Ruby操作MySQL

使用mysql2連線mysql並操作mysql。相關檔案參見：

gem install mysql2

連線mysql

建立連線：

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  #=>主要用於設定本次連線時的某些變數
)

連線建立後就可以運算元據庫了，比如執行SQL語句：

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

如果測試和mysql的連線是否斷開，可執行ping()：

conn.ping

如果連線未斷開，ping()返回true，如果連線已斷開但已啟用auto-reconnect，則ping()會嘗試依次reconnect，連線成功則返回true，否則報錯。如果連線已斷開，且未啟用auto-reconnect，則報錯。

query()查詢和結果處理

query()用於執行任何允許的SQL語句，比如執行查詢語句。

查詢結果可使用each進行迭代，迭代時傳遞查詢到的每一行記錄，可使用hash索引的方式(預設以hash型別儲存每一行)查詢某個欄位的內容：

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

可見，查詢結果中，每一行資料預設以hash格式儲存。

實際上，對於增刪改的SQL語句，query()的返回值為nil，對於查詢類的語句，其返回值以Mysql2::Result物件返回

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}>

query()各查詢選項的含義以及預設的查詢選項參見下文。先了解兩個：

:as項表示以陣列方式(:as=>:array)還是hash方式(:as=>:hash)儲存查詢結果
:symbolize_keys表示返回hash結果時，其key是否設定為符號型別，預設為false，即key是字串型別

可在查詢時指定這些引數，也可在each迭代時指定這些引數，例如：

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}

雖然大多數時候使用hash儲存每一行資料更方便，但有時候也會想要以陣列方式操作每一行資料，例如連線每一個欄位的值：

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"

Mysql2::Result自身具備的方法不多：

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

外，Mysql2::Result已經mix-in了Enumerable模組，所以可直接使用該模組中的方法，例如first表示取第一行記錄。

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

需注意，所有SQL語句中涉及到的值都是未經跳脫的，有時候需要也建議在執行它們之前先對它們進行跳脫。

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

查詢選項含義以及預設查詢選項

query()預設的查詢選項可以通過Mysql2::Client.default_query_options獲取，它是一個hash結果：

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

其中(重要)：

:as項表示以陣列方式(:as=>:array)還是hash方式(:as=>:hash)儲存查詢結果
:symbolize_keys表示返回hash結果時，其key是否設定為符號型別，預設為false，即key是字串型別
:async表示查詢是否非同步模式，即是否非阻塞的查詢，參考https://github.com/brianmario/mysql2#async
:cast指示MySQL的查詢結果轉換為Ruby資料時是否進行型別轉換，如果確定本次查詢的欄位型別和Ruby的型別完全對應，可禁用casting功能提升效率
:database_timezone指示Ruby接收MySQL返回的日期時間資料時的時區，mysql2將先以該時區建立日期時間物件來儲存對應欄位的值。僅支援:local和:utc兩個值
:application_timezone指示最終Mysql2::Result中的日期時間的時區，即程式端的時區。因此，mysql2先以”無失真”的時區從MySQL獲取日期時間資料，並根據application_timezone將其轉換成程式端時區的日期時間物件
:cache_rows指示是否快取構建出來的hash行或array行
Mysql2處理查詢結果的流程：
- Mysql2的MySQL C api從MySQL伺服器端查詢資料，並儲存在Ruby的查詢結果集(結果集屬於C)
- Mysql2::Result和C端結果集是關聯的，當釋放Mysql2::Result，也會對C結果集進行GC
- Mysql2在需要取得結果集中的資料時(比如each迭代)，才從結果集中根據查詢選項構建所需行並返回，比如構建hash結構的行，構建陣列結構的行，構建hash結構時將key轉換為Symbol型別等
- 預設情況下，從結果集中查詢並構建出來的hash行或array行會快取在Ruby中，使得下次再次請求這一行時(比如再次迭代)，可用直接從快取中取得hash行或array行
- 比如從MySQL伺服器端查詢了100行資料儲存在C的結果集中，第一次以hash方式請求其中4行，這4行hash資料會快取起來，如果下次再從頭開始以hash方式請求15行，則前4行來自於快取，後11行來自於結果集的臨時構建
- 如果:cache_rows未禁用，當結果集中的所有行都被快取，Mysql2::Result將會去釋放C端的結果集
- 如果能確保查詢的結果集只使用一次，可禁用:cache_rows，這會提升效率
:stream: true表示以Stream的方式處理查詢結果。有時候查詢結果資料量非常大，Ruby端不方便存放所有結果，可採用stream的方式去處理本次查詢：一邊從MySQL伺服器端取資料放進結果集，一邊從結果集中取資料進行處理(比如迭代)。使用stream時，會自動關閉cache_rows，因為它們是互相沖突的概念。此外，使用stream模式要求必須迭代完所有資料集才會執行下一條查詢，因為一個mysql連線在某一時刻只能執行一個操作，在迭代完之前，本次查詢操作還尚未完成。

修改Mysql2::Client.default_query_options可以設定預設query()的查詢選項。如果想要設定其中某選項，可以通過hash合併的方式來設定該選項。

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

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

prepare()+execute()

除了直接使用query()執行SQL語句查詢資料庫，也可以使用prepare()方法將字串準備成一個待執行的SQL語句，其中可以使用?充當預留位置。

prepare後的語句是一個Mysql2::Statement物件，該物件有一個execute()方法，可以用來執行這個準備好的語句，它可指定查詢選項，且其返回值同query()一樣：對於增刪改操作，返回值為nil，對於查詢類操作，返回Mysql2::Result結果物件。

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

處理多結果集

有些儲存過程中可能包含多個查詢結果集，或者有時候會在一行SQL中包含多個select語句而同時返回多個結果集，Mysql2能很好地處理多結果集問題。

要處理多結果集，連線mysql時必須指定一個flag：

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')

雖然本次query()涉及了多個select語句，Mysql2也已經儲存了這三個select的查詢結果集(儲存在結果集佇列中)，但本次query()方法的返回值僅是第一個結果集，所以res中儲存的是第一個結果集的內容。

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

要獲取剩餘的結果集，可通過conn.next_result將結果集偏移指標移到下一個結果集，然後通過conn.store_result獲取下一個結果集，依次類推，直到沒有剩餘結果集後，conn.next_result返回false。可通過more_results?()方法判斷是否還有剩餘的結果集。

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}

需注意，開啟多行語句(即多結果集)功能後，所查詢得到的所有結果集必須已經處理完成(嚴格來說，是存放結果集的佇列已經為空)，才能繼續執行後續的SQL語句(事實上，經測試，結果集佇列未空的情況下執行其它SQL語句會導致直接斷開mysql連線)。可使用abandon_results!()方法強行丟棄所有剩餘結果集，使得Mysql2馬上回歸正常狀態：可向MySQL伺服器端傳送SQL語句。

# 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'

Mysql2的EventMachine

Mysql2支援EM，可以執行非同步的query()，此外，可以指定當query()查詢成功或失敗時的回撥語句塊：

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

ORM之：Sequel

Active:Record應該是最為人熟知的orm，其功能極其豐富。

另一個輕量級的ORM是Sequel，它支援ADO, JDBC, MySQL, Mysql2, ODBC, Oracle, PostgreSQL, SQLite3等等。

Sequel官方手冊：

例如：

require 'sequel'

# 建立資料庫範例
DB = Sequel.connect(
  adapter: :mysql2,
  user: 'root',
  password: 'P@ssword1!',
  host: '192.168.200.73',
  port: 3306,
  database: 'mytest'
)

# 建立資料集，資料集表示的是一張表或表部分資料
# 此時不會去查詢資料，會推遲到需要資料時才查詢
dataset = DB[:tb]

# 迭代表資料
dataset.each do |row|
  pp row
end

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