How I get next auto-increment ID in mysql table?

Question

I need to get next auto-increment ID from my mysql product Table.

This is how I tried it:

SELECT `auto_increment` 
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'products';

When this query is running, I can get an output like this.

+----------------+
| auto_increment |
+----------------+
|             10 |
|              1 |
|             14 |
+----------------+
3 rows in set (0.02 sec)

Can anybody tell me why it shows 3 values. (3 rows)

NOTE: my product table is still empty.

mysql> select * from products;
Empty set (0.00 sec)

Accessing the INFORMATION_SCHEMA is a very bad idea. Auto-increment is best left to the db to handle. If you need to maintain your keys then it would be advisable in using other field(s). — , Oct 10 '15 at 07:14

score 2 · Accepted Answer · edited May 23 '17 at 12:19

2

Run this query

SHOW TABLE STATUS LIKE 'products'

Using php

$result = mysql_query("
    SHOW TABLE STATUS LIKE 'products'
");
$data = mysql_fetch_assoc($result);
$next_increment = $data['Auto_increment'];

Using mysqli

$db = new mysqli('YOUR_HOST', 'DB_USERNAME', 'DB_PASSWORD', 'DATABASE');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

$sql = <<<SQL
SHOW TABLE STATUS LIKE 'products'
SQL;

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}

$row = $result->fetch_assoc();

echo $row['Auto_increment'];

Details https://stackoverflow.com/a/1372115

edited May 23 '17 at 12:19

Community

1
1

answered Oct 10 '15 at 07:07

ARIF MAHMUD RANA

5,026
3
31
58

Can we modify this query with mysqli prepared statements? – user3733831 Oct 10 '15 at 07:17
@user3733831 check now – ARIF MAHMUD RANA Oct 10 '15 at 07:28

score 2 · Answer 2 · answered Oct 10 '15 at 07:28

You didn't specify any database name in you query. You might have 'products' table in another database. If you don't use 'TRUNCATE' to make empty your table, the auto_increment id remains same as before. No matter that you have no data in you table.

Execute this query:

SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "databaseName" AND TABLE_NAME = "tableName"

It will help you to find out the next auto_increment id.

How I get next auto-increment ID in mysql table?

2 Answers2