Rietta
Rietta: Web Apps Where Security Matters
You are reading The Rietta Blog, a publication about the web since 2005.

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:

models/testimonial.rb
1
2
3
4
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:

1
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_random_func_in_mysql.sql
1
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.

About Frank Rietta

Frank Rietta's photo

Frank Rietta is specialized in working with startups, new Internet businesses, and in developing with the Ruby on Rails platform to build scalable businesses. He is a computer scientist with a Masters in Information Security from the College of Computing at the Georgia Institute of Technology. He teaches about security topics and is a contributor to the security chapter of the 7th edition of the "Fundamentals of Database Systems" textbook published by Addison-Wesley.