Shulou(Shulou.com)05/31 Report--

This article mainly explains "Ruby how to use Mysql2 connection to operate MySQL", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn "Ruby how to use Mysql2 connection to operate MySQL"!

Ruby operation MySQL

Use mysql2 to connect to mysql and operate mysql.

Gem install mysql2 connection mysql

Establish a connection:

Require 'mysql2'conn = Mysql2::Client.new ({host:' 192.168.200.73percent, username: 'root', password:' purssword1words'})

Accepted connection options include:

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', # = > read connection information from file: default_group = 'my.cfg section', # = > Select section: default_auth =' authentication_windows_client' in .my.cnf : init_command = > SQL_Statement # = > is mainly used to set some variables in this connection)

After the connection is established, you can manipulate the database, such as executing the 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 "mysql"} {"Database" = > "mytest"} {"Database" = > "performance_schema"} {"Database" = > "sys"} = endconn.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

It can be seen that in the query results, each row of data is saved in hash format by default.

In fact, for the addition, deletion and modification of the SQL statement, the return value of query () is nil, and for the statement of query class, the return value is returned as a Mysql2::Result object

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

Results:

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

For the meaning of the query options for query () and the default query options, see below. Let's start with two things:

: as entry indicates whether to store query results in an array (: as= >: array) or hash (: as= >: hash)

: symbolize_keys indicates whether key is set to symbol type when hash result is returned. The default is false, that is, key is a string type.

You can specify these parameters at query time or during each iterations, for example:

Conn.query ("select * from mytest.tb", symbolize_keys: true) .each do | row | pp rowendconn.query ("select * from mytest.tb") .each (symbolize_keys: true) do | row | pp rowend

Results:

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

Although it is more convenient to use hash to save each row of data most of the time, sometimes you want to manipulate each row of data in an array, such as concatenating the values of each field:

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

Results:

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

There are not many methods that Mysql2::Result itself has:

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

Besides, Mysql2::Result has already mix-in the Enumerable module, so you can directly use the methods in this module, such as first to represent the first row of records.

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

Note that the values involved in all SQL statements are unescaped, and it is sometimes recommended that they be escaped before they are executed.

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

The default query option for query () can be obtained through Mysql2::Client.default_query_options, which is a hash result:

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

Among them (important):

: as entry indicates whether to store query results in an array (: as= >: array) or hash (: as= >: hash)

: symbolize_keys indicates whether key is set to symbol type when hash result is returned. The default is false, that is, key is a string type.

: async indicates whether the query is in asynchronous mode, that is, whether the query is non-blocking. Refer to https://github.com/brianmario/mysql2#async.

Cast indicates whether type conversion is performed when the query result of MySQL is converted to Ruby data. If it is determined that the field type of this query corresponds exactly to the type of Ruby, you can disable casting to improve efficiency.

Database_timezone indicates the time zone when Ruby receives the date-time data returned by MySQL, and mysql2 will first create a date-time object to save the value of the corresponding field. Only: local and: utc are supported

Application_timezone indicates the time zone of the date and time in the final Mysql2::Result, that is, the time zone of the program side Therefore, mysql2 first acquires date-time data from MySQL with a "lossless" time zone and converts it into a date-time object of the program-side time zone according to application_timezone

Cache_rows indicates whether to cache hash or array rows that are built

Mysql2's flow of processing query results:

Mysql2's MySQL C api queries data from the MySQL server and saves it in Ruby's query result set (the result set belongs to C)

Mysql2::Result is associated with the C-side result set. When Mysql2::Result is released, the C result set will also be GC.

When Mysql2 needs to get the data in the result set (such as each iteration), it builds the required rows from the result set according to the query options and returns them, such as building rows of hash structure, building rows of array structure, converting key to Symbol type when building hash structure, etc.

By default, hash or array rows that are queried and built from the result set are cached in Ruby so that the next time the row is requested (such as iterating again), the hash or array rows can be fetched directly from the cache

For example, if you query 100 rows of data from the MySQL server and save them in the result set of C, and request 4 rows of hash data in hash for the first time, these 4 rows of hash data will be cached. Next time, if 15 rows are requested in hash from scratch, the first 4 rows will come from the cache, and the last 11 rows will come from the temporary construction of the result set.

If: cache_rows is not disabled, when all rows in the result set are cached, Mysql2::Result will release the C-side result set.

If you can ensure that the result set of the query is used only once, disable: cache_rows, which will improve efficiency

: stream: true means to process the query results in a Stream manner. Sometimes the query result has a very large amount of data, and it is not convenient for the Ruby side to store all the results. You can use the stream method to deal with this query: while fetching data from the MySQL server into the result set, while fetching data from the result set for processing (such as iteration). When using stream, cache_rows is automatically turned off because they are conflicting concepts. In addition, using the stream schema requires that all datasets must be iterated before the next query is executed, because a mysql connection can only perform one operation at a time, and this query operation has not been completed until the iteration is complete.

Modify Mysql2::Client.default_query_options to set the query options for the default query (). If you want to set one of these options, you can set it through hash merge.

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

In addition to directly using query () to execute the SQL statement to query the database, you can also use the prepare () method to prepare the string into a pending SQL statement, where you can use? Act as a placeholder.

The statement after prepare is a Mysql2::Statement object, which has an execute () method that can be used to execute the prepared statement, which can specify query options, and its return value is the same as query (): for add, delete and modify operations, the return value is nil, for query operations, the Mysql2::Result result object is returned.

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

Some stored procedures may contain multiple query result sets, or sometimes contain multiple select statements in one SQL and return multiple result sets at the same time. Mysql2 can handle the problem of multiple result sets well.

To handle multiple result sets, you must specify a flag when connecting to the mysql:

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

Then execute multiple query statements for multiple result sets:

Res = conn.query ('select 1 × select 2 select 3')

Although this time query () involves multiple select statements, and Mysql2 has saved the query result set of the three select (saved in the result set queue), the return value of this query () method is only the first result set, so what is saved in res is the content of the first result set.

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

To get the remaining result set, move the result set offset pointer to the next result set through conn.next_result, then get the next result set through conn.store_result, and so on, until conn.next_result returns false when there are no remaining result sets. You can determine whether there are any remaining result sets by the more_results () method.

Conn.next_resultres = conn.store_resultres.first # = > {"2" = > 2} conn.next_resultres = conn.store_resultres.first # = > {"3" = > 3} conn.next_result # = > false

Therefore, multiple result sets can be traversed:

Res = conn.query ('select 1 * select 2 * select 3') loop do p res.first break unless conn.next_result res = conn.store_resultend# or p res.firstwhile conn.next_result res = conn.store_result p res.firstend

Output result:

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

It should be noted that after turning on the multi-row statement (that is, multiple result sets) function, all the result sets obtained by the query must have been processed (strictly speaking, the queue where the result sets are stored has been empty) before you can continue to execute subsequent SQL statements (in fact, after testing, executing other SQL statements when the result set queue is not empty will directly disconnect the mysql connection). You can use the abandon_results! () method to forcibly discard all remaining result sets so that Mysql2 returns to normal immediately: a SQL statement can be sent to the MySQL server.

Both # res and res1 save only the first result set # but what remains in the result set queue are the result sets of select 5 and select 6 res = conn.query ('select 1 and select 2) conn.abandon_results! # discard all remaining result sets res1 = conn.query (' select 4 select 5 and select 6')

In addition, if an error is reported by a query statement in the middle of multiple query statements, it will affect that the subsequent statements will not be executed, so the subsequent result set cannot be obtained.

Res = conn.query ('select 1ten select 2select Aten select 3') loop do p res.first break unless conn.next_result res = conn.store_resultend

Results:

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

Mysql2 supports EM and can execute asynchronous query (). In addition, you can specify the callback statement block when the query () query succeeds or fails:

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}" endendORM: Sequel

Active:Record should be the most well-known orm, with extremely rich features.

Another lightweight ORM is Sequel, which supports ADO, JDBC, MySQL, Mysql2, ODBC, Oracle, PostgreSQL, SQLite3 and so on.

For example:

Require 'sequel'# to create a database instance DB = Sequel.connect (adapter:: mysql2, user:' root', password: 'purssword1 databases, host:' 192.168.200.73 databases, port: 3306, database: 'mytest') # create a dataset, which represents a table or part of the table data # do not query the data at this time It will be postponed to query dataset = DB [: tb] # iterative representative data dataset.each do | row | pp rowend# conditional query pp dataset.where (name: 'junmajinlong', age: 23). Firstpp dataset.where {name = ~ "junmajinlong" and age = ~ 23}. First, I believe you have a better understanding of "how Ruby uses Mysql2 connection operation MySQL". You might as well do it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.