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: