尝试在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 (student_number - come_from_number);

但是其中加粗的部分，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的，于是就按部就班的做了，不过网上的教程有点儿过时了，这里稍微做一个介绍：

先保证自己装的是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

写了这么多，以为可以万事大吉了，谁知道调用下来，却报了下面这个错误：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