6.35. Ruby connection Mysql-MySql2

We introduced the use of Ruby DBI in the previous chapter. In this chapter, we use Ruby to connect Mysql to more efficient drivers. mysql2 is also recommended to connect to MySql in this way.

Installation mysql2 drive:

geminstallmysql2

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

6.35.1. Connect #

The syntax for connecting to the database is as follows:

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

See http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/MysqlAdapter.html for more parameters.

6.35.2. Query #

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

6.35.3. 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 

6.35.4. Iterative result set: #

Results. eachdo | row | # row is hash # key value is database field # value is corresponding Data putrow ["id"] # row ["id"]. class in MySQL== Fixnumifrow ["dne"] # If it does not exist, it is nilputsrow ["dne"] end 

Example #

#!/usr/bin/ruby -wrequire'mysql2'client=Mysql2::Client.new(:host=>'127.0.0.1', #main engine:username=>'root',#user name:password=>'123456', #password:database=>'test', #database:encoding=>'utf8'#coding)results=client.query("SELECT VERSION()")results.eachdo\|row\|putsrowend 

The output of the above instance is as follows:

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

6.35.5. Connection option #

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.cfgsection', :init_command => sql ) 

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