17

I have a mysql database in which i am using auto_increment(integer), can you tell me till what integer it can incremented. How we can increase the limit of auto_increment?

Eka
  • 14,170
  • 38
  • 128
  • 212

2 Answers2

50

The limit of an auto_increment column is the size of the column:

Use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255.

The limits of the integer types are:

TINYINT            - 127
UNSIGNED TINYINT   - 255
SMALLINT           - 32767
UNSIGNED SMALLINT  - 65535
MEDIUMINT          - 8388607
UNSIGNED MEDIUMINT - 16777215
INT                - 2147483647
UNSIGNED INT       - 4294967295
BIGINT             - 9223372036854775807
UNSIGNED BIGINT    - 18446744073709551615
Brendan Long
  • 53,280
  • 21
  • 146
  • 188
6

Integer can go as high as 2147483647. If unsigned it can be 4294967295.

See this chart for all of the integer values.

John Conde
  • 217,595
  • 99
  • 455
  • 496
  • can we increase the limit by using "BIGINT (9223372036854775807)"? – Eka Jun 29 '12 at 15:23
  • 6
    If you do one billion increments per second, you have enough in a BIGINT for 292 years of updates. If you have a three node galera cluster, that number drops to a measly 97 years. – opentokix Jun 15 '16 at 08:04
  • @opentokix just out of curiosity, why are you dividing it for the number of nodes in the cluster? – AldoB Feb 12 '18 at 14:10
  • @AldoB When you use a galera mariadb cluster it will increment by the number of nodes. nodeA will get 1 4 nodeB will get 2 5 and nodeB will get 3 6 and so on when you do "inc" – opentokix Feb 13 '18 at 15:24