MySql "nextval"

[rsassine]

I want to simulate this Oracle statement:
select sequence_name.nextval from dual;
in MySql.

Any idea on how to do that?

(The idea here is for performance issue. I do NOT to use a MySql INSERT statement into a table and then pull it right back).

Thanks,
Raymond

[Anon]

The only foolproof way of getting the next autoincrement value is to do an insert and then use last_insert_id() to retrieve the value.

[Anon]

select MAX(record_id) from datatable;

[Anon]

Yes, that will tell you what the maximum id is. If you add 1 to it, you will get the next id value.

But is that what you really want? That tells you what the next record id will be, regardless of who or how it gets inserted. There is no GOOD and RELIABLE way to do what you want to do. The best you can do is INSERT then get the last insert id.

[[deleted]]

Indeed, the point is that MySQL does have a way of telling you what the next value is going to be, but it does not reserve that value they way Oracle (and postgreSQL) do with nextval().

In MySQL, you can only get the value that will be used for the next insert in that table, not 'per connection'. Everyone who requests that value in that table will get the same answer, regardless of how many others have requested it.