How to Get the Value of Autoincrement of Last Row at the Insert

How to get the value of autoincrement of last row at the insert

Use SCOPE_IDENTITY:

-- do insert

SELECT SCOPE_IDENTITY();

Which will give you:

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.

How to get id(Auto increment) of last row inserted

mysql_insert_id will return the ID of the last row inserted in the transaction. If you create a new connection to MySQL for each page and don't use multiple threads within a page, mysql_insert_id should do what you want even in the presence of multiple INSERTs happening on different connections, provided you're not doing anything tricky within your PHP page.

MySQL: Get last inserted primary key without auto_increment

The fact

There's no equivalent to LAST_INSERT_ID() returning a non integer value.

One can simply

1. The easy approach

Add an integer column which can either be auto incremented or non auto incremented.

To have it auto incremented correctly one has at least to implement an algorithm in MySQL itself or with a language of their choice to fill the existing records with the new IDs.

2. The more complex approach

https://stackoverflow.com/a/53481729/2323764 (@kellymandem)

Add a second table managing the ID and triggered by the origin table without IDs.

One cannot

I found this very promising Q/A.

Is there a way to get last inserted id of a NON - auto incremented column in MySQL?

It's mentioned there to use LAST_INSERT_ID() in the INSERT statement already.

But

INSERT INTO `table` ( `non_integer_column` ) VALUES ( LAST_INSERT_ID( 42 ) );
SELECT LAST_INSERT_ID( );
-> 42

INSERT INTO `table` ( `non_integer_column` ) VALUES ( LAST_INSERT_ID( 'a0b1c2d3e4f5' ) );
SELECT LAST_INSERT_ID( );
-> 0

Non integer values will be ignored.

Get the new record primary key ID from MySQL insert query?

You need to use the LAST_INSERT_ID() function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id

Eg:

INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...);
SELECT LAST_INSERT_ID();

This will get you back the PRIMARY KEY value of the last row that you inserted:

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.

So the value returned by LAST_INSERT_ID() is per user and is unaffected by other queries that might be running on the server from other users.

How can I SELECT the last row that NO ID increment

You've edited question so, here's update

SELECT MAX(cate_id) AS last_cate_id FROM table;

or you can get next ID by:

SELECT MAX(cate_id)+1 AS next_cate_id FROM table;

Without transactions this is very vulnerable for inserting same cate_id!

If you cant use them, for example because of MyISAM, you could insert with select.

INSERT INTO table
(cate_id, task_id ..)
VALUES
( (SELECT MAX(cate_id)+1 AS next_cate_id FROM table), 1 )

Autoincrement does not increment sequentially if last row was deleted

This is from MYSQL developer comment. See More https://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

In order to reset the auto_increment, in a situation where some of the
most recently added rows were deleted, use:

ALTER TABLE your_table_name AUTO_INCREMENT=1234  //this is a demo number

Then future insertions will be numbered from 1234 again (unless you still had rows numbered greater than 1234, and then the future insertions will start from the greatest number + 1 ).

Get last inserted auto increment id in mysql

Try using an alias

rs = st.executeQuery("select last_insert_id() as last_id from schedule");
lastid = rs.getString("last_id");

Is there a way to get last inserted id of a NON - auto incremented column in MySQL?

you can easily do that using the same LAST_INSERT_ID().

INSERT INTO thetable (id, value)
VALUES (LAST_INSERT_ID(126), 'some data');

SELECT LAST_INSERT_ID();  -- returns 126

Get the last primary key from table with no auto increment

Based on your statement 'i search for the last value inserted (highest value) of the primary key', I presume that you're currently doing something like this to get the maximum existing ID:

SELECT MAX(id_column) + 1 FROM my_table

If you have an empty table, this will of course return NULL. In that case, just handle the NULL using IFNULL to return 0 if there is no maximum value:

SELECT IFNULL(MAX(id_column), 0) + 1 FROM my_table

This will output 1 as the next identifier if the table has no rows.

---

Related Topics