3

For the sake of brevity, let's assume we have a numbers table with 2 columns: id & number:

CREATE TABLE numbers(
   id INT NOT NULL AUTO_INCREMENT,
   NUMBER INT NOT NULL,
   PRIMARY KEY ( id )
);

I want the number column to auto-increment, but restart from 1 after hitting 100, satisfying the following requirements:

  1. If there are no records yet, set number to 1.
  2. If there are already records in the table:
    1. If the last record's number is less than 100, set number to that + 1.
    2. If the last record's number is 100 or more, set number to 1.

Here's what I have so far:

INSERT INTO `numbers` (`number`)
VALUES (
    IF(
        IFNULL(
            (SELECT `number`
            FROM `numbers` as n
            ORDER BY `id` DESC
            LIMIT 1),
            0
        ) > 99,
        1,
        IFNULL(
            (SELECT `number`
            FROM `numbers` as n
            ORDER BY `id` DESC
            LIMIT 1),
            0
        ) + 1
    )
)

This works, but uses two identical SELECT queries.

Is there any way to remove the duplicate SELECT?

P.S. I want to do all this within a query. No triggers or stored procedures.

Joseph Silber
  • 214,931
  • 59
  • 362
  • 292

3 Answers3

3

Try to use modulo % operator

INSERT INTO `numbers` (`number`)
VALUES (
          IFNULL(
            (SELECT `number`
            FROM `numbers` as n
            ORDER BY `id` DESC
            LIMIT 1),
            0
        ) % 100 + 1
    )
valex
  • 23,966
  • 7
  • 43
  • 60
  • @JosephSilber In this case `% 100`. [SQLFiddle demo](http://sqlfiddle.com/#!2/d41d8/28822). I've fixed the answer. – valex Jan 10 '14 at 06:25
  • Exactly. BTW, what about race conditions? Is table locking (MyISAM) the only option here? – Joseph Silber Jan 10 '14 at 06:26
  • `If the last record's number is 100 or more, set number to 1. `, `or more` will break the data. But anyway, if you insert data only with this query, then it's fine. – xdazz Jan 10 '14 at 06:32
  • It should never be "or more" :) Are you saying that even if two of these queries run concurrently, I'll still always get consistent data? Is it not possible for the second query's SELECT to run after the first one's, but before the first query's INSERT runs? – Joseph Silber Jan 10 '14 at 06:35
  • @JosephSilber Never mind if you start with empty table. Your requirements said `If there are already records in the table` make the miss leading. – xdazz Jan 10 '14 at 06:39
  • Not following you. Are addressing the race conditions, or the solution as a whole? 'cause the solution is rock solid; `IFNULL` will handle the case for when there are no records. – Joseph Silber Jan 10 '14 at 06:42
2

You could use a temp variable.

INSERT INTO `numbers`(`number`) VALUES (
  IF((@a := IFNULL((SELECT number FROM `numbers` AS n ORDER BY id DESC LIMIT 1), 0)) > 99, 1, @a + 1)
);
xdazz
  • 158,678
  • 38
  • 247
  • 274
1

I see. The modulo answer is nice. It feels 'brittle' to me as it would not handle the "100 or more" requirement as stated. It's fine if you change the requirement to "If the last record's number is exactly 100, set number to 1."

To me this is more intuitive:

INSERT INTO numbers (number)
SELECT 
  CASE 
    WHEN number >= 100 THEN 0
    ELSE COALESCE (number, 0)
  END + 1
FROM numbers
ORDER BY id DESC
LIMIT 1
mdahlman
  • 9,204
  • 4
  • 44
  • 72