How to Get 0 or 1 Randomly in MySQL/MariaDB Select Statement?

In MySQL/MariaDB we have the RAND() function to generate random floating-point values between 0 and 1. In this tutorial we look at how to use it to get a random integer value 0 or 1 in the SELECT statement.

Using ROUND()

Quite simply, we could use ROUND() on the floating-point value we get from RAND(). For example:

SELECT ROUND(RAND())

This would round up to 1 if the floating-point value is greater than or equal to 0.5, and round down to 0 otherwise.

Using Modulus

With the modulo operator (%) we can check if the remainder of dividing a randomly generated integer with 2 is 0 (i.e. all even numbers) or 1 (i.e. all odd numbers). To do so, we simply need to:

  1. Multiply the floating-point value we get from RAND() with 10 to get a number between 0 and 10, and;
  2. Round it off to get rid of its fractional part (for example by using FLOOR, ROUND or CEIL functions).

Putting that logic together, the query would look something like this:

SELECT FLOOR(RAND()*10) % 2

-- Or using the alternative modulus syntax:
SELECT MOD(FLOOR(RAND()*10), 2);

SELECT FLOOR(RAND()*10) MOD 2;

This post was published (and was last revised ) by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.