Ruby DBI操作MySql数据库的方法
用Ruby操作数据库，可能不会在Ruby on Rails中用得到。但是这种使用Ruby直接操作数据库的方法，有时候还真可以派得上用场。我们都知道Ruby是一种类似Perl和Python的语言，但其功能却比这两种语言强大不知道多少倍。Ruby几乎全部继承了Perl的优点，又克服了Perl的缺点。而且完全面向对象，其功能之强大，Perl和Python绝对望尘莫及。
Ruby甚至可以取代传统的shell脚本语言，例如我们编写shell程序都使用awk之类的语言，用Ruby也完全可以做得到。但是你可以使用awk连接数据库么？当然不能。有人使用Python连接数据库，并在一定程度上也实现了替代awk的功能，这当然完全没有问题。但这里给出的是Ruby的实现方法。其实也是蛮简单的。简单归简单，如果没做过的话，看上去也很难。下面我们就开始：
一、安装ruby-dbi
下载地址：http://rubyforge.org/projects/ruby-dbi/
下载ruby-DBI（我下载的是dbi-0.1.1.tar.gz）,解压后进入目录运行如下命令：
E:\ruby-dbi>ruby setup.rb config --with=dbi,dbd_mysql
entering config phase...
config done.
E:\ruby-dbi>ruby setup.rb setup
entering setup phase...
setting #! line to "#!e:/ruby/bin/ruby"
setup.rb: skip bin/proxyserver(dbd_proxy) by user option
setup.rb: skip ext/dbd_sqlite(dbd_sqlite) by user option
setup.rb: skip ext/dbd_sybase(dbd_sybase) by user option
setup done.
E:\ruby-dbi>ruby setup.rb install
entering install phase...
mkdir -p e:/ruby/bin
install sqlsh.rb e:/ruby/bin
setup.rb: skip bin/proxyserver(dbd_proxy) by user option
mkdir -p e:/ruby/lib/ruby/site_ruby/1.8/DBD/Mysql
install Mysql.rb e:/ruby/lib/ruby/site_ruby/1.8/DBD/Mysql/Mysql.rb
mkdir -p e:/ruby/lib/ruby/site_ruby/1.8/dbi
install columninfo.rb e:/ruby/lib/ruby/site_ruby/1.8/dbi
install row.rb e:/ruby/lib/ruby/site_ruby/1.8/dbi
install sql.rb e:/ruby/lib/ruby/site_ruby/1.8/dbi
install trace.rb e:/ruby/lib/ruby/site_ruby/1.8/dbi
install utils.rb e:/ruby/lib/ruby/site_ruby/1.8/dbi
install version.rb e:/ruby/lib/ruby/site_ruby/1.8/dbi
install dbi.rb e:/ruby/lib/ruby/site_ruby/1.8
setup.rb: skip ext/dbd_sqlite(dbd_sqlite) by user option
setup.rb: skip ext/dbd_sybase(dbd_sybase) by user option
install done.
二、安装MySQL/Ruby for Windows
下载地址：http://www.vandomburg.net/pages/mysql-ruby-windows
我的文件为：mysql-ruby-windows-2.7.3-r1.zip
安装方法（我直接把英文的步骤拷贝过来吧，这个我测试过，没问题）：
1、Select and copy the .so file of your choice to %RUBYARCHDIR% and rename it to mysql.so (e.g. C:\ruby\lib\ruby\site_ruby\1.8\i386-msvcrt\mysql.so).
The 4.1 and 5.0 directories correspond to your MySQL server version. (For all you tech savvies: they are generally interchangable, but differ slightly on byte level in terms of argument types.)
The ‘vanilla’ library is the stock MySQL/Ruby 2.7.3 we all know and love. The ‘performance’ library has Stefan Kaes’ garbage correction patch applied that can increase performance anywhere from 5% to 30% depending on the size of the data set you’re accessing. The flip-side is this: it is less tested, so take your pick!
2、MySQL/Ruby needs a DLL dependency satisfied in order to run. You can do this by either:
(Recommended) Including the MySQL bindir in your path (e.g. C:\Program Files\MySQL\MySQL Server 5.0\bin).
3、Copying libmySQL.dll from your MySQL bindir or source package to your Ruby bindir (e.g. C:\ruby\bin).
To verify, try to run the following code:
require 'mysql'
puts Mysql::VERSION
If it outputs 20703, then you’re all set up! Just be sure that you’re MySQL server has TCP/IP connections enabled.
三、Linux 上的安装方法：
1、对于ruby-dbi没有什么区别，此略。
2、MySQL/Ruby for Linux
下载地址：http://tmtm.org/downloads/mysql/ruby/
我下载的文件：mysql-ruby-2.7.1.tar.gz
安装方法（因为比较懒惰，也弄一段英文的安装步骤在此吧）：
The module is distributed as a compressed tar file, which you should unpack after downloading it. For example, if the current version is 2.7.1, the distribution file can be unpacked using either of the following commands:
% tar zxf mysql-ruby-2.7.1.tar.gz
% gunzip < mysql-ruby-2.7.1.tar.gz | tar xf -
After unpacking the distribution, change location into its top-level directory and configure it using the extconf.rb script in that directory:
% ruby extconf.rb
If extconf.rb successfully locates your MySQL header file and library directories, you can proceed to build and install the module. Otherwise, it indicates what it could not find, and you'll need to run the command again with additional options that specify the appropriate directory locations. For example, if your header file and library directories are /usr/local/mysql/include/mysql and /usr/local/mysql/include/lib, the configuration command looks like this:
% ruby extconf.rb \
--with-mysql-include=/usr/local/mysql/include/mysql \
--with-mysql-lib=/usr/local/mysql/lib/mysql
Alternatively, tell extconf.rb where to find the mysql_config program. In that case, extconf.rb runs mysql_config to locate the header and library files:
% ruby extconf.rb --with-mysql-config=/usr/local/mysql/bin/mysql_config
After configuring the distribution, build and install the module:
% make
% make install
You might need to run the installation command as root.
四、测试程序
编辑dbitest.rb文件如下：（这段代码抄得别人的，我测试过了，没有问题）
require 'dbi'
begin
#连接数据库
dbh=DBI.connect("DBI:Mysql:dbi_development:localhost","root","mypassword")
dbh.columns("articles").each do |h|
p h
end
#示范3种事务处理方式
#手动commit
dbh["AutoCommit"]=false
1.upto(10) do |i|
sql = "insert into articles (name, author) VALUES (?, ?)"
dbh.do(sql, "Song #{i}", "#{i}")
end
dbh.commit
#使用transaction方法
dbh.transaction do |dbh|
1.upto(10) do |i|
sql = "insert into articles (name, author) VALUES (?, ?)"
dbh.do(sql, "Song #{i}", "#{i}")
end
end
#使用SQL语句
dbh.do("SET AUTOCOMMIT=0")
dbh.do("BEGIN")
dbh["AutoCommit"]=false
dbh.do("UPDATE articles set name='test' where ")
dbh.do("COMMIT")
#查询
sth=dbh.execute("select count(id) from articles")
puts "bookCount:#{sth.fetch[0]}"
sth.finish
begin
sth=dbh.prepare("select * from articles")
sth.execute
while row=sth.fetch do
p row
end
sth.finish
rescue
end
#上面这段查询可以改写为：
#dbh.select_all("select * from articles") do |row|
# p row
#end
#使用工具类输出xml格式结果集以及测量查询时间
sql="select * from articles"
mesuretime=DBI::Utils::measure do
sth=dbh.execute(sql)
end
puts "SQL:#{sql}"
puts "Time:#{mesuretime}"
rows=sth.fetch_all
col_names=sth.column_names
sth.finish
puts DBI::Utils::XMLFormatter.table(rows)
dbh.do("delete from articles")
rescue DBI::DatabaseError=>e
puts "error code:#{e.err}"
puts "Error message:#{e.errstr}"
ensure
dbh.disconnect if dbh
end
现在运行>ruby dbitest.rb测试就可以了。
