Over at Jeremy Zawodny's blog he writes a response to an article by George Reese at O'Reilly's OnLamp.

Good stuff.. I just have one little correction..

Jeremy says:
Finally, he says "You cannot easily determine from an application what values MySQL has automatically generated." Why would you need to do that? If you code the application properly, that's not going to be an issue. Remember, this is an AUTO_INCREMENT column. It's supposed to be generated AUTOmatically. If your code is generating the values, don't tell MySQL that it should be an AUTO_INCREMENT column. It's as simple as that.

I think what George is thinking about is a function similar to Oracle's sequence.CURRVAL which is easily obtained by using this query:

SELECT LAST_INSERT_ID()

If he wants to duplicate Oracle's sequence.NEXTVAL, it can be really easily duplicated in MySQL by the use of an additional table.

Now this isn't the same as having the nice and easy Oracle function but it serves the same exact purpose and doesn't depend on code to generate the auto_increment value.

Let's say you want to know what is the next value that is generated on an AUTO_INCREMENT column (we'll call it SOME_KEY) on table SOME_TABLE

1. Create a table, let's call this one SOME_SEQQ:

CREATE TABLE SOME_SEQQ (
   QUERY VARCHAR(200)
);

2. In that table now store something like this:

INSERT INTO SOME_SEQQ VALUES ('SELECT MAX(SOME_KEY) FROM SOME_TABLE);

Now when you define SOME_TABLE, just define your SOME_KEY as you normally would an AUTO_INCREMENT column.. something like this, perhaps:

CREATE TABLE SOME_TABLE (
   SOME_KEY INT NOT NULL AUTO_INCREMENT,
   SOME_DATA VARCHAR(20) NULL
   PRIMARY KEY(SOME_KEY)
);

Now in your code, when you want to know what will be the next value generated by your AUTO_INCREMENT column, you just need to query your SOME_SEQQ and increment it. This is also better to query for the current value rather than using the built-in MySQL function as it is *more* fool-proof (if not completely) than that.

Obviously, this is not fool-proof but it's better than nothing and probably the easiest way to duplicate what Oracle does without the use of stored procedures.
Note: this might easily break if you have more than one application inserting data to the same table!

I've done a lot of work duplicating Oracle functionality for MySQL to preserve the flexibility of our code and w/o going database specific on our SQL (we support more than one database, obviously).