Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Generate Random Data

Tags:

mysql

In MySQL I have some tables I need to randomize the phone numbers and Email addresses to be randomly generated for development purposes.

In MySQL how could I generate 7 digit unique random numbers for the phone numbers?

How can I generate random email address like [email protected].

How can I generate this random data with MySQL Queries?

like image 394
CodeArtist Avatar asked Aug 21 '13 19:08

CodeArtist


People also ask

Can SQL generate random records in database?

In SQL Server, it is quite easy to do this thanks to the NEWID() system function. The NEWID() system function creates a unique value of type uniqueidentifier. There's no need to add a new column to your table just to have the ability of randomly selecting records from your table.

How do I shuffle data in MySQL?

If you need the rows in a random order, grab the required number of rows and then use PHP's shuffle function on the array of rows returned. There can be quite a performance penalty with using ORDER BY RAND() in a query, depending on how many records there are.


4 Answers

MySQL rand() Returns a random floating-point value in the range 0 <= value < 1.0.

Multiply that by another number: UPPER_BOUND and get the floor of that, and you will get a random integer between 0 and (UPPER_BOUND-1) like this:

SELECT floor(rand() * 10) as randNum;

That will give you only one random number between 0 and 10.

Change the 10 to the number one higher than you want to generate.

Something like this :

UPDATE user 
SET email = CONCAT(FLOOR(rand() * 10000000),'@mailinator.com'), 
    PhoneNo = FLOOR(rand() * 10000000)
like image 110
Arun Kumar M Avatar answered Oct 09 '22 19:10

Arun Kumar M


This should give you a random number of 7 digits length

SELECT FLOOR(1000000 + RAND() * 8999999)

And something like this should update your phone numbers and e-mail addresses according to your requirement

UPDATE Customers 
SET phone = CAST(FLOOR(1000000 + RAND(8999999) AS VARCHAR), 
email = CONCAT(CAST(FLOOR(1000000 + RAND(8999999) AS VARCHAR), '@mailinator.com')
like image 33
Dewald Swanepoel Avatar answered Oct 09 '22 19:10

Dewald Swanepoel


MySQL Generate random data walkthrough:

Random number between 0 (inclusive) and 1 exclusive:

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.5485130739850114 |
+--------------------+                                                     
1 row in set (0.00 sec)

Random int between 0 (inclusive) and 10 exclusive:

mysql> select floor(rand()*10);
+------------------+
| floor(rand()*10) |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

Random letter or number:

mysql> select concat(substring('ABCDEF012345', rand()*36+1, 1));
+---------------------------------------------------------------------------+
| concat(substring('ABCDEF012345', rand()*36+1, 1))                         |
+---------------------------------------------------------------------------+
| F                                                                         |
+---------------------------------------------------------------------------+
1 row in set (0.00 sec)

Random letter a to z:

mysql> select char(round(rand()*25)+97);
+---------------------------+
| char(round(rand()*25)+97) |
+---------------------------+
| s                         |
+---------------------------+
1 row in set (0.00 sec)

Random 8 character alphanumeric string:

mysql> SELECT LEFT(UUID(), 8);
+-----------------+
| LEFT(UUID(), 8) |
+-----------------+
| c26117af        |
+-----------------+
1 row in set (0.00 sec)

Random capital letter in MySQL:

mysql> select CHAR( FLOOR(65 + (RAND() * 25)));
+----------------------------------+
| CHAR( FLOOR(65 + (RAND() * 25))) |
+----------------------------------+
| B                                |
+----------------------------------+
1 row in set (0.00 sec)

Load a random row into a table:

mysql> create table penguin (id INT primary key auto_increment, msg TEXT);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into penguin values (0, LEFT(UUID(), 8));
Query OK, 1 row affected (0.00 sec)

mysql> select * from penguin;
+------+----------+
| id   | msg      |
+------+----------+
|    0 | abab341b |
+------+----------+
1 row in set (0.00 sec)

Load random rows:

Make a procedure called dennis that loads 1000 random rows into penguin.

mysql> delimiter ;;
mysql> drop procedure if exists dennis;;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create procedure dennis()
    -> begin
    -> DECLARE int_val INT DEFAULT 0;
    -> myloop : LOOP
    ->   if (int_val = 1000) THEN
    ->     LEAVE myloop;
    ->   end if;
    ->   insert into penguin values (0, LEFT(UUID(), 8));
    ->   set int_val = int_val +1;
    -> end loop;
    -> end;;
Query OK, 0 rows affected (0.00 sec)  

mysql> call dennis();;

mysql> select * from penguin;;
+------+----------+                      
| id   | msg      |              
+------+----------+                   
|    0 | abab341b |                 
|    1 | c5dc08ee |           
|    2 | c5dca476 |
...
+------+----------+

Update all rows in a table to have random data:

mysql> create table foo (id INT primary key auto_increment, msg TEXT);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into foo values (0,'hi');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values (0,'hi2');
Query OK, 1 row affected (0.00 sec)

mysql> insert into foo values (0,'hi3');
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+----+------+
| id | msg  |
+----+------+
|  1 | hi   |
|  2 | hi2  |
|  3 | hi3  |
+----+------+
3 rows in set (0.00 sec)

mysql> update foo set msg = rand();
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from foo;
+----+---------------------+
| id | msg                 |
+----+---------------------+
|  1 | 0.42576668451145916 |
|  2 | 0.6385560879842901  |
|  3 | 0.9154804171207178  |
+----+---------------------+
3 rows in set (0.00 sec)
like image 44
Eric Leschinski Avatar answered Oct 09 '22 20:10

Eric Leschinski


Here is an online tool to generate random data with many options. http://www.generatedata.com/

Just enter the parameters to define what kind of random data you want, and export it to the appropriate format, then you can load it.

like image 37
Hituptony Avatar answered Oct 09 '22 21:10

Hituptony