[web] unique ID's in mySql fields?
Author
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.
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)
);
CREATE TABLE test (
MyKey int AUTO_INCREMENT,
MyData varchar(50),
// etc
PRIMARY KEY (MyKey)
);
Author
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?
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.
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.
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.
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.
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.
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.
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.
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.
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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
