How to generate random numbers in MySQL

In this article, we will learn how to generate random integers and floating numbers in MySQL through practical examples.

MySQL Rand function

MySQL has a native function that allows random number generation. We are talking about the Rand function.

Something we have to keep in mind is that the Rand function generates floating numbers between 0 and 1. But later we will see an example of how to also generate integers with the Rand function.

MySQL Rand function syntax

The syntax of the Rand function is quite simple since it only receives one optional parameter. The parameter is used to indicate an initialization value. The Rand function will return the same value if it is invoked several times with the same initialization parameter.

1. Rand (initialization_parameter)

Script 1. MySQL Rand function syntax

MySQL generate random number example

In our first example, we are going to create a Microfone table. Then we will build a query that generates random discounts percentages for each row of the table.

The Microphone table will have the following structure and content:

1. CREATE TABLE `Microphone` (
2. `cod` int(11) NOT NULL,
3. `Description` varchar(50) NOT NULL,
4. `Supplier` varchar(50) NOT NULL,
5. `Brand` varchar(30) NOT NULL,
6. `Price` float NOT NULL,
7. `InStock` varchar(10) NOT NULL
8. );
10. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(201, 'Microphone 10', 'Supplier #A', 'Brand 11', 100, 'No');
11. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(202, 'Microphone 11', 'Supplier #B', 'Brand 12', 50, 'Yes');
12. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(203, 'Microphone 12', 'No Supplier', 'Brand 13', 200, 'No');
13. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(204, 'Microphone 13', 'Supplier #C', 'Brand 14', 150, 'Yes');
14. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(205, 'Microphone 14', 'No Supplier', 'Brand 15', 250, 'No');
15. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(206, 'Microphone 15', 'Supplier #D', 'Brand 16', 80, 'Yes');
16. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(207, 'Microphone 16', 'Supplier #E', 'Brand 17', 90, 'Yes');
17. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(208, 'Microphone 17', 'Supplier #A', 'Brand 18', 205, 'Yes');
18. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(209, 'Microphone 18', 'Supplier #B', 'Brand 19', 45, 'Yes');
19. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(210, 'Microphone 19', 'No Supplier', 'Brand 20', 65, 'Yes');
20. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(211, 'Microphone 20', 'No Supplier', 'Brand 21', 60, 'Yes');

Script 2. MySQL Rand function syntax

Let’s check the content of the table we just created. For this, we can execute the following query:

1. SELECT * FROM `microphone`

Script 3. Query to retrieve Microphone table rows

Result:

cod	Description	Supplier	Brand	Price	InStock
201	Microphone 10	Supplier #A	Brand 11	100	No
202	Microphone 11	Supplier #B	Brand 12	50	Yes
203	Microphone 12	No Supplier	Brand 13	200	No
204	Microphone 13	Supplier #C	Brand 14	150	Yes
205	Microphone 14	No Supplier	Brand 15	250	No
206	Microphone 15	Supplier #D	Brand 16	80	Yes
207	Microphone 16	Supplier #E	Brand 17	90	Yes
208	Microphone 17	Supplier #A	Brand 18	205	Yes
209	Microphone 18	Supplier #B	Brand 19	45	Yes
210	Microphone 19	No Supplier	Brand 20	65	Yes
211	Microphone 20	No Supplier	Brand 21	60	Yes

Table 1. Microphone table rows

Now we can build our example query to generate a column with random discount percentages between 0 and 10 for each row of the table.

1. SELECT *, rand()*10 as `Discount Percentage` FROM `microphone`

Script 4. MySQL random number generation example 1

Result:

cod	Description	Supplier	Brand	Price	InStock	Discount Percentage
201	Microphone 10	Supplier #A	Brand 11	100	No	4.665044848495205
202	Microphone 11	Supplier #B	Brand 12	50	Yes	8.350913448586049
203	Microphone 12	No Supplier	Brand 13	200	No	7.759433004781076
204	Microphone 13	Supplier #C	Brand 14	150	Yes	3.744424985483685
205	Microphone 14	No Supplier	Brand 15	250	No	5.443826220411645
206	Microphone 15	Supplier #D	Brand 16	80	Yes	5.985856452943623
207	Microphone 16	Supplier #E	Brand 17	90	Yes	3.5978039108196307
208	Microphone 17	Supplier #A	Brand 18	205	Yes	0.03145050260373438
209	Microphone 18	Supplier #B	Brand 19	45	Yes	9.363841087896228
210	Microphone 19	No Supplier	Brand 20	65	Yes	6.724854239006391
211	Microphone 20	No Supplier	Brand 21	60	Yes	5.532748238679719

Table 2. Example 1 result

MySQL random integer generation example

Our second example will be similar to the previous one, but this time we will generate discount percentages with integer numbers between 1 and 10:

1. SELECT *, FLOOR(1 + (RAND() * 10)) as `Discount Percentage` FROM `microphone`

Script 5. MySQL random number generation example 2

Result:

cod	Description	Supplier	Brand	Price	InStock	Discount Percentage
201	Microphone 10	Supplier #A	Brand 11	100	No	1
202	Microphone 11	Supplier #B	Brand 12	50	Yes	5
203	Microphone 12	No Supplier	Brand 13	200	No	9
204	Microphone 13	Supplier #C	Brand 14	150	Yes	9
205	Microphone 14	No Supplier	Brand 15	250	No	9
206	Microphone 15	Supplier #D	Brand 16	80	Yes	8
207	Microphone 16	Supplier #E	Brand 17	90	Yes	2
208	Microphone 17	Supplier #A	Brand 18	205	Yes	7
209	Microphone 18	Supplier #B	Brand 19	45	Yes	9
210	Microphone 19	No Supplier	Brand 20	65	Yes	2
211	Microphone 20	No Supplier	Brand 21	60	Yes	3

Table 3. Example 2 result