I want to know the next value of auto increment field I wanted to test this :
select max(contactid) from contact
and I add 1 but I realized that it can give me an error for exemple if I insert one record and I delete it so if I insert after the field will increase by two
How can I achieve that?
There are multiple solutions to this problem:
1. (Preferable) Stop trying to predict auto-increment values
This is the more typical case, and basically is using auto-increment as designed. This assumes that you don't actually need the auto-increment value before you insert. What you can do is:
DROP TABLE IF EXISTS t;
CREATE TABLE t (id INT UNSIGNED NOT NULL auto_increment, x INT NOT NULL, PRIMARY KEY(id));
INSERT INTO t (x) VALUES (100);
SELECT LAST_INSERT_ID();
The call to SELECT LAST_INSERT_ID() will return the ID that was just generated for your INSERT.
2. Set up an ID generation table specifically to generate IDs
You can create a table with just an auto-increment column, like so:
DROP TABLE IF EXISTS id_generator;
CREATE TABLE id_generator (id INT UNSIGNED NOT NULL auto_increment, PRIMARY KEY(id));
You can then generate a new, unique ID with:
INSERT INTO id_generator (id) VALUES (NULL);
SELECT LAST_INSERT_ID();
And use that ID to insert into the table you're actually working with. As long as all generated IDs come from this ID generation table, there will be no conflicts. However there is a cost to generating these IDs, and auto-increment is not very efficient at it.
3. Use an external ID generation scheme
This is more or less similar to solution 2, but doesn't use MySQL at all for the ID generation. You can use something like a UUID/GUID scheme which generates a string, or you could use something like Snowflake to generate integer IDs.