MySQL Alter table, add column with unique random value

Question

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

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| phone      | varchar(11)  | NO   |     | NULL    |                |
| age        | tinyint(3)   | NO   |     | NULL    |                |
| test       | tinyint(4)   | NO   |     | 0       |                |
| note       | varchar(100) | YES  |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

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. — O. Jones, Jan 11 '11 at 23:38

Nabil Kadimi · Answer 1 · 2017-02-01T05:16:12.137

-- 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 = (
  SELECT GROUP_CONCAT(SUBSTRING(chars_str , 1+ FLOOR(RAND()*LENGTH(chars_str)) ,1) SEPARATOR '')
  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 '')
  FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) AS dummy_tbl
);

Random string in MySQL

score 3 · Accepted Answer · answered Jan 11 '11 at 18:24

3

Try this

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

answered Jan 11 '11 at 18:24

shankhan

6,343
2
19
22

1

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
2

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

score 1 · Answer 3 · answered Jan 11 '11 at 18:28

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.

MySQL Alter table, add column with unique random value

3 Answers3

Linked