-1

Suppose a column of type unsigned int. Since 4294967295 is the largest unsigned integer, this query will fail with a overflow error:

update mytable set intcolumn = intcolumn + 1;

How could I achieve that instead of overflowing, 4294967295 + 1 will become 0 (zero) ?

More generally, by adding any integer, I would just like the integer to wrap around zero instead of overflowing.

I just found out that before mysql 5.5.5, integer silently wrapped around. Only 5.5.5 and above throw an error.

Lorenz Meyer
  • 19,166
  • 22
  • 75
  • 121
  • 2
    `update mytable set intcolumn = CASE WHEN intcolumn = 4294967295 THEN 0 ELSE intcolumn + 1 END;` Anyway why do you reinvent autoincrement? – Lukasz Szozda Dec 17 '15 at 14:50
  • That's an idea, but it is not general enough. What if I would add an arbitrary integer ? – Lorenz Meyer Dec 17 '15 at 14:52
  • Then it will be incremented by 1. The point is reinventing SEQUENCE/IDENTITY/AUTO_INCREMENT causes more problems than solves. – Lukasz Szozda Dec 17 '15 at 14:52
  • And it is not for autoincrement at all. If it were, wrapping around of zero would result in inconsistent references. – Lorenz Meyer Dec 17 '15 at 14:56
  • Struggling to wrap my head around why this would be necessary. Since you're adding any integer at all to it, you might look into a `TRIGGER` to handle this. Also, if you added more than 1 to the max value would you always want it to be 0 or would you expect the new value to be 0 + the amount greater than 1 you were adding? – Michael McGriff Dec 17 '15 at 15:18

1 Answers1

1

update mytable set intcolumn CASE intcolumn WHEN < 4294967295 then intcolumn + 1 WHEN > 4294967295 then 0

Yehuda Schwartz
  • 3,378
  • 3
  • 29
  • 38
  • This only answers the question if I add 1. It does not work for other values to be added. – Lorenz Meyer Dec 17 '15 at 15:13
  • 1
    same idea `update mytable set intcolumn CASE intcolumn WHEN < 4294967295 - valuetoadd then intcolumn + valuetoadd WHEN > 4294967295 - valuetoadd then 0` – Yehuda Schwartz Dec 17 '15 at 21:45