Tuesday 3 December 2019

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. );
  9. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(201, 'Microphone 10', 'Supplier #A', 'Brand 11', 100, 'No');
  10. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(202, 'Microphone 11', 'Supplier #B', 'Brand 12', 50, 'Yes');
  11. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(203, 'Microphone 12', 'No Supplier', 'Brand 13', 200, 'No');
  12. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(204, 'Microphone 13', 'Supplier #C', 'Brand 14', 150, 'Yes');
  13. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(205, 'Microphone 14', 'No Supplier', 'Brand 15', 250, 'No');
  14. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(206, 'Microphone 15', 'Supplier #D', 'Brand 16', 80, 'Yes');
  15. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(207, 'Microphone 16', 'Supplier #E', 'Brand 17', 90, 'Yes');
  16. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(208, 'Microphone 17', 'Supplier #A', 'Brand 18', 205, 'Yes');
  17. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(209, 'Microphone 18', 'Supplier #B', 'Brand 19', 45, 'Yes');
  18. INSERT INTO `Microphone` (`cod`, `Description`, `Supplier`, `Brand`, `Price`, `InStock`) VALUES(210, 'Microphone 19', 'No Supplier', 'Brand 20', 65, 'Yes');
  19. 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:
codDescriptionSupplierBrandPriceInStock
201Microphone 10Supplier #ABrand 11100No
202Microphone 11Supplier #BBrand 1250Yes
203Microphone 12No SupplierBrand 13200No
204Microphone 13Supplier #CBrand 14150Yes
205Microphone 14No SupplierBrand 15250No
206Microphone 15Supplier #DBrand 1680Yes
207Microphone 16Supplier #EBrand 1790Yes
208Microphone 17Supplier #ABrand 18205Yes
209Microphone 18Supplier #BBrand 1945Yes
210Microphone 19No SupplierBrand 2065Yes
211Microphone 20No SupplierBrand 2160Yes
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:
codDescriptionSupplierBrandPriceInStockDiscount Percentage
201Microphone 10Supplier #ABrand 11100No4.665044848495205
202Microphone 11Supplier #BBrand 1250Yes8.350913448586049
203Microphone 12No SupplierBrand 13200No7.759433004781076
204Microphone 13Supplier #CBrand 14150Yes3.744424985483685
205Microphone 14No SupplierBrand 15250No5.443826220411645
206Microphone 15Supplier #DBrand 1680Yes5.985856452943623
207Microphone 16Supplier #EBrand 1790Yes3.5978039108196307
208Microphone 17Supplier #ABrand 18205Yes0.03145050260373438
209Microphone 18Supplier #BBrand 1945Yes9.363841087896228
210Microphone 19No SupplierBrand 2065Yes6.724854239006391
211Microphone 20No SupplierBrand 2160Yes5.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:
codDescriptionSupplierBrandPriceInStockDiscount Percentage
201Microphone 10Supplier #ABrand 11100No1
202Microphone 11Supplier #BBrand 1250Yes5
203Microphone 12No SupplierBrand 13200No9
204Microphone 13Supplier #CBrand 14150Yes9
205Microphone 14No SupplierBrand 15250No9
206Microphone 15Supplier #DBrand 1680Yes8
207Microphone 16Supplier #EBrand 1790Yes2
208Microphone 17Supplier #ABrand 18205Yes7
209Microphone 18Supplier #BBrand 1945Yes9
210Microphone 19No SupplierBrand 2065Yes2
211Microphone 20No SupplierBrand 2160Yes3
Table 3. Example 2 result

0 comments:

Post a Comment