Sign in to follow this  

[web] unique ID's in mySql fields?

This topic is 4746 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
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
Quote:
Original post by nimrand
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.


Could you give me some linkage on that? I have a MySQL database with 30 tables or so that all use AUTO_INCREMENT for their unique ID's. I need to move this database to MS-SQL in two weeks or so, so I would definately like to look up this snag.

Share this post


Link to post
Share on other sites

This topic is 4746 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.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this