はじめに
rubyで、Mysql2を使用してMySQLを操作する方法を記述します。
環境
- CentOS 6.5
- Ruby 2.1.2p95
- Mysql2
MySQLのインストール
$ wget http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
$ sudo yum localinstall mysql-community-release-el6-5.noarch.rpm
$ sudo yum install mysql-community-client.x86_64 mysql-community-devel.x86_64 mysql-community-server.x86_64
$ sudo /etc/init.d/mysqld start
$ /usr/bin/mysql_secure_installation
Mysql2のインストール
- bundleのインストール
$ gem install bundle
- Mysql2 を追記
Gemfile
gem "mysql2"
- Mysql2 のインストール
$ bundle
$ gem list mysql2
*** LOCAL GEMS ***
mysql2 (0.3.16)
MySQLを操作してみる
- テーブルの準備
mysql> create table test.languages (id int, name varchar(32));
Query OK, 0 rows affected (1.21 sec)
mysql> desc test.languages;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.11 sec)
mysql> insert into test.languages values (1, 'Ruby');
Query OK, 1 row affected (0.09 sec)
mysql> insert into test.languages values (2, 'Python');
Query OK, 1 row affected (0.06 sec)
mysql> insert into test.languages values (3, 'Java');
Query OK, 1 row affected (0.03 sec)
- テーブルの参照
mysql2_sample.rb
require 'mysql2'
client = Mysql2::Client.new(:host => 'localhost', :user => 'root', :password => 'password')
query = %q{select user, host from test.languages}
results = client.query(query)
results.each do |row|
puts "--------------------"
row.each do |key, value|
puts "#{key} => #{value}"
end
end
$ ruby mysql2_sample.rb
------------------------------
id => 1
name => Ruby
------------------------------
id => 2
name => Python
------------------------------
id => 3
name => Java
- データの挿入
mysql2_sample02.rb
require 'mysql2'
client = Mysql2::Client.new(:host => 'localhost', :user => 'root', :password => 'password')
query = %q{insert into test.languages values (4, 'PHP')}
results = client.query(query)
query = %q{select user, host from test.languages}
results = client.query(query)
results.each do |row|
puts "--------------------"
row.each do |key, value|
puts "#{key} => #{value}"
end
end
$ ruby mysql2_sample02.rb
------------------------------
id => 1
name => Ruby
------------------------------
id => 2
name => Python
------------------------------
id => 3
name => Java
------------------------------
id => 4
name => PHP
- データの更新
mysql2_sample03.rb
require 'mysql2'
client = Mysql2::Client.new(:host => 'localhost', :user => 'root', :password => 'password')
query = %q{update test.languages set name = 'Perl' where id = 4}
results = client.query(query)
query = %q{select user, host from test.languages}
results = client.query(query)
results.each do |row|
puts "--------------------"
row.each do |key, value|
puts "#{key} => #{value}"
end
end
$ruby mysql2_sample03.rb
------------------------------
id => 1
name => Ruby
------------------------------
id => 2
name => Python
------------------------------
id => 3
name => Java
------------------------------
id => 4
name => Perl
- データの削除
mysql2_sample04.rb
require 'mysql2'
client = Mysql2::Client.new(:host => 'localhost', :user => 'root', :password => 'password')
query = %q{delete from test.languages where id = 4}
results = client.query(query)
query = %q{select user, host from test.languages}
results = client.query(query)
results.each do |row|
puts "--------------------"
row.each do |key, value|
puts "#{key} => #{value}"
end
end
$ruby mysql2_sample04.rb
------------------------------
id => 1
name => Ruby
------------------------------
id => 2
name => Python
------------------------------
id => 3
name => Java
------------------------------