72
Help us understand the problem. What are the problem?

posted at

updated at

RubyでMySQLを操作する

はじめに

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

参考

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
72
Help us understand the problem. What are the problem?