MySQL Alter table, add a column with a unique random value

Question

I have a table in which I added a column called phone - the table also has an identifier specified as a primary key that automatically indicates. How to insert a random value into a column of a phone that will not be duplicated. The following UPDATE statement inserted random values, but not all of them were unique. In addition, I am not for sale, I also applied the phone field correctly, but I encountered problems when trying to install it as an int (11) w / ALTER TABLE command (basically, it worked correctly, but when adding a line with a new phone number, inserted value was transferred to another number).

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

Spec table

  + ------------ + -------------- + ------ + ----- + -------- - + ---------------- +
 |  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 |  |
 + ------------ + -------------- + ------ + ----- + -------- - + ---------------- +

Answer 1

try it

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

Answer 2

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

Answer 3

Did you try to create a phone and then check if this value is in the column?

Answer 4

I would deal with this by creating a (temporary) table containing numbers in the range you need, then scrolling through each record in the table that you want to set as random numbers. Select a random item from the temp table, refresh the table, and remove it from the temp table. Not nice, not fast .. but easy to develop and easy to test.