CODEWITHSUNDEEP
mysql
CodeArtist
CodeArtist

Reputation: 133

MySQL Generate Random Data

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?

Upvotes: 8

Views: 14585

Answers (4)

Eric Leschinski
Eric Leschinski

Reputation: 154101

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)

Upvotes: 5

Dewald Swanepoel
Dewald Swanepoel

Reputation: 1681

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')

Upvotes: 5

Hituptony
Hituptony

Reputation: 2860

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.

Upvotes: 2

Arun Kumar M
Arun Kumar M

Reputation: 848

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)

Upvotes: 10

Related Questions