Adding RANDOM alias to RAND in MySQL without Changing Ruby on Rails Code

While building the new website for http://www.sqlconverter.com, I recently ran into a slight compatibility issue between the SQLite3 database used for local initial development and MySQL when the site was prepared for production. What was the compatibility issue? It was the random function!

The issue popped up in the function used by the main page to return three random testimonials (in random order) for presentation on the homepage. The RoR code for the function is:

In models/testimonial.rb:

def self.top_random
  number_to_return = 3
  Testimonial.find(:all, :limit => number_to_return, :order => "RANDOM()")
end

The highlighted ActiveRecord call more or less translates into this SQL:

SELECT * FROM testimonials ORDER BY RANDOM() LIMIT 3

The SQL server is expected to return three (3) entries in random order. It works great in SQLite and PostgreSQL. It also works great in MySQL, except MySQL doesn’t have RANDOM() but rather has a RAND() - which does the exact same thing.

However, I did not want to have to change the RoR code, which was already passing its unit tests. Besides doing so would cause the code to not work in the other databases, which define the RANDOM function. The quickest solution turns out to be to define a custom RANDOM function for MySQL!

MySQL statement to define RANDOM() as an alias for its native RAND()

CREATE FUNCTION random() RETURNS FLOAT NO SQL SQL SECURITY INVOKER RETURN rand();

Originally Posted

This SQL statement was originally posted at Selecting random in rails sqlite vs mysql by Jason Weathered, thank you Jason!.

And everything worked. I hope this will help you too.