How to insert a record with a column values a autoincrement pk value?

I have the table like something like below, I use this to save the upload image name. The customer want the image to be {id}.{jpg|gif}.

create table imgae(
开发者_开发问答id int autoincrement,
image text
)

How can i insert a new record with only one sql.

You cannot do it with a single SQL statement in MySQL. If MySQL supported Oracle-style sequences, you would be able to do this in a single statement. But because auto_increment won't give you the next id until after the insert has taken place, you need to actually have the row inserted before having access to the id. The closest you can get in MySQL is this:

insert into imgae (image) values (null);
update imgae set image=concat(last_insert_id(), '.jpg') where id=last_insert_id();

BTW: I see your table is called imgae which is probably a typo. Did you mean to call it image? Also, when I tried to run your create table statement, it failed. I had to make a couple of changes to get it to work so I could validate my answer. It should look like this:

create table imgae(id int auto_increment primary key, image text);

INSERT INTO "imgae" VALUES("image") (CONCAT(LAST("id") + 1, /*Script code for fetching the extension/))

INSERT INTO imgae (image) VALUES (CONCAT(LAST_INSERT_ID() + 1, ".jpg"));

But, that doesn't actually name the image file. You'll have to make sure you do that after this is run using mysql_inser_id() to get that value.

It seems redundant to store the id in a column when the ID is already in another column. Why not keep the id the same, and just worry about storing the file extension? (assuming {id}.{jpg|gif} is all that's going in the column, and was grossly mis-represented as a text type). Then query them and concat the id and the extension columns to get a result.

...or am I missing something