35.md

# MySQL Ruby 教程

> 原文： [http://zetcode.com/db/mysqlrubytutorial/](http://zetcode.com/db/mysqlrubytutorial/)

这是针对 MySQL 数据库的 Ruby 编程教程。 它涵盖了使用 Ruby 进行 MySQL 编程的基础。 它使用`mysql`模块。 这些示例是在 Ubuntu Linux 上创建和测试的。

在 ZetCode 上有一个类似的 [MySQL C API 教程](/tutorials/mysqlcapitutorial/)， [MySQL Visual Basic 教程](/db/mysqlvb/)或 [MySQL Python 教程](/db/mysqlpython/)。

您也可以考虑查看 [MySQL 教程](/databases/mysqltutorial/)。

## MySQL & Ruby

_MySQL是领先的开源数据库管理系统。 它是一个多用户，多线程的数据库管理系统。 MySQL 在网络上特别流行。 它是由 Linux，Apache，MySQL 和 PHP 组成的非常流行的LAMP平台的一部分。 目前，MySQL 由 Oracle 拥有。 MySQL 数据库在最重要的 OS 平台上可用。Ruby_ 是一种动态的，反射性的，通用的面向对象的编程语言。 最近，它在 Web 编程中变得非常流行，这主要归功于 Ruby on Rails 框架的成功。

## mysql 模块

`mysql`模块是 MySQL 服务器的 Ruby 接口。 它为 Ruby 程序提供的功能与 MySQL C API 为 C 程序提供的功能相同。

```
$ sudo gem1.9 install mysql

```

在这里，我们为 MySQL 数据库安装 Ruby 模块。

## 开始之前

我们将创建一个新的数据库用户和一个新的数据库。 为此，我们使用`mysql`客户端程序。

```
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.67-0ubuntu6 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              | 
+--------------------+
2 rows in set (0.00 sec)

```

我们使用 root 帐户连接到 MySQL 服务器。 我们用`SHOW DATABASES`语句显示所有可用的数据库。

```
mysql> CREATE DATABASE mydb;
Query OK, 1 row affected (0.02 sec)

```

我们创建一个新的`mydb`数据库。 在整个教程中，我们将使用此数据库。

```
mysql> CREATE USER user12@localhost IDENTIFIED BY '34klq*';
Query OK, 0 rows affected (0.00 sec)

mysql> USE mydb;
Database changed

mysql> GRANT ALL ON mydb.* to user12@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;
Bye

```

我们创建一个新的数据库用户。 我们授予该用户`mydb`数据库所有表的所有特权。

## MySQL 服务器版本

在第一个示例中，我们将获取 MySQL 数据库的版本。

```
#!/usr/bin/ruby

require 'mysql'

begin
    con = Mysql.new 'localhost', 'user12', '34klq*'
    puts con.get_server_info
    rs = con.query 'SELECT VERSION()'
    puts rs.fetch_row    

rescue Mysql::Error => e
    puts e.errno
    puts e.error

ensure
    con.close if con
end

```

在此脚本中，我们获取服务器版本。 我们用两种不同的方式来做。

```
require 'mysql'

```

我们导入`mysql`模块。 该模块具有用于 MySQL 数据库的类和方法。

```
con = Mysql.new 'localhost', 'user12', '34klq*'

```

我们创建连接对象。 参数包括主机名，用户名和密码。 在我们的情况下，主机名是 localhost，例如 我们的电脑。

```
puts con.get_server_info

```

我们创建的 Mysql 对象具有`get_server_info`方法。 它返回安装的 MySQL 服务器的版本。

```
rs = con.query 'SELECT VERSION()'
puts rs.fetch_row    

```

获取版本的另一种方法是执行`SELECT VERSION()` SQL 语句。 我们获取数据。 由于只检索一条记录，因此我们称为`fetch_row`方法。

```
rescue Mysql::Error => e
    puts e.errno
    puts e.error

```

我们检查错误。 这很重要，因为使用数据库容易出错。

```
ensure
    con.close if con
end

```

最后，我们释放资源。

```
$ ./version.rb
5.5.9
5.5.9

```

输出可能类似于上面。

## 列出数据库

MySQL Ruby 模块具有`list_dbs`方法，该方法返回可用的数据库。

```
#!/usr/bin/ruby

require 'mysql'

begin
    con = Mysql.new 'localhost', 'user12', '34klq*'

    con.list_dbs.each do |db|
        puts db
    end

rescue Mysql::Error => e
    puts e.errno
    puts e.error

ensure
    con.close if con
end

```

在此脚本中，我们在 MySQL 服务器上打印所有可用的数据库。

```
con.list_dbs.each do |db|
    puts db
end

```

`list_dbs`方法返回可用数据库名称的数组。 使用数组的 each 方法，我们将数组的每个项目打印到控制台。

```
$ ./listdb.rb
information_schema
mydb
test
world

```

在我的系统上，我创建了上述数据库。

## 创建并填充表

我们创建一个表，并用一些数据填充它。

```
#!/usr/bin/ruby

require 'mysql'

begin

    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

    con.query("CREATE TABLE IF NOT EXISTS \
        Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
    con.query("INSERT INTO Writers(Name) VALUES('Jack London')")
    con.query("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    con.query("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    con.query("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    con.query("INSERT INTO Writers(Name) VALUES('Truman Capote')")   

rescue Mysql::Error => e
    puts e.errno
    puts e.error

ensure
    con.close if con
end

```

我们创建一个 Writers 表，并向其中添加五个作者。

```
con.query("CREATE TABLE IF NOT EXISTS \
        Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")

```

要执行 SQL 语句，我们使用`query`方法。 该 SQL 语句创建一个称为 Writers 的新数据库表。 它有两列。 ID 和名称。

```
con.query("INSERT INTO Writers(Name) VALUES('Jack London')")
con.query("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
...

```

我们使用`INSERT`语句在表中插入作者。 在这里，我们添加两行。

```
mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)

```

执行脚本后，我们使用`mysql`客户端工具从 Writers 表中选择所有数据。

## 检索数据

现在我们已经将一些数据插入数据库中，我们可以将其取回。

```
#!/usr/bin/ruby

require "mysql"

begin

    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

    rs = con.query("SELECT * FROM Writers")
    n_rows = rs.num_rows

    puts "There are #{n_rows} rows in the result set"

    n_rows.times do
        puts rs.fetch_row.join("\s")
    end

rescue Mysql::Error => e
    puts e.errno
    puts e.error

ensure
    con.close if con
end

```

在此示例中，我们从 Writers 表中检索所有数据。

```
con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

```

构造函数的最后一个参数是我们要连接的数据库名称。

```
rs = con.query("SELECT * FROM Writers")

```

该 SQL 语句从`Writers`表中选择所有数据。

```
n_rows = rs.num_rows

```

我们使用结果集对象的`num_rows`方法获得结果集中的行数。

```
n_rows.times do
    puts rs.fetch_row.join("\s")
end

```

在这里，我们使用`fetch_row`方法获取每一行。 它返回一行作为字段数组。 默认情况下，打印时这些字段用换行分隔。 使用`join`方法，我们将每一行打印在一行上。 字段由一个空格分隔。

```
$ ./retrieve1.rb
There are 5 rows in the result set
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

```

接下来，我们介绍另一种从表中检索数据的方法。

```
#!/usr/bin/ruby

require "mysql"

begin
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

    rs = con.query("SELECT * FROM Writers")

    rs.each do |row|
        puts row.join("\s")
    end

rescue Mysql::Error => e
    puts e.errno
    puts e.error

ensure
    con.close if con
end

```

我们从`Writers`表中打印所有数据。 这次我们使用结果集的每种方法遍历数据。

```
rs.each do |row|
    puts row.join("\s")
end

```

我们使用每种方法遍历结果集。

```
$ ./retrieve2.rb
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

```

这是示例的输出。

我们可以以 Ruby 哈希的形式遍历数据。

```
#!/usr/bin/ruby

require 'mysql'

begin

    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'    
    rs = con.query "SELECT * FROM Writers WHERE Id IN (1, 2, 3)"
    puts "We have #{rs.num_rows} row(s)"

    rs.each_hash do |row|
       puts row['Id'] + " " + row['Name']
    end      

rescue Mysql::Error => e
    puts e    
ensure
    con.close if con
end

```

在示例中，我们使用`each_hash`迭代器。 可以通过列名称检索结果集中的记录。

```
rs.each_hash do |row|
    puts row['Id'] + " " + row['Name']
end     

```

我们使用`each_hash`方法查看结果集。 返回的每一行都是一个 Ruby 哈希； 键值对的集合。 键是列名。

```
$ ./retrieve3.rb
We have 3 row(s)
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger

```

示例的输出。

## 多个语句

MySQL 支持多条语句执行。 必须通过特殊选项启用它。

```
#!/usr/bin/ruby

require 'mysql'

begin

    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
    con.set_server_option Mysql::OPTION_MULTI_STATEMENTS_ON

    rs = con.query "SELECT Name FROM Writers WHERE Id=1;
                    SELECT Name FROM Writers WHERE Id=2;
                    SELECT Name FROM Writers WHERE Id=3"

    puts rs.fetch_row        

    while con.next_result
        rs = con.store_result    
        puts rs.fetch_row
    end       

rescue Mysql::Error => e
    puts e.errno
    puts e.error

ensure
    con.close if con
end

```

在此示例中，我们在一个查询中具有三个`SELECT`语句。

```
con.set_server_option Mysql::OPTION_MULTI_STATEMENTS_ON

```

首先，我们需要使用`Mysql::OPTION_MULTI_STATEMENTS_ON`启用多条语句处理。

```
rs = con.query "SELECT Name FROM Writers WHERE Id=1;
                SELECT Name FROM Writers WHERE Id=2;
                SELECT Name FROM Writers WHERE Id=3"

```

在这里，我们定义了三个`SELECT`语句。 它们之间用分号分隔。

```
puts rs.fetch_row 

```

查询方法返回第一个结果集。 我们从该结果集中获取一行。

```
while con.next_result
    rs = con.store_result    
    puts rs.fetch_row
end   

```

我们将获得其他结果集，直到没有更多要处理的语句为止。

```
$ ./multiplest.rb
Jack London
Honore de Balzac
Lion Feuchtwanger

```

运行示例。

## 元数据

元数据是有关数据库中数据的信息。 MySQL 系统中的元数据包含有关存储数据的表和列的信息。 受 SQL 语句影响的行数是元数据。 结果集中返回的行数和列数也属于元数据。

```
#!/usr/bin/ruby

require 'mysql'

begin

    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'    
    rs = con.query "SELECT * FROM Writers WHERE Id IN (1, 2, 3)"
    puts "We have #{con.field_count} fields"
    puts "We have #{rs.num_rows} row(s)"
    puts rs.fetch_row.join("\s")

rescue Mysql::Error => e
    puts e    
ensure
    con.close if con
end

```

在此脚本中，我们从 SQL 查询中找出行数和列数。

```
rs = con.query "SELECT * FROM Writers WHERE Id IN (1, 2, 3)"

```

该 SQL 语句返回三行。 每行有两列。

```
puts "We have #{con.field_count} fields"
puts "We have #{rs.num_rows} row(s)"

```

这两行返回结果集中的列数和行数。 请注意，此处的字段是列的同义词。 返回的数据是元数据。

```
puts rs.fetch_row.join("\s")

```

在这里，我们从结果集中返回一行。 这是存储在我们数据库表中的原始数据。

对于`INSERT`，`DELETE`和`UPDATE`语句，有一种称为`rows_affected`的方法。 此方法返回受这三个语句影响的行数。

```
#!/usr/bin/ruby

require 'mysql'

begin

    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'    
    con.query "DELETE FROM Writers WHERE Id IN (1, 2, 3)"

    puts "The query has affected #{con.affected_rows} rows"

rescue Mysql::Error => e
    puts e    
ensure
    con.close if con
end

```

在我们的示例中，我们从`Writers`表中删除了前三行。

```
con.query "DELETE FROM Writers WHERE Id IN (1, 2, 3)"

```

一个 SQL 语句，该语句删除`Writers`表的前三行。

```
puts "The query has affected #{con.affected_rows} rows"

```

在这里，我们得到受上述 SQL 语句影响的行数。 该数字属于元数据。

```
$ ./affected.rb
The query has affected 3 rows

mysql> SELECT * FROM Writers;
+----+---------------+
| Id | Name          |
+----+---------------+
|  4 | Emile Zola    |
|  5 | Truman Capote |
+----+---------------+
2 rows in set (0.00 sec)

```

我们执行受影响的.rb 脚本，并检查`Writers`表中的更改。 三行已被删除。

在下一个示例中，我们将检查有关字段的元数据。

```
#!/usr/bin/ruby

require 'mysql'

begin
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
    rs = con.query "SELECT * FROM Writers WHERE Id=1"
    field = rs.fetch_field_direct 1

    puts "Table name: #{field.table}"
    puts "Field name: #{field.name}"
    puts "Field length: #{field.length}"
    puts "Field type: #{field.type}"

rescue Mysql::Error => e
    puts e

ensure
    con.close if con
end

```

我们从数据库中获得一条记录。 我们得到字段的表名，列名，长度和类型。

```
rs = con.query "SELECT * FROM Writers WHERE Id=1"

```

该查询返回一行。 它有两列。

```
field = rs.fetch_field_direct 1

```

使用`fetch_field_direct`方法，我们得到了一条特定的记录。 更准确地说，记录来自第一行第二列的交集。

```
puts "Table name: #{field.table}"
puts "Field name: #{field.name}"
puts "Field length: #{field.length}"
puts "Field type: #{field.type}"

```

我们使用字段对象的属性读取器获取元数据。

```
$ ./metadata.rb
Table name: Writers
Field name: Name
Field length: 25
Field type: 253

```

这是示例的输出。

在与元数据有关的最后一个示例中，我们将打印表中的所有行及其列名。

```
#!/usr/bin/ruby

require 'mysql'

begin
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'
    rs = con.query "SELECT * FROM Writers"

    fields = rs.fetch_fields    
    puts "%3s %s" % [fields[0].name, fields[1].name]

    rs.each_hash do |row|
        puts "%3s %s" % [row['Id'], row['Name']]
    end

rescue Mysql::Error => e
    puts e

ensure
    con.close if con
end

```

我们将`Writers`表的内容打印到控制台。 现在，我们也包括列的名称。

```
fields = rs.fetch_fields    
puts "%3s %s" % [fields[0].name, fields[1].name]

```

第一步，我们获取列名。 它们使用标准的 Ruby 字符串格式化功能进行打印。

```
rs.each_hash do |row|
    puts "%3s %s" % [row['Id'], row['Name']]
end

```

现在，将获取数据并将其打印到控制台。 我们也进行一些格式化。

```
$ ./columnheaders.rb
 Id Name
  1 Jack London
  2 Honore de Balzac
  3 Lion Feuchtwanger
  4 Emile Zola
  5 Truman Capote

```

脚本的输出。

## 预备语句

现在，我们将以预备语句来关注自己。 在编写预备语句时，我们使用占位符，而不是直接将值写入语句中。 预准备的语句可提高安全性和性能。

```
#!/usr/bin/ruby

require 'mysql'

name = "Stefan Zweig"

begin

    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'    
    pst = con.prepare "INSERT INTO Writers(Name) VALUES(?)"
    pst.execute name

rescue Mysql::Error => e
    puts e    

ensure
    con.close if con
    pst.close if pst
end

```

在上面的示例中，我们将新行插入`Writers`表。 我们使用预备语句。

```
pst = con.prepare "INSERT INTO Writers(Name) VALUES(?)"

```

`prepare`方法用于创建预备语句。 `?`字符是一个占位符。 稍后，我们将一个值绑定到该占位符。

```
pst.execute name

```

我们将名称变量中的值绑定到占位符，并执行预备语句。

```
pst.close if pst

```

预备语句已关闭。

```
mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
|  6 | Stefan Zweig      |
+----+-------------------+
6 rows in set (0.00 sec)

```

脚本成功运行后，我们在 Writers 表中看到一个新作者。

## 写图像

有些人喜欢将其图像放入数据库中，有些人则希望将其保留在文件系统中以供其应用程序使用。 当我们处理大量图像时，会出现技术难题。 图像是二进制数据。 MySQL 数据库具有一种特殊的数据类型来存储称为`BLOB`（二进制大对象）的二进制数据。

```
mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.06 sec)

```

对于此示例，我们创建一个名为`Images`的新表。

```
#!/usr/bin/ruby

require 'mysql'

begin

    fin = File.open("woman.jpg" , "rb")
    img = fin.read

rescue SystemCallError => e      
    puts e
ensure
    fin.close if fin 
end

begin

    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'  
    pst = con.prepare("INSERT INTO Images SET Data='%s'" % img.unpack('H*'))
    pst.execute    

rescue Mysql::Error => e
    puts e

ensure
    con.close if con
    pst.close if pst
end

```

在上面的脚本中，我们读取 JPG 图像并将其插入到`Images`表中。

```
fin = File.open("woman.jpg" , "rb")
img = fin.read

```

我们打开并阅读图像。 `read`方法以字符串形式返回数据。

```
pst = con.prepare("INSERT INTO Images SET Data='%s'" % img.unpack('H*'))

```

将该字符串数据放入预备语句中。 在这样做之前，使用 Ruby 字符串对象的`unpack`方法对其进行解码。 解码是必需的，因为图像对象具有许多无法正常处理的特殊字符。

## 读取图像

在前面的示例中，我们已将图像插入数据库表中。 现在，我们将从表中读取图像。

```
#!/usr/bin/ruby

require 'mysql'

begin

    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'          
    rs = con.query "SELECT Data FROM Images LIMIT 1"    
    f = File.new "woman2.jpg", "wb"
    f.write rs.fetch_row.pack 'H*'

rescue Mysql::Error, SystemCallError => e
    puts e

ensure
    con.close if con
    f.close if f
end

```

我们从图片表中读取了一张图片。

```
rs = con.query "SELECT Data FROM Images LIMIT 1" 

```

我们从表中选择一条记录。

```
f = File.new "woman2.jpg", "wb"

```

我们创建一个可写的二进制文件。

```
f.write rs.fetch_row.pack 'H*'

```

我们从先前的 SQL 语句中获取数据并将其写入文件。 `fetch_row`方法返回一个数组对象。 在将数据写入文件之前，使用数组的`pack`方法将其放回原始格式。 对于解码和反转这两种操作，我们使用相同的指令`'H*'`。 它代表十六进制字符串。

现在我们在当前目录中应该有一个名为`woman2.jpg`的映像。 我们可以检查它是否与我们插入表中的图像相同。

## 事务支持

`transaction`是针对一个或多个数据库中数据的数据库操作的基本单位。 事务中所有 SQL 语句的影响可以全部提交给数据库，也可以全部回滚。

默认情况下，MySQL 在自动提交模式下运行。 在这种模式下，对表的所有更改将立即生效。 为了防止这种情况，我们必须关闭自动提交模式。 禁用自动提交后，对事务感知表的更改不会立即永久更改。 要存储更改，我们必须调用`COMMIT`语句或`ROLLBACK`还原它们。 Ruby MySQL 为这些 SQL 语句`commit`和`rollback`提供了便捷的方法。

MySQL 数据库具有不同类型的存储引擎。 最常见的是 MyISAM 和 InnoDB 引擎。 在数据安全性和数据库速度之间需要权衡。 MyISAM 表的处理速度更快，并且不支持事务。 `commit`和`rollback`方法未实现。 他们什么都不做。 另一方面，InnoDB 表可以更安全地防止数据丢失。 他们支持事务。 它们处理较慢。

```
mysql> SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES 
    -> where TABLE_SCHEMA = 'mydb' AND TABLE_NAME='Writers';
+------------+--------+
| TABLE_NAME | ENGINE |
+------------+--------+
| Writers    | InnoDB |
+------------+--------+
1 row in set (0.00 sec)

```

`Writers`表的引擎是 InnoDB，它支持事务。

```
#!/usr/bin/ruby

require 'mysql'

begin
    con = Mysql.new 'localhost', 'user12', '34klq*', 'mydb'

    con.autocommit false

    pst = con.prepare "UPDATE Writers SET Name = ? WHERE Id = ?"
    pst.execute "Leo Tolstoy", "1"
    pst.execute "Boris Pasternak", "2"
    pst.execute "Leonid Leonov"

    con.commit

rescue Mysql::Error => e
    puts e
    con.rollback

ensure
    pst.close if pst
    con.close if con
end

```

在此脚本中，我们尝试更新三行。

```
con.autocommit false

```

自动提交模式被禁用。

```
pst = con.prepare "UPDATE Writers SET Name = ? WHERE Id = ?"
pst.execute "Leo Tolstoy", "1"
pst.execute "Boris Pasternak", "2"
pst.execute "Leonid Leonov"

```

我们执行三个`UPDATE`语句。 最后一个不正确。 第二个参数丢失。

```
con.commit 

```

如果一切正常，则更改将提交到表中。

```
rescue Mysql::Error => e
    puts e
    con.rollback

```

如果发生错误，则会回滚更改。

```
$ ./update.rb
execute: param_count(2) != number of argument(1)

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
|  6 | Stefan Zweig      |
+----+-------------------+
6 rows in set (0.00 sec)

```

运行脚本会出现错误。 但是，该事务已回滚，并且前两行未更改。

[Tweet](https://twitter.com/share) 

这是 MySQL Ruby 教程。 您可能也对 [PostgreSQL Ruby 教程](/db/postgresqlruby/)， [SQLite Ruby 教程](/db/sqliteruby/)或 [MongoDB Ruby 教程](/db/mongodbruby/)感兴趣。