MySQL Ruby

最后修改于 2020 年 7 月 6 日

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

ZetCode 上还有类似的 MySQL C API 教程、MySQL Visual Basic 教程，以及 MySQL Python 教程。

您也可以考虑查看 MySQL 教程。

MySQL & Ruby

MySQL 是一个领先的开源数据库管理系统。它是一个多用户、多线程的数据库管理系统。MySQL 在 Web 领域尤其流行。它是非常流行的 LAMP 平台的一部分，该平台由 Linux、Apache、MySQL 和 PHP 组成。目前 MySQL 由 Oracle 公司所有。MySQL 数据库可在大多数主流操作系统平台上使用。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 和 Name。

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 表中的所有数据。这次我们使用结果集的 each 方法来遍历数据。

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

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

$ ./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 

query 方法返回第一个结果集。我们从这个结果集中获取一行。

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

这两行返回结果集中的列数和行数。请注意，这里的字段（field）是列（column）的同义词。返回的数据是元数据。

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

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

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)

我们执行 affected.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

我们将 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

我们从 Images 表中读取一张图像。

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 的图像。我们可以检查它是否与我们插入到表中的图像相同。

事务支持

事务是针对一个或多个数据库中的数据执行的数据库操作的原子单元。事务中所有 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)

运行脚本会产生一个错误。但是，事务被回滚，前两行没有被更改。

这是 MySQL Ruby 教程。您可能还对 PostgreSQL Ruby 教程、PyMySQL 教程、SQLite Ruby 教程或 MongoDB Ruby 教程感兴趣。