Database Reference
In-Depth Information
To retrieve the sales count (for example, to display a message to customers such as “you
just purchased copy n of this topic”), issue a SELECT query for the same book title:
SELECT copies FROM booksales WHERE title = 'The Greater Trumps' ;
Unfortunately, this is not quite correct. Suppose that between the times when you update
and retrieve the count, some other person buys a copy of the topic (and thus increments
the copies value). Then the SELECT statement won't actually produce the value you
incremented the sales count to, but rather its most recent value. In other words, other
clients can affect the value before you have time to retrieve it. This is similar to the
problem discussed in Recipe 13.4 that can occur if you try to retrieve the most recent
AUTO_INCREMENT value from a column by invoking MAX( col_name ) rather than LAST_IN
SERT_ID() .
There are ways around this (such as by grouping the two statements as a transaction or
by locking the table), but MySQL provides a simpler solution based on LAST_IN
SERT_ID() . If you call LAST_INSERT_ID() with an expression argument, MySQL treats
it like an AUTO_INCREMENT value. To use this feature with the booksales table, modify
the count-incrementing statement slightly:
INSERT INTO booksales ( title , copies )
VALUES ( 'The Greater Trumps' , LAST_INSERT_ID ( 1 ))
ON DUPLICATE KEY UPDATE copies = LAST_INSERT_ID ( copies + 1 );
The statement uses the LAST_INSERT_ID( expr ) construct both to initialize and to in‐
crement the count. MySQL treats the expression argument like an AUTO_INCREMENT
value, so that you can invoke LAST_INSERT_ID() later with no argument to retrieve the
value:
SELECT LAST_INSERT_ID ();
By setting and retrieving the copies column this way, you always get back the value you
set it to, even if some other client updated it in the meantime. If you issue the INSERT
statement from within an API that provides a mechanism for fetching the most recent
AUTO_INCREMENT value directly, you need not even issue the SELECT query. For example,
using Connector/Python, update a count and get the new value using the lastrowid
attribute:
cursor = conn . cursor ()
cursor . execute ( '''
INSERT INTO booksales (title,copies)
VALUES('The Greater Trumps',LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE copies = LAST_INSERT_ID(copies+1)
''' )
count = cursor . lastrowid
cursor . close ()
conn . commit ()
In Java, the operation looks like this:
Search WWH ::




Custom Search