Getting last auto increment value in database

how to get auto increment value in mysql after insert
mysql get auto increment value before insert
how to get last insert id in mysql stored procedure
get last auto increment value mysql
mysql auto increment start value
mysql get last insert id
mysql get auto_increment value
codeigniter get next auto increment id

I am trying to retrieve the auto increment value of last inserted data in mySQL. Here is my code:

public int getAutoIncrementProductID() {
    ResultSet rs = null;
    DBController db = new DBController();
    db.getConnection();
    int autoIncKeyFromFunc = -1;
    rs = db.readRequest("SELECT LAST_INSERT_ID()");
    try {
        if (rs.next()) {
            autoIncKeyFromFunc = rs.getInt(1);
            System.out.println("AUTO ID IS " + autoIncKeyFromFunc);
            rs.close();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    db.terminate();
    return autoIncKeyFromFunc;
}

However, these codes keep returning me 0 value although the auto increment column in database is keep increasing. It just wont get the auto increment value of last inserted data. Anybody could help?

You should use LAST_INSERT_ID() after you insert something.

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client. It is not even changed if you update another AUTO_INCREMENT column with a nonmagic value (that is, a value that is not NULL and not 0).

Source

You may also try

SELECT max(id) FROM tableName

But it will not suppose deleted rows.

Getting last auto increment value in database, You should use LAST_INSERT_ID() after you insert something. For LAST_INSERT_ID(), the most recently generated ID is maintained in the� The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment.

How about this?

SELECT your_id FROM your_table ORDER BY your_id DESC LIMIT 1

mysql get last auto increment value, (1) You could do it with looking at the last id in the table and incrementing it manually (but you won't be sure that the max(id)+1 is really the next� Obtaining the value of column that uses AUTO_INCREMENT after an INSERT statement can be achieved in a number of different ways. To obtain the value immediately after an INSERT, use a SELECT query with the LAST_INSERT_ID() function.

I think since you are using Jdbc there is another way to get generated key is to use API connection. createStatement (Statement.RETURN_GENERATED_KEYS); Look at this thread PreparedStatement with Statement.RETURN_GENERATED_KEYS

Get current AUTO_INCREMENT value for any table, You can get all of the table data by using this query: SHOW TABLE I believe you're looking for MySQL's LAST_INSERT_ID() function. If in the If the last INSERT statement inserted more than one row, this will give you the wrong answer. MySQL insert into translation (id) values (null); 21 = SELECT LAST_INSERT_ID();. For example, if the table has eight rows and you insert a new row without specifying the value for the auto-increment column, MySQL will automatically insert a new row with id value 9. Sometimes, you may need to reset the value of the auto-increment column so that the first record’s identity that you insert into the table starts from a

I use this:

SELECT auto_increment + 1 AS NEXT_ID
FROM   `information_schema`.`tables`
WHERE  table_name = "table_name"
       AND table_schema = "database_name"

How to get the next auto-increment id in MySQL?, MySQL has the AUTO_INCREMENT keyword to perform auto-increment. The starting value for AUTO_INCREMENT is 1, which is the default. Get ID of The Last Inserted Record. If we perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, we can get the ID of the last inserted/updated record immediately. In the table "MyGuests", the "id" column is an AUTO_INCREMENT field:

Be Careful when using mysql_insert_id() specially if you have multiple connections to the Database. Because It doesn't get the value of the query you've inserted. it gets the latest id of the table. It may be a row another query has inserted. Only use this function if you access Database in one connection.

If you want to get the id of the query you've inserted, try to select the row with an unique value you've inserted with that query. Ex : finding the user id of a user with his email address.

SELECT id from users where emailaddress='me@johndoe.com' LIMIT 1

More details here : https://www.php.net/manual/en/function.mysql-insert-id.php

MySQL LAST_INSERT_ID Function By Practical Examples, When you insert a row into the table without specifying a value for the id Finally , use the LAST_INSERT_ID function to get the last automatically inserted value:. It generates auto-incrementing values in table by defining seed and increment values, works much like SEQUENCE object in SQL Server and Oracle. However, IDENTITY property is table dependent and SEQUENCE object works independently from the table. Often we require to get last identity inserted value in SQL server. Multiple functions and methods

MySQL Reset Auto Increment Values, Notice that the value must be greater than or equal to the current maximum value of the auto-increment column. Let's delete the last record in the tmp table with id� The last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

LAST_INSERT_ID, Last inserted auto_increment value. For example, after inserting a row that generates an AUTO_INCREMENT value, you can get the value like this: SELECT � Teams. Q&A for Work. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.

PHP MySQL Get Last Inserted ID, If we perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field , we can get the ID of the last inserted/updated record immediately. In the table� Using the MySQL-specific method call isn't portable, and issuing a SELECT to get the AUTO_INCREMENT key's value requires another round-trip to the database, which isn't as efficient as possible. The following code snippets demonstrate the three different ways to retrieve AUTO_INCREMENT values.

Comments
  • possible duplicate of Get Auto Increment value with MySQL query
  • @RiaD it told me column name not found
  • @Gwen instead of id use the actual column name of your auto increment column in the table.