How to get the next auto-increment id in mysql

Question

How to get the next id in mysql to insert it in the table

INSERT INTO payments (date, item, method, payment_code)
VALUES (NOW(), '1 Month', 'paypal', CONCAT("sahf4d2fdd45", id))

Answer 1

You can use

SET information_schema_stats_expiry = 0;
SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'table_name'
AND table_schema = DATABASE( );

The default information_schema_stats_expiry value is 86400 so without the first statement (only needed once per session) this method can return stale data.

Alternatively, you can use this query and the value will be in the Auto_increment column but other additional data is also returned:

SHOW TABLE STATUS LIKE 'table_name';

Answer 2

SELECT AUTO_INCREMENT AS next_id FROM information_schema.tables WHERE table_name = 'table name' AND table_schema = 'database name of table name'

Answer 3

For MySQL 8 use SHOW CREATE TABLE to retrieve the next autoincrement insert id:

SHOW CREATE TABLE mysql.time_zone

Result:

CREATE TABLE `time_zone` (
  `Time_zone_id` int unsigned NOT NULL AUTO_INCREMENT,
  `Use_leap_seconds` enum('Y','N') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Time_zone_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1784 DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC COMMENT='Time zones'

See the AUTO_INCREMENT=1784 at the last line of returned query.

Compare with the last value inserted: select max(Time_zone_id) from mysql.time_zone

Result:

+-------------------+
| max(Time_zone_id) |
+-------------------+
|              1783 |
+-------------------+

Tested on MySQL v8.0.20.

Answer 4

If return no correct AUTO_INCREMENT, try it:

ANALYZE TABLE `my_table`;
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE (TABLE_NAME = 'my_table');

This clear cache for table, in BD

Answer 5

This will return auto increment value for the MySQL database and I didn't check with other databases. Please note that if you are using any other database, the query syntax may be different.

SELECT AUTO_INCREMENT 
FROM information_schema.tables
WHERE table_name = 'your_table_name'
     and table_schema = 'your_database_name';

SELECT AUTO_INCREMENT 
FROM information_schema.tables
WHERE table_name = 'your_table_name'
     and table_schema = database();

Answer 6

For me it works, and looks simple:

 $auto_inc_db = mysql_query("SELECT * FROM my_table_name  ORDER BY  id  ASC ");
 while($auto_inc_result = mysql_fetch_array($auto_inc_db))
 {
 $last_id = $auto_inc_result['id'];
 }
 $next_id = ($last_id+1);


 echo $next_id;//this is the new id, if auto increment is on

Answer 7

You can get the next auto-increment value by doing:

SHOW TABLE STATUS FROM tablename LIKE Auto_increment
/*or*/
SELECT `auto_increment` FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = 'tablename'

Note that you should not use this to alter the table, use an auto_increment column to do that automatically instead.
The problem is that last_insert_id() is retrospective and can thus be guaranteed within the current connection.
This baby is prospective and is therefore not unique per connection and cannot be relied upon.
Only in a single connection database would it work, but single connection databases today have a habit of becoming multiple connection databases tomorrow.

See: SHOW TABLE STATUS

Answer 8

Improvement of @ravi404, in case your autoincrement offset IS NOT 1 :

SELECT (`auto_increment`-1) + IFNULL(@@auto_increment_offset,1) 
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name = your_table_name
AND table_schema = DATABASE( );

(auto_increment-1) : db engine seems to alwaus consider an offset of 1. So you need to ditch this assumption, then add the optional value of @@auto_increment_offset, or default to 1 : IFNULL(@@auto_increment_offset,1)

Answer 9

using the answer of ravi404:

CREATE FUNCTION `getAutoincrementalNextVal`(`TableName` VARCHAR(50))
    RETURNS BIGINT
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

    DECLARE Value BIGINT;

    SELECT
        AUTO_INCREMENT INTO Value
    FROM
        information_schema.tables
    WHERE
        table_name = TableName AND
        table_schema = DATABASE();

    RETURN Value;

END

using in your insert query, to create a SHA1 Hash. ex.:

INSERT INTO
    document (Code, Title, Body)
VALUES (                
    sha1( getAutoincrementalNextval ('document') ),
    'Title',
    'Body'
);

Answer 10

Simple query would do SHOW TABLE STATUS LIKE 'table_name'

Answer 11

SELECT id FROM `table` ORDER BY id DESC LIMIT 1

Although I doubt in its productiveness but it's 100% reliable

Answer 12

mysql_insert_id();

That's it :)

Answer 13

use "mysql_insert_id()". mysql_insert_id() acts on the last performed query, be sure to call mysql_insert_id() immediately after the query that generates the value.

Below are the example of use:

<?php
    $link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('mydb');

mysql_query("INSERT INTO mytable  VALUES('','value')");
printf("Last inserted record has id %d\n", mysql_insert_id());
    ?>

I hope above example is useful.

Answer 14

I suggest to rethink what you are doing. I never experienced one single use case where that special knowledge is required. The next id is a very special implementation detail and I wouldn't count on getting it is ACID safe.

Make one simple transaction which updates your inserted row with the last id:

BEGIN;

INSERT INTO payments (date, item, method)
     VALUES (NOW(), '1 Month', 'paypal');

UPDATE payments SET payment_code = CONCAT("sahf4d2fdd45", LAST_INSERT_ID())
     WHERE id = LAST_INSERT_ID();

COMMIT;

Answer 15

The top answer uses PHP MySQL_ for a solution, thought I would share an updated PHP MySQLi_ solution for achieving this. There is no error output in this exmaple!

$db = new mysqli('localhost', 'user', 'pass', 'database');
$sql = "SHOW TABLE STATUS LIKE 'table'";
$result=$db->query($sql);
$row = $result->fetch_assoc();

echo $row['Auto_increment'];

Kicks out the next Auto increment coming up in a table.

Answer 16

In PHP you can try this:

$query = mysql_query("SELECT MAX(id) FROM `your_table_name`");
$results = mysql_fetch_array($query);
$cur_auto_id = $results['MAX(id)'] + 1;

OR

$result = mysql_query("SHOW TABLE STATUS WHERE `Name` = 'your_table_name'");
$data = mysql_fetch_assoc($result);
$next_increment = $data['Auto_increment'];

Answer 17

You have to connect to MySQL and select a database before you can do this

$table_name = "myTable"; 
$query = mysql_query("SHOW TABLE STATUS WHERE name='$table_name'"); 
$row = mysql_fetch_array($query); 
$next_inc_value = $row["AUTO_INCREMENT"];  

Answer 18

What do you need the next incremental ID for?

MySQL only allows one auto-increment field per table and it must also be the primary key to guarantee uniqueness.

Note that when you get the next insert ID it may not be available when you use it since the value you have is only within the scope of that transaction. Therefore depending on the load on your database, that value may be already used by the time the next request comes in.

I would suggest that you review your design to ensure that you do not need to know which auto-increment value to assign next

Answer 19

Solution:

CREATE TRIGGER `IdTrigger` BEFORE INSERT ON `payments`
  FOR EACH ROW
BEGIN

SELECT  AUTO_INCREMENT Into @xId
    FROM information_schema.tables
    WHERE 
    Table_SCHEMA ="DataBaseName" AND
    table_name = "payments";

SET NEW.`payment_code` = CONCAT("sahf4d2fdd45",@xId);

END;

"DataBaseName" is the name of our Data Base

Answer 20

You can't use the ID while inserting, neither do you need it. MySQL does not even know the ID when you are inserting that record. You could just save "sahf4d2fdd45" in the payment_code table and use id and payment_code later on.

If you really need your payment_code to have the ID in it then UPDATE the row after the insert to add the ID.

Answer 21

Use LAST_INSERT_ID() from your SQL query.

Or

You can also use mysql_insert_id() to get it using PHP.