Last insert id ?!?

Hi,
consider a situation which we have a table in mysql (ex. innodb storage engine, I mention it 'cause I thought locking may be useful ) with the following fields :

id int not_null auto_increment primary key
field_x varchar(20) not null

we define id as primary key and field_x as unique key

AND also for some reasons, we want to have values in our table with the help of insert command with the following style :

id field_x
1 cod1
2 cod2

in fact, we want to get the current auto increment value in each row, add it to some string and place it to the current row with the same insert command,

the only way that I've think about it is that we perform a pre select query , which returns the latest auto increment value , add it by 1 and then place it in the main insert command
but as you know, this may cause serious problems when multiple persons execute your program at the same time and the returned value of the latest inserted record plus one is not trustable for insertion,

any helps would be appreciated,

how about using update get your id value and append concat it to your str for the value of field_x

I think you have said it yourself - you cannot rely on next auto incr value being 1 higher than the current highest and you run into the mult-update issue you refer to. I think you will have to do an INSERT, and then use last_insert_id() as part of a follow on UPDATE command, unless a guru (like Mr 937) knows different?

how about not appending the auto_increment to the field

that's an even better solution

He speaks!!!

On that point, if your field is ALWAYS going to be 'literal' plus auto incr value, why not SELECT CONCAT(field_x,id) as afield ?

as you see, I said field_x is not null and it's also defined as unique.so the question is this : which value should we insert into that field at the insert time ? how can we make sure that the value that we entered is unique that in the next level we get the real previous auto increment value and perform the update ?



I don't think so buddy



first question : as mentioned above, how can we meke sure that the first value for field_x with insert command is unique ? what should we insert in this time ?
second question: user a insert a row , user b insert a row after user a, but for some reasons, the second query of the user b runs faster than second query of user a !!! (something like syncronization problems ; ) ) so ... ?!!!



sorry dude, all situations in this problem is mandatory ! unfortunately , we don't have what if and what about in the real life



sorry, I didn't undrestand what you mean sir ? !!

thanks all for your attention ... any Ideas ?

okay, sure

but you said the field was unique

so why do you want to append a number onto the end of it? are you trying to make it "more unique" ??

no sir ! it was just an example for a bigger problem in the real life, It's too hard to explain the situation in the real project, but we can model it exactly like the problem I said !

okay, then my answer is too hard to explain in the real situation



but you can model it exactly like the problem you posted -- namely, do not append the auto_increment onto an already unique field value

OK !!!!
so, what about the real problem :
consider a web-based user management system , ...
users can register and in this case automatically get a code (for some reasons, it cannot be the exact auto_increment id and it cannot be null, and probably it should have something like COD at the first )
the system administrator also can create users in his control panel, but he/she must explicitly enter the user's code (it canot contain term COD in it)

then my friend, you may ask when we have a real unique auto increment column, then why we need this code ? the answer is : I am a programmer in this project and not a db designer and I'm forced to do what I have told , and also this project is developing for a company which each employee has a code and also they want to integrate registered users and users that admin creates in one table , and separate them with their role (defined in another table) and also their code ,,,

I would appreciate if you model your help in the real problem !!!

"the system administrator also can create users in his control panel, but he/she must explicitly enter the user's code"

how does he/she know what code to assign?

the solution is to acknowledge what you said --

1. each employee has a code
2. they want to integrate registered users
3. they want to integrate users that admin creates
4. and separate them with their role

so as long as you assign a unique value, and use a proper role, you're fine

easy, see?

by company rules, sir ... I don't know ! but they want this feature !
---
yeah , each user has a role , such as system administrator, registered user, saler manager , etc ... but it's different from their code , for example saler manager may have code : 13243434 based on the company rules ! but system managers has the role no 1, salers with role no 2, (and also one person may have multiple roles but just one company code , in fact, forget role issue dear !)


easy , but unfortunately impossible !! thanks anyway for your attention sir,
any ideas ?