MySQL – Function – Random Number

Background

For a play application that I am working on, rather than to hardcode all of the data, it seems fitting to have the system generate some of the data itself.

For one particular integer column, I wanted random integer data.

BTW, my targeted database is running on MySQL.

 

Function

Here is a simple SQL for generating random int.

 

Outline

  1. Drop function if it exists already
  2. Temporarily change delimiter from ; to //
  3. Create Function
  4. Revert delimiter to ;

 

SQL


DROP FUNCTION IF EXISTS funcRandom;

delimiter //


CREATE FUNCTION funcRandom
(
      low  INT 
    , high int
)
RETURNS int

DETERMINISTIC

BEGIN

    RETURN (
                floor
                (
                    rand() *
                    (
                          ( high + 1 )
                        - ( low )
                    )    
                )    
                + low 
          );
                
END;
//

 

Source Code

GitHub

Gist

DanielAdeniji/mysql.randomNumber.sql

Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s