2

I'm creating a website that will be updated with contributor posts, comments, etc, etc and I'm wondering the best way of creating the id's to prevent there ever being a duplicate in ANY of the tables. I'm using a timestamp at the moment along with a unique number at the end, like:

$time = time();
$id= time().'-'.mt_rand(1000, 9999);

To Output:

1378291391-1167, 1378294733-8990, 1378294934-3336, etc, etc, etc.

Can anyone see a problem with this type of id creation system?

user2737457
  • 293
  • 1
  • 5
  • 19
  • Yes, random numbers aren't guaranteed unique – Mark Baker Sep 16 '13 at 14:38
  • 1
    This sounds like an XY-problem. http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem - please explain why you need the ids to be unique over multiple tables and we'll tell you why it's a bad idea. – Halcyon Sep 16 '13 at 14:41
  • Why does the id have to be globally unique? `foo.id`, `bar.id`, etc... ARE globally unique, even if the id portion can be duplicated in multiple tables. – Marc B Sep 16 '13 at 14:44
  • I'd just like to keep the id's the same length really as they are the user id's needed for each profile page identification. There's no real need for them to be unique over multiple tables, just to be a consistent format I guess – user2737457 Sep 16 '13 at 14:44
  • Thanks @MarkBaker but am I right in thinking that it would be Extremely unlikely for there to ever be 2 ID's the same using this system? – user2737457 Sep 16 '13 at 14:59
  • Unlikely, but not impossible (about 8999:1) for requests made in the same second - autoincrement is guaranteed as long as you take a couple of precautions... you control the db, so you can set autoincrement steps to ensure no clashes across your tables; or use table prefix values – Mark Baker Sep 16 '13 at 15:02
  • Thanks @MarkBaker I guess in many ways there's no real guarantee with any system that doesn't use extensive precautions. I think I'll stick with the system I have and increase the value of the rand() to make a string of 9 numbers to be safe – user2737457 Sep 16 '13 at 15:06
  • possible duplicate: https://stackoverflow.com/questions/1467581/how-to-generate-unique-id-in-mysql – Deke Sep 25 '19 at 22:17

2 Answers2

2

I would increase uniqueness by using

  • microtime() instead of time()

  • if strings are allowed, appending microtime with uniqid() function

  • and using mt_rand with larger values (mt_rand(0, 10000000))

http://php.net/manual/en/function.uniqid.php

http://fi2.php.net/microtime

iiro
  • 3,132
  • 1
  • 19
  • 22
  • That's `uniqid` and not `uniqueid`, which I believe is what you meant. http://php.net/manual/en/function.uniqid.php - which I noticed on the same page. I believe that was an error from the person who posted it on PHP.net – Funk Forty Niner Sep 16 '13 at 14:49
-1

Use autoincrement as follows:

CREATE TABLE tablename(
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     col1 CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

Source: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

JSK NS
  • 3,346
  • 2
  • 25
  • 42