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

User · Answer

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

User · Answer

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    lt  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           gt    I hope above example is useful

User · Answer

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

User · Answer

Use LAST INSERT ID   from your SQL query   Or  You can also use mysql insert id   to get it using PHP

User · Answer

mysql insert id      That s it

User · Answer

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

User · Answer

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

User · Answer

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- gt query  sql    row    result- gt fetch assoc     echo  row  Auto increment      Kicks out the next Auto increment coming up in a table

User · Answer

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

User · Answer

SELECT id FROM  table  ORDER BY id DESC LIMIT 1   Although I doubt in its productiveness but it s 100  reliable

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Simple query would do SHOW TABLE STATUS LIKE  table name

User · Answer

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

User · Answer

You can use   SELECT AUTO INCREMENT FROM information schema tables WHERE table name    table name  AND table schema   DATABASE        or if you do not wish to use information schema you can use this  SHOW TABLE STATUS LIKE  table name

User · Answer

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

User · Answer

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

[mysql] How to get the next auto-increment id in mysql

Examples related to mysql

Examples related to sql