Description\n-----------\n\nHow do I get the last inserted auto_increment value?\n\nUse the LAST_INSERT_ID() function:\n\n``sql\nSELECT LAST_INSERT_ID();\n`\n\nWhat if someone else inserts before I select my id?\n\nLAST_INSERT_ID() is connection specific, so there is no problem from race conditions.\n\nHow do I get the next value to be inserted?\n\nYou don't. Insert, then find out what you did with LAST_INSERT_ID().\n\nHow do I change what number auto_increment starts with?\n\nRun a query, ALTER TABLE yourTable AUTO_INCREMENT = x; . The next insert will contain x or MAX(autoField) + 1, whichever is higher.\n\nAs an alternative, run INSERT INTO yourTable (autoField) VALUES (x); . The next insert will contain x+1 or MAX(autoField) + 1, whichever is higher.\n\nIssuing TRUNCATE TABLE will delete all the rows in the table, and will reset the auto_increment value to 0 in most cases.\n\nHow do I renumber rows once I deleted some in the middle?\n\nTypically, you don't want to. Gaps are hardly ever a problem; if your application can't handle gaps in the sequence, you probably should rethink your application.\n\nCan I do group-wise auto_increment?\n\nYes, if you use the MyISAM engine.\n\nHow do I get the AUTO_INCREMENT value in a BEFORE INSERT trigger?\n\nThis isn't possible. It's only available after insert.\n\nHow do I assign two fields the same auto_increment value in one query?\n\nYou can't, not even with an AFTER INSERT trigger. Insert, then go back and update using LAST_INSERT_ID(). Those two statements could be wrapped into one stored procedure if you wish.\n\nHowever, you can mimic this behavior with a BEFORE INSERT trigger and a second table to store the sequence position:\n\n`sql\nCREATE TABLE sequence (table_name VARCHAR(255), position INT UNSIGNED);\nINSERT INTO sequence VALUES ('testTable', 0);\nCREATE TABLE testTable (firstAuto INT UNSIGNED, secondAuto INT UNSIGNED);\nDELIMITER //\nCREATE TRIGGER testTable_BI BEFORE INSERT ON testTable FOR EACH ROW BEGIN\n  UPDATE sequence SET position = LAST_INSERT_ID(position + 1) WHERE table_name = 'testTable';\n  SET NEW.firstAuto = LAST_INSERT_ID();\n  SET NEW.secondAuto = LAST_INSERT_ID();\nEND//\nDELIMITER ;\nINSERT INTO testTable VALUES (NULL, NULL), (NULL, NULL);\nSELECT  FROM testTable;\n\n+-----------+------------+\n| firstAuto | secondAuto |\n+-----------+------------+\n|         1 |          1 |\n|         2 |          2 |\n+-----------+------------+\n`\n\nThe same sequence table can maintain separate sequences for multiple tables (or separate sequences for different fields in the same table) by adding extra rows.\n\nDoes the auto_increment field have to be primary key?\n\nNo, it only has to be indexed. It doesn't even have to be unique.\n\nHow does InnoDB handle AUTO_INCREMENT?\n\nSee AUTO_INCREMENT handling in InnoDB.\n\nGeneral Information To Read\n\nAUTO_INCREMENT\n\nManual Notes\n\nThere can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers wrap over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0.\n\nHow to start a table with a set AUTO_INCREMENT value?\n\n`sql\nCREATE TABLE autoinc_test (\n  h INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, \n  m INT UNSIGNED \n) AUTO_INCREMENT = 100;\n\nINSERT INTO autoinc_test ( m ) VALUES ( 1 );\n\nSELECT  FROM autoinc_test;\n+-----+------+\n| h   | m    |\n+-----+------+\n| 100 |    1 |\n+-----+------+\n``\n\nURL: https://mariadb.com/docs/server/reference/data-types/auto_increment-faq