# AUTO\_INCREMENT FAQ

## How do I get the last inserted auto\_increment value?

Use the [LAST\_INSERT\_ID()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/last_insert_id) function:

```sql
SELECT LAST_INSERT_ID();
```

## What if someone else inserts before I select my id?

[LAST\_INSERT\_ID()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/last_insert_id) is connection specific, so there is no problem from race conditions.

## How do I get the next value to be inserted?

You don't. Insert, then find out what you did with [LAST\_INSERT\_ID()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/last_insert_id).

## How do I change what number auto\_increment starts with?

Run a query, `ALTER TABLE yourTable AUTO_INCREMENT = x;` . The next insert will contain `x` or `MAX(autoField) + 1`, whichever is higher.

As an alternative, run `INSERT INTO yourTable (autoField) VALUES (x);` . The next insert will contain `x+1` or `MAX(autoField) + 1`, whichever is higher.

Issuing [TRUNCATE TABLE](https://mariadb.com/docs/server/reference/sql-statements/table-statements/truncate-table) will delete all the rows in the table, and will reset the auto\_increment value to 0 in most cases.

## How do I renumber rows once I deleted some in the middle?

Typically, 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.

## Can I do group-wise auto\_increment?

Yes, if you use the [MyISAM engine](https://mariadb.com/docs/server/server-usage/storage-engines/myisam-storage-engine).

## How do I get the AUTO\_INCREMENT value in a BEFORE INSERT trigger?

This isn't possible. It's only available after insert.

## How do I assign two fields the same auto\_increment value in one query?

You 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.

However, you can mimic this behavior with a `BEFORE INSERT` trigger and a second table to store the sequence position:

```sql
CREATE TABLE sequence (table_name VARCHAR(255), position INT UNSIGNED);
INSERT INTO sequence VALUES ('testTable', 0);
CREATE TABLE testTable (firstAuto INT UNSIGNED, secondAuto INT UNSIGNED);
DELIMITER //
CREATE TRIGGER testTable_BI BEFORE INSERT ON testTable FOR EACH ROW BEGIN
  UPDATE sequence SET position = LAST_INSERT_ID(position + 1) WHERE table_name = 'testTable';
  SET NEW.firstAuto = LAST_INSERT_ID();
  SET NEW.secondAuto = LAST_INSERT_ID();
END//
DELIMITER ;
INSERT INTO testTable VALUES (NULL, NULL), (NULL, NULL);
SELECT * FROM testTable;

+-----------+------------+
| firstAuto | secondAuto |
+-----------+------------+
|         1 |          1 |
|         2 |          2 |
+-----------+------------+
```

The same sequence table can maintain separate sequences for multiple tables (or separate sequences for different fields in the same table) by adding extra rows.

## Does the auto\_increment field have to be primary key?

No, it only has to be indexed. It doesn't even have to be unique.

## How does InnoDB handle AUTO\_INCREMENT?

See [AUTO\_INCREMENT handling in InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/auto_increment-handling-in-innodb).

## General Information To Read

[AUTO\_INCREMENT](https://mariadb.com/docs/server/reference/data-types/auto_increment)

## Manual Notes

There 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.

## How to start a table with a set AUTO\_INCREMENT value?

```sql
CREATE TABLE autoinc_test (
  h INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, 
  m INT UNSIGNED 
) AUTO_INCREMENT = 100;

INSERT INTO autoinc_test ( m ) VALUES ( 1 );

SELECT * FROM autoinc_test;
+-----+------+
| h   | m    |
+-----+------+
| 100 |    1 |
+-----+------+
```

## See Also

* [AUTO\_INCREMENT](https://mariadb.com/docs/server/reference/data-types/auto_increment)
* [AUTO\_INCREMENT handling in XtraDB/InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/auto_increment-handling-in-innodb)
* [LAST\_INSERT\_ID()](https://mariadb.com/docs/server/reference/sql-functions/secondary-functions/information-functions/last_insert_id)
* [BLACKHOLE and AUTO\_INCREMENT](https://mariadb.com/docs/server/server-usage/storage-engines/blackhole#blackhole-and-auto_increment)
* [Sequences](https://mariadb.com/docs/server/reference/sql-structure/sequences) - an alternative to `AUTO_INCREMENT`

*The initial version of this article was copied, with permission, from* [*Autoincrement\_FAQ*](https://hashmysql.org/wiki/Autoincrement_FAQ) *on 2012-10-05.*

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/data-types/auto_increment-faq.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.