How to get next auto increment id in mysql

Question

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

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

Answer 1

Use LAST_INSERT_ID() from your SQL query.

or

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

Answer 2

you can use

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

or if you do not want to use information_schema, you can use this

 SHOW TABLE STATUS LIKE 'table_name' 

Answer 3

You can get the following 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 modify the table, use the auto_increment column to do this automatically.
The problem is that last_insert_id() is retrospective and thus can be guaranteed in the current connection.
This child is promising and therefore not unique to every relationship, and cannot be relied on.
This will only work in one connection database, but today individual connection databases have the habit of turning into multiple connection databases tomorrow.

See: SHOW TABLE STATUS

Answer 4

The top answer uses PHP MySQL _ for the solution, I thought that I would share the updated PHP MySQLi _ solution to achieve this. There is no error output in this example!

 $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']; 

Causes the next automatic increment in the table.

Answer 5

In PHP you can try the following:

 $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 6

This will return the auto-increment value for the MySQL database, and I have not checked other databases. Please note that if you use 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 = database(); 

Answer 7

Decision:

 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 database

Answer 8

You can use mysql trigger

Answer 9

Simple query SHOW TABLE STATUS LIKE 'table_name'

Answer 10

You cannot use the identifier during insertion, and you do not need it. MySQL does not even know the identifier when you insert this record. You can just save "sahf4d2fdd45" in the payment_code table and use id and payment_code .

If you really need your billing code to have an identifier in it, then UPDATE the line after insertion to add the identifier.

Answer 11

What is the next incremental identifier for?

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

Please note that when you receive the following insert identifier, it may not be available when you use it, since the value you have is only within this transaction. Therefore, depending on the load on your database, this value can be used at the time the next request arrives.

I would advise you to review your design to make sure you don't need to know what auto-increment value the next one assigns.

Answer 12

I suggest rethinking what you are doing. I have never experienced one use case when special knowledge is required. The next identifier is a very specific implementation detail, and I won’t expect it to be safe for ACID.

Make one simple transaction that 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 13

You need 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 14

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

The following is an 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 the example above is useful.

Answer 15

 mysql_insert_id(); 

What is it:)

Answer 16

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

Although I doubt its productivity, it’s 100% reliable.

Answer 17

using ravi404's answer:

 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 inserts in your request to create the SHA1 hash. eg:.

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

Answer 18

Improvement @ ravi404 if auto-increment 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): it seems that the database engine always considers offset 1. Therefore, you need to abandon this assumption and then add the optional value @@ auto_increment_offset or the default value 1: IFNULL (@@ auto_increment_offset, 1)

Answer 19

This works for me, and it 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