MariaDb Quick-tip #4 - Random int for each row

#sql #mysql #mariadb

MariaDB tips and tricks

This is part of a series of quick tips and tricks I have accumulated over the years, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.

Random int for each row

The trick to generate a random number for each row is to use the RAND() function, multiply it by the maximum number you need and then cast the number as INT.

The code below show how to create a number between 0 and 24 and another example of how to create a number between 15 and 25.

The calculation of the random int for each row is not very efficient, so you should consider using a more efficient method for generating the random numbers if you need it in production code.

You have to do something slightly different to generate a range of int on the SQL Server

CREATE OR REPLACE TEMPORARY TABLE names (Name VARCHAR(50));

INSERT INTO names (Name) 
VALUES ('Joe'), ('Bob'), ('Anne'), ('Jane');

-- Generate random ints between 0 and 24
SELECT Name, CAST(MOD(RAND() * 100000, 25) AS INT) AS `Random Int`
  FROM names;

-- Generate random ints between 15 and 25
SELECT Name, CAST(MOD(RAND() * 100000, (25-15)) AS INT) + 15 AS `Random Int`
  FROM names;