Get Auto-Increment value that MySQL holds

[theScien]

Hi all, this one is killing me slowly, and I have read 2 full pages of hold threads on the Auto-Increment issue and nothing.

My problem is this: I have a database with a primary key which is also the auto_increment field, this works fine, but I need to know the auto_increment value that MySQL holds internally, the LAST_INSERT_ID() function is no good for my case since I need this value to be inserted and not after, also tried the "SELECT MAX(fld_HAM_ID) FROM tbl_HAM;" this gives the last assigned value, I thought great I just need to add 1 to this and it's done, but no, if some records are deleted at the end of the RS then it would give me value 13 I add 1 = 14 but the auto_increment is going to be 16, this is what I need to know, the increment value that MySQL holds internallly.

Thanks.

---------------------------------------------------------------------
If you haven't heard of it, then you most likely don't need it.

http://www.mylittlehost.com

---------------------------------------------------------------------

 

[KarveR]

Hehe this one pops up from time to time, let mysql do its thing, you cant (and or shouldn't) mess with auto_increment fields, they dont like it.

If you need to display sequectial numbers when displaying records, generate them outside of mysql.

Don't try to manage ID numbers on auto fields either, it wastes so much time and achieves .. well next to nothing apart from giving huge headaches.

Hope I haven't sounded too negative ..

//karv

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

you said you need to know the autonumber before the row is inserted and not after -- may i ask why?

rudy

http://r937.com/

SQL Consulting

 

[theScien]

First, let me thank both of you for answering.

I'm not trying to manage ID's with auto_increment fields, I need to generate a unique ID for an product list, and I will be using the ID-RND+DATETIME data for that, ID must be the same as the current row ID, that's why I need to know the next auto_increment value MySQL will use, before I insert the row.

I'm doing this at the moment by inserting everything first, then get the ID, and then update the UniqueID field only with the ID, but this is a 2 way process, if I can get the ID from the beginning before insertion it could be done in one go.

Thanks.

------------------------------

http://www.mylittlehost.com

------------------------------

 

[swampBoogie]

Store the ID, DATETIME and RND values in separate columns.

 

[BNPMike]

You do seem to be managing IDs with auto increment-fields, at least to me.

As a matter of interest - " ID must be the same as the current row ID" - why?

 

[theScien]

Storing the ID + DATETIME + RDN in seperate columns? Why would I want to do that? The inique identifier is like 623-643373020204105382 which equals to ID-RNDRNDddmmyyhhmmss.

Well yes, it's a different type of ID management than the one I thought you were refering to, but yes, I'm taking the ID from the auto increment column, this value is unique by nature so I can use it.

ID must be the same as the row ID so we can easily refer to a produt when someone gives us the unique identifier by just looking up the first numbers of it.

It's all working fine, but it's within my nature to always look for better ways to achieve something, and I think that we are making 2 DB calls when we could do everything with just 1, the question remains, can one know the next auto increment value MySQL will use without making an insert first?

Thanks.

------------------------------

http://www.mylittlehost.com

------------------------------

 

[r937]

i saw your explanation for getting the autonumber first, and i still don't understand it

what does "must be the same as the current row" mean?

at the time you are ready to do the INSERT, which row is the current row? do you mena the "latest" based on the autonumber? how would you know without doing a SELECT?

rudy

http://r937.com/

SQL Consulting

 

[theScien]

You raised an issue that I have not thought of, even if there's a way to know what I need, I would still need to make a DB call to get it, so I might leave it as it is.

What I mean is this, imagine you have a table with an auto increment column, this auto increment has 3 rows with auto values of 1,2,3 it previously had 5 rows, but you deleted rows 4 and 5, now, the next record will take a value of 6 isn't it, you know the next record will be 6 because you know you've deleted the others, what about if you didn't know that? What if someone else deleted those 2 records, how can you find out which value is next in line for the auto increment?

That was my question.

------------------------------

http://www.mylittlehost.com

------------------------------

 

[r937]

if you want to use an autonumber as part of your identifier (which is a very weak idea in the first place), then you could "construct" the identifier from separate stored values whenever you need to display it

another thing you could do is perform an update after the insert, to update the identifier to incorporate the current value of the autonumber





rudy

http://r937.com/

SQL Consulting

 

[theScien]

I'm doing exactly what you described in the second part of your message.

I'm using the autonumber as part of my identifier, I don't see how that can be weak, this value will never change in the autonumber and even if it does, the identifier is not linked in any way to the autonumber after it has been set, the reason I implemented it, is just as an easy way to spot/recognise a record, nothing more.

It appears no one knows how to find out which value is next in line for the auto increment column, MySQL keeps track of this alright, so why can't we get hold of this value?

------------------------------

http://www.mylittlehost.com

------------------------------

 

[BNPMike]

It seems people are not helping you by answering your original question, but the reason is this is generally a bad thing to do. If you are issuing new ids to records you really need to be in control of this yourself and not piggy-back on a special feature not intended for this purpose.

This is a generalised problem. Person A attempts to load a new record so gets a new id No 1. Person B meantime does the same and gets No 2. Person A however decides not to proceed or fails to complete for some reason. As a result there is an unassigned id. You seem to be happy with this but it is messy because you have now introduced gaps that serve no purpose at all apart from to confuse people. There is no audit trail for that missing id so how do you convince yourself something hasn't got lost somewhere.

Usually people maintain a separate table of ids and pick a new one from it. You can then lock it (for example) in order to control ids.

 

[KarveR]

the only way I can see of vreating the unique entries you mention is by runing an update on the table once a day or more :

UPDATE your_table SET unique_id=concat(ID,othervalue);

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[r937]

once a day or more?

make it "more" -- like, right after each insert



rudy

http://r937.com/

SQL Consulting

 

[KarveR]

..whatever, its simple enough to follow any insert with the update ;-)

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[theScien]

Yep, point taken, tanks everyone that contributed to this topic.

------------------------------

http://www.mylittlehost.com

------------------------------