Generating and Using Sequences - MySQL - page 470

Statement s = conn . createStatement ();
s . executeUpdate (
"INSERT INTO booksales (title,copies)"
+ "VALUES('The Greater Trumps',LAST_INSERT_ID(1))"
+ "ON DUPLICATE KEY UPDATE copies = LAST_INSERT_ID(copies+1)" ,
Statement . RETURN_GENERATED_KEYS );
long count ;
ResultSet rs = s . getGeneratedKeys ();
if ( rs . next ())
{
count = rs . getLong ( 1 );
}
else
{
throw new SQLException ( "getGeneratedKeys() produced no value" );
}
rs . close ();
s . close ();
Use of LAST_INSERT_ID( expr ) for sequence generation has certain other properties that
differ from true AUTO_INCREMENT sequences:
• AUTO_INCREMENT values increment by one each time, whereas values generated by
LAST_INSERT_ID( expr ) can be any nonnegative value you want. For example, to
produce the sequence 10, 20, 30, …, increment the count by 10 each time. You need
not even increment the counter by the same value each time. If you sell a dozen
copies of a book rather than a single copy, update its sales count as follows:
INSERT INTO booksales ( title , copies )
VALUES ( 'The Greater Trumps' , LAST_INSERT_ID ( 12 ))
ON DUPLICATE KEY UPDATE copies = LAST_INSERT_ID ( copies + 12 );
• To reset a counter, simply set it to the desired value. Suppose that you want to report
to book buyers the sales for the current month, rather than the total sales (for ex‐
ample, to display messages like “you're the n th buyer this month”). To clear the
counters to zero at the beginning of each month, use this statement:
UPDATE booksales SET copies = 0 ;
• One property that's not so desirable is that the value generated by LAST_IN
SERT_ID( expr ) is not uniformly available via client-side retrieval methods under
all circumstances. You can get it after UPDATE or INSERT statements, but not for SET
statements. If you generate a value as follows (in Ruby), the client-side value re‐
turned by insert_id is 0, not 48:
dbh . do ( "SET @x = LAST_INSERT_ID(48)" )
seq = dbh . func ( :insert_id )
To get the value in this case, ask the server for it:
seq = dbh . select_one ( "SELECT LAST_INSERT_ID()" ) [ 0 ]