How can I Insert many rows into a MySQL table and return the new IDs?

by

Old thread but just looked into this, so here goes: if you are using InnoDB on a recent version of MySQL, you can get the list of IDs using LAST_INSERT_ID() and ROW_COUNT().

InnoDB guarantees sequential numbers for AUTO INCREMENT when doing bulk inserts, provided innodb_autoinc_lock_mode is set to 0 (traditional) or 1 (consecutive).
Consequently you can get the first ID from LAST_INSERT_ID() and the last by adding ROW_COUNT()-1.

More Related Contents:

  1. MySQL – Get row number on select
  2. Delimiters in MySQL
  3. How to use GROUP_CONCAT in a CONCAT in MySQL
  4. MySQL query / clause execution order
  5. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  6. ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)
  7. MySQL SELECT WHERE datetime matches day (and not necessarily time)
  8. Operand Should Contain 1 Column – MySQL NOT IN
  9. Error Code: 1005. Can’t create table ‘…’ (errno: 150)
  10. What is the MySQL JDBC driver connection string?
  11. Error 1022 – Can’t write; duplicate key in table
  12. How to export SQL Server database to MySQL? [duplicate]
  13. Are nested transactions allowed in MySQL?
  14. SQL Query to get column values that correspond with MAX value of another column?
  15. MySQL : transaction within a stored procedure
  16. MySQL – How to search for exact word match using LIKE?
  17. Fetching UTF-8 text from MySQL in R returns “????”
  18. Implementing a hierarchical data structure in a database
  19. MySQL date format
  20. How to get current date & time in MySQL?
  21. MySQL – Persistent connection vs connection pooling
  22. ERROR: Loading local data is disabled – this must be enabled on both the client and server sides
  23. How do I split the output from mysqldump into smaller files?
  24. SELECT INTO Variable in MySQL DECLARE causes syntax error?
  25. MySQL Group By Hours
  26. RMySQL dbWriteTable with field.types
  27. How can I “select *” from a table in MySQL but omit certain columns?
  28. For homebrew mysql installs, where’s my.cnf?
  29. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?
  30. comparison of truncate vs delete in mysql/sqlserver [duplicate]

Leave a Comment