RubyでMySQLを操作する


## はじめに

rubyで、Mysql2を使用してMySQLを操作する方法を記述します。

## 環境

* CentOS 6.5
* Ruby 2.1.2p95
* Mysql2

## MySQLのインストール

```shell
$ 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のインストール

```shell
$ gem install bundle
```

* _Mysql2_ を追記

```ruby:Gemfile
gem "mysql2"
```

* _Mysql2_ のインストール

```shell
$ bundle
$ gem list mysql2
*** LOCAL GEMS ***

mysql2 (0.3.16)
```

## MySQLを操作してみる

* テーブルの準備

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

* テーブルの参照

```ruby: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
```

```shell
$ ruby mysql2_sample.rb
------------------------------
id => 1
name => Ruby
------------------------------
id => 2
name => Python
------------------------------
id => 3
name => Java
```

* データの挿入

```ruby: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
```

```shell
$ ruby mysql2_sample02.rb
------------------------------
id => 1
name => Ruby
------------------------------
id => 2
name => Python
------------------------------
id => 3
name => Java
------------------------------
id => 4
name => PHP
```

* データの更新

```ruby: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
```

```shell
$ruby mysql2_sample03.rb
------------------------------
id => 1
name => Ruby
------------------------------
id => 2
name => Python
------------------------------
id => 3
name => Java
------------------------------
id => 4
name => Perl
```

* データの削除

```ruby: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
```

```shell
$ruby mysql2_sample04.rb
------------------------------
id => 1
name => Ruby
------------------------------
id => 2
name => Python
------------------------------
id => 3
name => Java
------------------------------
```

## 参考

* [Download MySQL Yum Repository](http://dev.mysql.com/downloads/repo/yum/)
* [mysql2](https://github.com/brianmario/mysql2)