Note that LAST_INSERT_ID() is tied to the session, so even if multiple connections are inserting into the same table, each with get its own id. It is possible! 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. Ignoring existing rows. MySQL INSERT statement. This will return the first inserted id when doing multiple or bulk INSERT. This returns the first id generated by your batch of rows. For information on LAST_INSERT_ID(), which can be used within an SQL statement, see Information Functions. For example: All you need to do is call the PDO lastInsertId() method. As stated initially, the INSERT command is a built-in MySQL statement which … INSERT Single Row 3. In the table "MyGuests", the "id" column is an AUTO_INCREMENT field: Get ID of The Last Inserted Record. > b) do the following as a way to give users access to the list of last ID's > inserted: > mysql> SELECT ID FROM INFORMATION_SCHEMA.SESSION_LAST_INSERT_ID; > -> 21124195 > -> 21124196 > -> 21124198 > mysql> SELECT ID FROM INFORMATION_SCHEMA.SESSION_LAST_INSERT_ID LIMIT 1; > -> 21124195 I vote for something along this line of thinking (i.e. This is a reliable method. To select last 10 rows from MySQL, we can use a subquery with SELECT statement and Limit concept. Let’s now read and understand each of the section one by one. Next we need to perform a simple addition subtraction operation with the number of rows affected by the last INSERT statement. For a multiple-row INSERT into the detail table, LAST_INSERT_ID() returns a constant value (the value from the master record), even if the detail table itself contains an AUTO_INCREMENT column. When importing large datasets, it may be preferable under certain circumstances to skip rows that would usually cause the query to fail due to a column restraint e.g. duplicate primary keys. I thought to get MAX(id) before selection, then get last_insert_id after selection and using count of inserted rows assign id to each. For information on obtaining the auto-incremented value when using Connector/J, see Retrieving AUTO_INCREMENT Column Values through JDBC. Creating a table. But I haven't found in documentation anything about order that rows in insert are processed, so I'm worried I won't be able to know what the ID's are. Typically, inserting 100 rows in a single batch insert this way is 10 times as fast as inserting them all individually. However, as of MySQL 4.1.2 and 5.0.1, LAST_INSERT_ID() changes from row to row for the INSERT … For information on mysql_insert_id(), the function you use from within the C API, see Section 7.39, “mysql_insert_id()”. INSERT Date Columns 4. For information on LAST_INSERT_ID(), which can be used within an SQL statement, see Information Functions. INSERT Multiple Rows. Then assume the next N consecutive id values are used by your batch of rows. INSERT Default Values 3. The following is an example. multiple rows and mysql_insert_id() Posted by: Adam Sachs Date: March 03, 2008 01:09AM Hey guys, I was wondering if it is possible to get all of the inserted id's of an auto increment column when doing multiple inserts at 1 time. Because mysql_insert_id() acts on the last performed query, be sure to call mysql_insert_id() immediately after the query that generates the value. Query the [code ]last_insert_id()[/code]. For information on obtaining the auto-incremented value when using Connector/J, see Retrieving AUTO_INCREMENT Column Values through JDBC. For information on mysql_insert_id(), the function you use from within the C API, see Section 7.38, “mysql_insert_id()”. Note : The value of the MySQL SQL function LAST_INSERT_ID() always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries.