Skip to main content

Ruby connects to Mysql - MySql2

Ruby connects to Mysql - MySql2​

In the previous chapter we introduced the use of Ruby DBI. In this chapter, we use Ruby to connect to Mysql to drive mysql2 more efficiently. It is currently recommended to use this method to connect to MySql.

Install mysql2 driver:

 
    gem install [mysql](/search?q=mysql)2

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 .

connect​

The syntax for connecting to the database is as follows:

 
    client = Mysql2::Client.new(:host => "localhost", :username => "root")

Inquire​

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

Special character escaping​

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

Calculate the quantity returned by the result set:

 
    results.count

Iterate over the result set:​

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

Example​

       1. #!/usr/bin/[[ruby](/search?q=ruby)](/search?q=ruby) -w
    
      2. require 'mysql2'
    
      3.  
    
      4. client = Mysql2::Client.new(
    
      5.     :host     => '127.0.0.1', # 主机
    
      6.     :username => 'root',      # 用户名
    
      7.     :password => '123456',    # 密码
    
      8.     :database => 'test',      # 数据库
    
      9.     :encoding => 'utf8'       # 编码
    
      10.     )
    
      11. results = client.query("SELECT VERSION()")
    
      12. results.each do |row|
    
      13.   puts row
    
      14. end

The output result of the above example is:

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

Connection options​

       1. Mysql2::Client.new(
    
      2.   :host,
    
      3.   :username,
    
      4.   :password,
    
      5.   :port,
    
      6.   :database,
    
      7.   :socket = '/path/to/mysql.sock',
    
      8.   :flags = REMEMBER_OPTIONS | LONG_PASSWORD | LONG_FLAG | TRANSACTIONS | PROTOCOL_41 | SECURE_CONNECTION | MULTI_STATEMENTS,
    
      9.   :encoding = 'utf8',
    
      10.   :read_timeout = seconds,
    
      11.   :write_timeout = seconds,
    
      12.   :connect_timeout = seconds,
    
      13.   :reconnect = true/false,
    
      14.   :local_infile = true/false,
    
      15.   :secure_auth = true/false,
    
      16.   :default_file = '/path/to/my.cfg',
    
      17.   :default_group = 'my.cfg section',
    
      18.   :init_command => sql
    
      19.   )