currently I'm using uniqid("") as a primary key in a mySQL-DB. I need to create this unique ID on the PHP side and I'm not able to to use AUTO_INCREMENT on the mySQL side.
With uniqid("") I'm getting a 13 character long hex-string which I store in a VARCHAR(13).
I'd like to optimize database space and use an INT or FLOAT instead. What's the best way to go if I want to make sure the ID is 100% unique and at the same time don't want to waste extra bytes for a huge ID?
thanks
"and I'm not able to to use AUTO_INCREMENT on the mySQL side"
Why not?
"What's the best way to go if I want to make sure the ID is 100% unique and at the same time don't want to waste extra bytes for a huge ID?"
You can't make a unique ID, you can only create an id and check wether it is already in use.
The 'best' but still horribly wrong method is to fake the auto_increment.
Lock the table exclusively (this is the bad part, nobody else can do anything with the table untill you are finished)
Select the highest ID value + 1:
SELECT MAX(id)+1 AS newid FROM table;
Use that new id as the ID value for the next insert.
Note: You cannot unlock the table untill you have succesfully inserted the new value, otherwise other copies of the script might get the same value and try to insert a duplicate value.
So if you do a lot of inserts, this can/will have a serious impact on the speed of your database.
But I'm really curious about why you can't use auto_increment...
A forum, a FAQ, what else do you need?
But I'm really curious about why you can't use auto_increment...
sure I could... BUT...
I don't want to waste any single extra query, that's why. I need to get the ID from the current entry as I use it in other places in my PHP code. So I would be forced to make another SELECT just to get the ID back and this on a huge table with high load. Do you get my point?
thanks anyway for your idea. what about microtime?
"Do you get my point?"
No, because there is no extra query.
The new auto_increment value is returned automatically (nothing you can do about it) through mysql_insert_id().
Anyway, if you create your own 'unique' values you're going to waste one heck of a lot more resources. You must lock the table to make sure nobody else can generate a new number while you're generating it, and that is a lot worse than doing one extra query to get a value from a record that you just created (and is therefore still in cache, no disk-seek required)
Microtime won't do, you still have to lock the table. You simply cannot create a truely unique value without locking the table.
Let the database take care of database things, use auto_increment.
A forum, a FAQ, what else do you need?
mysql_insert_id().
hey Vincent, thanks! I didn't think about that. That definitely is the cleanest solution!
In mySQL this is really easy. I need to get this to work also on Postgres and M$SQL where it's slightly more complicated to get the last insert id.
take care