Getting the last IDs

Databases
,
1

I know the way to get the id from the last query(mysql_insert_id)…but what if 2 or more rows have been inserted in the last query…is it possible to get all the generated IDs?

2 
SELECT id
  FROM daTable
 WHERE naturalkey IN (value, value, value...)

auto_increment ids are called surrogate keys for a reason

if your table doesn’t have a natural key, i.e. a column whose values are always distinct, then the answer is you’re screwed

3

I do not understand what exactly this query tries to accomplish and what must be placed inside the parentheses(where VALUE is located)?

4

this query retrieves the ids of all the rows whose naturalkey column contains one of the values listed

when “2 or more rows have been inserted in the last query” then simply grab the values from that insert statement

everything depends, of course, on the table actually having a natural key

otherwise, you’re screwed

5

If I understand correctly the notion of a natural key my table does not have one.Only a primary key(surrogate) that is auto-incremented.

In such a table cannot think of a natural key…you could argue that the service name could be that but there maybe the(very possible)case where two business_users offer the exact same service.

6

no, i couldn’t – because so far you have not shared the design of your table

7

you are right…here it is:

CREATE TABLE `services_list` (
  `serviceID` int(11) NOT NULL AUTO_INCREMENT,
  `price` decimal(5,2) DEFAULT NULL,
  `servicename` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `duration` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`serviceID`)
) ENGINE=InnoDB AUTO_INCREMENT=72 DEFAULT CHARSET=latin1 COMMENT='it lists the possible services'
8

make servicename NOT NULL and UNIQUE

vwalah! :wink:

9

and if two business owners can have the same service name, but possibly different prices and/or durations, then your natural UNIQUE key will be a composite key consisting of two columns – some business identifier plus the servicename

10

I want to say two things:

  1. The possibility of two business owners having the same service name,price and duration is very likely.At least here in Greece.in such case is hair salons.
  • As a result your idea having a composite key that includes service name AND a business identifier(e-mail) is correct.
11

It’s easy - just get one last_insert_id, which for multi-row inserts represents the id of the first inserted row. Then just increment the value by one for each added row (apart from the first one) and you have the id of that row. In other words, you don’t need to get all those ids because you can calculate them from the first id. The inserted auto-increment ids done in a single statement are guaranteed to be sequential for InnoDB tables in the default AUTO_INCREMENT Lock Mode - see this. So unless you have a non-standard database configuration you will be safe. I don’t know how it behaves for MyISAM but I suppose it should be even better because MyISAM locks the whole table for inserts, anyway, so there should never be a problem of interleaved sequences.

Another option: change your approach and execute single-row inserts and get the id after each one.

2 Likes
12

I tried this and it worked beautifully…thanks.

13

A small tip: if you use multiple inserts into innodb tables wrap the whole procedure in a transaction - this will speed up the inserts a lot and make your data consistent in case of an error.

14

good thought

15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.