尝试在rails中调用MySql的stored procedure，不过最终放弃了。

手头一个项目有这样一个需求，数据库中有一张学生表students，其中每个学生都有自己的生源地(come_from)，用户希望能够随机的选择一些学生出来，但是要保证每个生源地的学生都有。

我的开发环境是RoR + MySql，简单考虑了一下后，第一个能想到的方法是在rails中生成随机数，然后用offset来得到随机的学生。但是这样做比较麻烦，性能也会很差，因为首先要知道每个生源地下有多少学生，不然的话，生成的随机数可能会过大。

排除了这个选择，于是考虑是否可以在sql级别实现。去查了查MySql的manual，发现有个然数RAND()可以用来生成0到1之间的随机浮点数，感觉可以用这个来做，马上试试看。

SELECT * FROM students WHERE come_from = '上海市' ORDER BY RAND() LIMIT 1;

注意，这里的随机方式与一般的想法不同。一般的想法是生成一个随机数作为offset，然后去找在offset上的数据项；而这里的做法是随机的对数据项进行排序（即shuffle），然后获得第一个。

以上sql多运行了几次下来，确实随机返回不同的学生，离目标近了一步，好事情。剩下来就是如何随机的在所有生源地上选择一个学生，如果学生个数不够，还要再随机选择剩余学生。出于性能考虑，我打算用stored procedure：

CREATE DEFINER=`root`@`%` PROCEDURE `random_students`()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE var_come_from VARCHAR(255);
  DECLARE come_from_cursor CURSOR FOR SELECT come_from FROM eva_development.students GROUP BY come_from;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN come_from_cursor;

  REPEAT
    FETCH come_from_cursor INTO var_come_from;
    SELECT * FROM eva_development.students where come_from = var_come_from order by rand() limit 1;
  UNTIL done END REPEAT;

  CLOSE come_from_cursor;
END

以上代码还是挺简单的，就是先找到所有的生源地，然后循环，以每个生源地作为条件随机出一个学生来。一开始的时候我还想传入一个参数，代表想要得到的学生个数，比如50、100的。因为这个个数通常比生源地个数要多，所以后面还应该随机取出一些学生来。但是我不知道这个sql应该怎么写，因为假设我们可以得到变量student_number代表想要的学生个数、come_from_number代表生源地的个数，那么sql语句应该差不多如下：

SELECT * FROM students LIMIT [b](student_number - come_from_number)[/b];

但是其中加粗的部分，sql自然是不认识的，也就是说LIMIT后面不能接变量，所以我也不知道该怎么办，所幸就拿到stored procedure外面来做了。

以上可以说是实现了MySql端的东东，那么rails方面呢？一开始我尝试使用

Student.find_by_sql('call random_students()')

结果报错说：ActiveRecord::StatementInvalid: Mysql::Error: PROCEDURE vc.testsp can’t return a result set in the given context.

到网上搜了一下，发现rails的wiki里面就有一篇讲怎么使用sp的，于是就按部就班的做了，不过网上的教程有点儿过时了，这里稍微做一个介绍：
[list=1]

先保证自己装的是MySql 5.0+版本。

安装native MySql Connector：gem install mysql。

修改rails中的mysql_adapter.rb，文件在$RUBY_ROOT/lib/ruby/gems/1.8/gems/activerecord-$VERSION/lib/active_record/connection_adapters中，具体修改如下：

ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username, password, database, port, socket], config)

==>

ConnectionAdapters::MysqlAdapter.new(mysql, logger, [host, username, password, database, port, socket, 65536], config)

这是因为sp有可能会返回多个结果集（说白了就是有多个SELECT，像我的sp就是这样），而rails默认的连接设置不支持这个，就会报错，而且即使只返回一个结果集，也会报一样的错误。65536代表的是MySql选项CLIENT_MULTI_STATEMENTS，这样一来就知道怎么回事儿了。

为rails添加一个调用sp的方法：

def select_sp(sql, name = nil)
  rows = select(sql, name = nil)
  while (@connection.more_results?())
    @connection.next_result()
  end        
  return rows
end

这个方法应该添加在ConnectionAdapters::MysqlColumn中（仍然在mysql_adapter.rb中），可以加到SCHEMA STATEMENTS段。

最后在Student.rb里面加入调用sp的方法：

def self.random_select(student_number)
  students = connection.select_sp('call random_students')
  students << find_by_sql(['select * from students order by rand() limit ?', student_number - students.length]) unless students.length >= student_number
  students.flatten[0, student_number]
end

[/list]
写了这么多，以为可以万事大吉了，谁知道调用下来，却报了下面这个错误：Commands out of sync; you can't run this command now.这下可不知道怎么办了，搜了一阵子也没有答案。我猜想可能与多结果集有关，于是写了一个单结果集的sp来调用，果然就没有问题。看来我的这个功能还真不能在sql里面做咧，于是只好全部转到ruby里面，不过还好有MySql的RAND()函数，性能不是太差，而且我数据库里面大概就2w数据，不多。

def self.random_select(number = 50)
  result = []
  students = Student.find_by_sql('select come_from from students group by come_from')
  students.each do |student|
    result << Student.find_by_sql(['select * from students where come_from = ? order by rand() limit 1', student.come_from])
  end
  result << Student.find_by_sql(['select * from students order by rand() limit ?', number - students.length]) unless students.length >= number
  result.flatten[0, number]
  end

评论

2 楼 bernieyoo 2007-10-18  

我看了一下你的Store Procedure，你所谓的多个结果集其实是可以合并到一个结果集里的。可以用如下几种方式：
1、在循环中不要直接执行SQL语句，而是拼接SQL字符串，类似这样的：
    

  select * from ....
   Union 
   Select * from ...

   最后一次执行：
  

exec(@sql)

2、你在循环中取到的结果集的结构是一样的，那可以考虑将每次的结果集存入一个临时表，最后从临时表取出全部结果，类似这样：
   

Create Table #temp(....)
    While 
       insert into #temp select * from ....
   ...
   ...
   Select * from #temp

我以上都是用SQL Server的语法写的，因为mysql不熟，但应该有类似的方法的。

另外，我调用Store Procedure是用DBI来实现的，这样可以实现对多个结果集的处理。但你这个应该是不需要的。

转变一下编程思路，会有意想不到的结果。

1 楼 ivice 2007-10-06  

我以前用mssql和vb写程序的时候，可以设置set nocount on 在最后一个要返回结果的select前面 可以加一个set nocount off。
这样就不会出错，mysql和rails不知道有没有这种用法。