0

How can I make it so when an entry is created in a table, one of the columns unique_id automatically gets filled with a randomly generated integer? Can this be done straight from mysql? I've seen that you can automatically fill it with a timestamp and such, so is it possible to automatically fill it with a randomly generated integer?

Maaz
  • 4,193
  • 6
  • 32
  • 50

4 Answers4

1

IF you need a unique number for id purpose. you may make your column auto-incremented. Remember this that if you are going with this you need to make it either unique or primary key. I think this is the easiest way. You will get a contiguous increment.

Nitu Dhaka
  • 1,300
  • 3
  • 14
  • 29
0

you can try this

SELECT FLOOR(10000 + RAND() * 89999) AS my_random_number
FROM mytable
WHERE "my_random_number" NOT IN (SELECT id FROM mytable)
LIMIT 1

OR SImply this SELECT FLOOR(RAND() * 1000000);

vhadalgi
  • 7,027
  • 6
  • 38
  • 67
  • won't I have to do this everytime? or running this query in sql will make it so the column will automatically generate a random number everytime an entry is created? – Maaz Nov 20 '13 at 06:19
  • you could make a link like you need with this – vhadalgi Nov 20 '13 at 06:21
  • so I can't use something like this `ALTER TABLE files ALTER random_id SET DEFAULT FLOOR(RAND() * 1000000)` – Maaz Nov 20 '13 at 06:31
  • I tried that but it doesn't work, how can I set the DEFAULT value of random_id column to a function that creates a `unique` random number – Maaz Nov 20 '13 at 06:37
  • i have no idea about `php` you asked for `mysql cmd` i gave it – vhadalgi Nov 20 '13 at 07:14
0

use RAND() and concat date and time with it so you can have a unique number..

Semi-Friends
  • 480
  • 5
  • 17
0

Use UUID function,A UUID is designed as a number that is globally unique in space and time. http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

Surabhil Sergy
  • 1,946
  • 1
  • 23
  • 40