SELECT last id without INSERT

Question

I m trying to retrieve the id of one table A to insert into another table B  I cannot use last insert id   as i have not inserted anything into A  Any ideas on how to do this nicely    n   mysql query  SELECT max id  FROM tablename    doesn t seem to work  nor does   n   mysql query  SELECT max id  FROM tablename GROUP BY id

User · Answer

You can get maximum column value and increment it:

InnoDB uses the following algorithm to initialize the auto-increment counter for a table t that contains an AUTO_INCREMENT column named ai_col: After a server startup, for the first insert into a table t, InnoDB executes the equivalent of this statement:
SELECT MAX(ai_col) FROM t FOR UPDATE;
InnoDB increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. If the table is empty, InnoDB uses the value 1.

Also you can use SHOW TABLE STATUS and its "Auto_increment" value.

User · Answer

You could descendingly order the tabele by id and limit the number of results to one   SELECT id FROM tablename ORDER BY id DESC LIMIT 1   BUT  ORDER BY rearranges the entire table for this request  So if you have a lot of data and you need to repeat this operation several times  I would not recommend this solution

User · Answer

I think to add timestamp to every record and get the latest  In this situation you can get any ids  pack rows and other ops

User · Answer

I have different solution   SELECT AUTO INCREMENT - 1 as CurrentId FROM INFORMATION SCHEMA TABLES WHERE TABLE SCHEMA    dbname  AND TABLE NAME    tablename

User · Answer

In MySQL  this does return the highest value from the id column   SELECT MAX id  FROM tablename    However  this does not put that id into  n    n   mysql query  SELECT max id  FROM tablename      To get the value  you need to do this    result   mysql query  SELECT max id  FROM tablename     if    result        die  Could not query     mysql error         id   mysql result  result  0   id      If you want to get the last insert ID from A  and insert it into B  you can do it with one command   INSERT INTO B  col  SELECT MAX id  FROM A

[mysql] SELECT last id, without INSERT

Examples related to mysql

Examples related to lastinsertid