Find solution
how to insert multiple rows from MySQL with checkbox?

How do I get all the auto-incremented IDs for multiple row inserts using a single insert statement?

  • I posted the same question on http://stackoverflow.com also. I have a table named settings(which stores user settings) and I have  to insert multiple settings for each user. Initially, I had executed a  separate insert statement for each setting, but having felt this wasn't a  particularly good way to do it, I thought of inserting multiple rows by  the same insert statement. My only problem is that I want the  auto_incremented IDs of each of the newly inserted rows. I've read answers that say this isn't possible/scalable etc, but I  feel that I have hit upon the solution. I want feedback whether my way  is correct or not and hence this question. What I've done is simple. After inserting the multiple rows, I call  last_insert_id() to get the ID of the first row of the simultaneously  inserted rows. I already have the count of the number of rows inserted,  so I simply create a new array and populate it with IDs starting at  last_insert_id() and ending at last_insert_id()+n-1 (where n is the  number of rows inserted). I feel this will work because of the following reasons: 1.) MYSQL documentation states that last_insert_id() is connection  dependent and if another client/connection inserts new records, then  that won't affect other client's last_insert_id(). 2.) I feel that as the insert is being done by a single SQL  statement, the whole insertion should be treated as a single  transaction. If that is true, then ACID rules should apply and the  auto_incremented values should be sequential. I'm not sure about this  one. Those are my reasons why I feel the logic should work. So my question  is, will the above logic work for ALL conditions? Can I rely on it to  work correctly in all situations? I know it is working for me currently.

  • Answer:

    You can just use mysql_insert_id(). It will return the first ID of the bunch that was inserted into the database. Then just increment that by 1 for every row you have inserted.

Geoffrey Reemer at Quora Visit the source

Was this solution helpful to you?

Other answers

I wouldn't trust adding an offset to the mysql_insert_id() because multiple connections could be inserting auto-incremented rows at the same time.  There's no guarantee that the IDs will be an unbroken sequence. Instead, I'd look at the design for your table and question the need for auto-incremented rows for settings.  You could simply store the user_id, key, and value for each setting.  Then you could REPLACE INTO any time you're updating settings for that user, which would insert new setting keys and update existing ones. Your keys would always be predictable and you'd never need to query for them after an insert. The table could look like: user_id | setting | value 1 lang en 1 tz Pacific/Los_Angeles 2 lang de 2 tz Europe/Berlin 3 lang pt 3 tz Europe/Lisbon You could update settings like: REPLACE INTO settings (user_id, setting, value) VALUES (3, 'lang', 'pt'), (3, 'tz', 'Europe/Lisbon'); Your primary key would be a compound of (user_id, setting).  You could include an index for (user_id) to make lookups quick for all the settings of a particular user.

Jeff Standen

Query the last_insert_id(). This returns the first id generated by your batch of rows.Then assume the next N consecutive id values are used by your batch of rows. This is a reliable method.If you read the source code for MySQL’s JDBC driver, it works exactly this way when you prepare a statement with the Statement.RETURN_GENERATED_KEYS option.Tip: use the count of “rows affected” for N, not the original number of rows you tried to insert, because if you had use INSERT IGNORE or INSERT…ON DUPLICATE KEY UPDATE, your insert may have created fewer rows than you sent.One case where this does not work is if the data you try to insert includes a mix of some rows that explicitly set their own id values, and also some rows that use 0 or NULL to cause new id values to be generated, this makes the estimate of inserted id values inaccurate. INSERT INTO MyTable (id, name, house) VALUES (NULL, ‘Harry’, ‘Gryffindor’), (1234, ‘Cedric’, ‘Hufflepuff’), (NULL, ‘Luna’, ‘Ravenclaw’); Don’t do this! When the client assumes that N rows got the id values following the first generated id, it returns the wrong values for rows that did specify an id.You should either give specific values for all rows, or else let all rows generate new id values in a given INSERT statement.

Bill Karwin

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.