Jump to content
  • Advertisement
Sign in to follow this  
GCoda

[web] unique ID's in mySql fields?

This topic is 5052 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hey, I need to query mySql table in order to find the next free ID for new entry. The ID would be just a field called ID and every entry in the table has its own ID. But the problem is that how can you know wich ID isn't used yet if you want to add new entry? I could do this query: SELECT UniqueID FROM SomeTable WHERE UniqueID = someNumber And if someNumber is used I'd just query for next ID until nothing is selected and the free ID is found. But that seems to be way too slow option. So any better ideas? Thanks for your time.

Share this post


Link to post
Share on other sites
Advertisement
You could just use the AUTO_INCREMENT field. Sure, if you delete an entry you'll have 'gaps' but you'll always get a unique ID.


CREATE TABLE test (

MyKey int AUTO_INCREMENT,
MyData varchar(50),
// etc

PRIMARY KEY (MyKey)
);

Share this post


Link to post
Share on other sites
Quote:
Original post by evolutional
You could just use the AUTO_INCREMENT field. Sure, if you delete an entry you'll have 'gaps' but you'll always get a unique ID.


Thanks evolutional. But I'm afraid that some deletion is needed so the gaps are problem.

Unless the increment wraps to zero (or first lowest) when it reaches the maxium?
And do you know what is the maxium?

Share this post


Link to post
Share on other sites
If you only need the IDs to be unique, the gaps should not be a problem.

The maximum value will depend on the column type you use for the AUTO_INCREMENT field (e.g. 2147483647 for int). IIRC, the counter will not wrap around if you hit this limit.

Share this post


Link to post
Share on other sites
An alternative is to use the aggregate function MAX. You simply run the query "SELECT MAX(ColumnName) FROM TableName". This will return the maximum value found in the given column.

Also, most DB programmers I know avoid using AUTO_INCREMENT datatypes. MySQL might be different, but I know in MS Sequel Server, they can get currupt and are very difficult to fix if they do.

Share this post


Link to post
Share on other sites
Every time you delete an entry you could store the ID number in a separate table, when it comes to adding a new record simply look for the lowest ID in the table - if the table is empty use the auto_increment field's default value. That *should* work and I don't think it'd have that much overhead - it just meas you'll have an extra step before deleting an item and adding a new one.

Share this post


Link to post
Share on other sites
Quote:
Original post by evolutional
Every time you delete an entry you could store the ID number in a separate table, when it comes to adding a new record simply look for the lowest ID in the table - if the table is empty use the auto_increment field's default value. That *should* work and I don't think it'd have that much overhead - it just meas you'll have an extra step before deleting an item and adding a new one.


Good idea, never thought about that.

Share this post


Link to post
Share on other sites
Quote:
Original post by evolutional
You could just use the AUTO_INCREMENT field. Sure, if you delete an entry you'll have 'gaps' but you'll always get a unique ID.


CREATE TABLE test (

MyKey int AUTO_INCREMENT,
MyData varchar(50),
// etc

PRIMARY KEY (MyKey)
);


this is probably what I'd do. I'm no expert, but I haven't heard about any problems with MySQL in regards to the auto increment feature, and if you use the bigint type (instead of just int), unsigned, you have values from 0 to 18,446,744,073,709,551,615. I believe that's enough room for gaps, at least on MOST systems.

Share this post


Link to post
Share on other sites
Experienced developers realise that these auto_increment / identity features are there for a reason.

As long as you know how to use it properly, use the database's native ID generator. It will give you an acceptable answer. Specificially, it's GUARANTEED to be unique (at the time at which it creates it).

There are no problems with MSSQL and unique IDs, IF YOU USE THEM RIGHT. The problem is that lots of lame programmers just don't get it.

In MySQL you can use the API to retrieve the unique ID after an insert. In MSSQL you can too, but mostly people use stuff like ADO which makes that difficult in some respects. Instead they select @@IDENTITY after an insert.

That is fine, as long as nothing else is allowed to happen to that connection in between the insert and the select @@IDENTITY. Specifically, if another insert happens, you get the wrong one. If you're using connection pooling (which you usually are in ADO unless you specifically aim not to), you can't guarantee that a single connection "object" won't actually be several connections to the server.

SO the obvious answer is to use a query batch, and put SELECT @@IDENTITY immediately after the insert. This combines them into a single operation, nothing can possibly get "in between" (on that connection anyway).

Mark

PS: If you're using replication, find another solution. However, you will have other issues too.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!