Take the 2-minute tour ×

I have a table that I added a column called phone - the table also has an id set as a primary key that auto_increments. How can I insert a random value into the phone column, that won't be duplicated. The following UPDATE statement did insert random values, but not all of them unique. Also, I'm not sold I cast the phone field correctly either, but ran into issues when trying to set it as a int(11) w/ the ALTER TABLE command (mainly, it ran correctly, but when adding a row with a new phone number, the inserted value was translated into a different number).

UPDATE Ballot SET phone = FLOOR(50000000 * RAND()) + 1;

Table spec's

| phone      | varchar(11)  | NO   |     | NULL    |                |
| age        | tinyint(3)   | NO   |     | NULL    |                |
| test       | tinyint(4)   | NO   |     | 0       |                |
| note       | varchar(100) | YES  |     | NULL    |                |
share|improve this question
Mind if I ask why you'd need to do this? –  Joe Stefanelli Jan 11 '11 at 18:27
Sure, I'm working on an existing table that's being converted from a postcard registration process via ballot code to a registration process that will allow voters to verify themselves through a automated voice service. The service only can allow one survey to be filled out for each phone number. –  Schoffelman Jan 11 '11 at 22:07
Now that I think abou it - the phone field doesn't need to be random - just as long as it's unique and not an existing 10 digit phone number. So something like UPDATE Ballot SET phone = id; should/would work. –  Schoffelman Jan 11 '11 at 22:14
In North America, any telephone number like (nnn) 1xx-yyyy is guaranteed to be invalid. That may help. –  Ollie Jones Jan 11 '11 at 23:38
add comment

4 Answers

up vote 1 down vote accepted

Try this

UPDATE Ballot SET phone = FLOOR(50000000 * RAND()) * id;
share|improve this answer
That still wouldn't satisfy his requirement for guaranteed uniqueness... 60 (random number) * 1 (id) == 30 (just as random) * 2 (id). –  kander Jan 11 '11 at 18:26
then just add fixnumber * id. i.e 893433*id –  shankhan Jan 11 '11 at 18:28
I did something similar to this to fill in the field, I just don't know if it was the best / correct solution. –  Schoffelman Jan 11 '11 at 22:01
add comment
-- tbl_name: Table
-- column_name: Column
-- chars_str: String containing acceptable characters
-- n: Length of the random string
-- dummy_tbl: Not a parameter, leave as is!
UPDATE tbl_name SET column_name = (
  FROM (SELECT 1 /* UNION SELECT 2 ... UNION SELECT n */) AS dummy_tbl

-- Example
UPDATE tickets SET code = (
  SELECT GROUP_CONCAT(SUBSTRING('123abcABC-_$@' , 1+ FLOOR(RAND()*LENGTH('123abcABC-_$@'))     ,1) SEPARATOR '')

Random string in MySQL

share|improve this answer
Perfect solution :) –  kovpack Dec 16 '13 at 14:47
add comment

Have you tried, creating the phone, then checking if that value isn't already in the column ?

share|improve this answer
Ideally you'll want to solve this set-based. Doing an update, followed by a check, means you'll be looping, which is computationally expensive. (not that my solution isn't a loop, and it's at least as expensive...) –  kander Jan 11 '11 at 18:30
add comment

I'd tackle this by generating a (temporary) table containing the numbers in the range you need, then looping through each record in the table you wish to supply with random numbers. Pick a random element from the temp table, update the table with that, and remove it from the temp table. Not beautiful, nor fast.. but easy to develop and easy to test.

share|improve this answer
add comment

Your Answer


