How to get the next auto-increment id in MySQL?

MySQL has the AUTO_INCREMENT keyword to perform auto-increment. The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record.

To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT.

Creating a table, with "d" as auto-increment.

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> create table </span><span class="typ">NextIdDemo</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> </span><span class="pun">(</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> id </span><span class="kwd">int</span><span class="pln"> auto_increment</span><span class="pun">,</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> primary key</span><span class="pun">(</span><span class="pln">id</span><span class="pun">)</span><span class="pln">
   </span><span class="pun">-></span><span class="pln"> </span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">0</span><span class="pln"> rows affected </span><span class="pun">(</span><span class="lit">1.31</span><span class="pln"> sec</span><span class="pun">)</span>

Inserting records into the table.

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">NextIdDemo</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">1</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.22</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">

mysql</span><span class="pun">></span><span class="pln">  insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">NextIdDemo</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">2</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.20</span><span class="pln"> sec</span><span class="pun">)</span><span class="pln">

mysql</span><span class="pun">></span><span class="pln">  insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">NextIdDemo</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">3</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Query</span><span class="pln"> OK</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> row affected </span><span class="pun">(</span><span class="lit">0.14</span><span class="pln"> sec</span><span class="pun">)</span>

To display all the records.

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> </span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="kwd">from</span><span class="pln"> </span><span class="typ">NextIdDemo</span><span class="pun">;</span>

The following is the output.

+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.04 sec)

We have inserted 3 records above. Therefore, the next id must be 4.

The following is the syntax to know the next id.

<span class="pln">SELECT AUTO_INCREMENT
FROM information_schema</span><span class="pun">.</span><span class="pln">TABLES
WHERE TABLE_SCHEMA </span><span class="pun">=</span><span class="pln"> </span><span class="str">"yourDatabaseName"</span><span class="pln">
AND TABLE_NAME </span><span class="pun">=</span><span class="pln"> </span><span class="str">"yourTableName"</span>

The following is the query.

<span class="pln">mysql</span><span class="pun">></span><span class="pln"> SELECT AUTO_INCREMENT
    </span><span class="pun">-></span><span class="pln"> FROM information_schema</span><span class="pun">.</span><span class="pln">TABLES
    </span><span class="pun">-></span><span class="pln"> WHERE TABLE_SCHEMA </span><span class="pun">=</span><span class="pln"> </span><span class="str">"business"</span><span class="pln">
    </span><span class="pun">-></span><span class="pln"> AND TABLE_NAME </span><span class="pun">=</span><span class="pln"> </span><span class="str">"NextIdDemo"</span><span class="pun">;</span>

Here is the output that displays the next auto-increment.

+----------------+
| AUTO_INCREMENT |
+----------------+
|              4 |
+----------------+
1 row in set (0.25 sec)