What is the limit of auto_increment (integer) in MySQL?

MySQLMySQLi Database

The limit of auto_increment integer depends on column data type. Displayed as follows:

The data type TINYINT range is 127
The data type UNSIGNED TINYINT range is 255
The data type SMALLINT range is 32767
The data type UNSIGNED SMALLINT range is 65535
The data type MEDIUMINT range is 8388607
The data type UNSIGNED MEDIUMINT range is 16777215
The data type INT range is 2147483647
The data type UNSIGNED INT range is 4294967295
The data type BIGINT range is 9223372036854775807
The data type UNSIGNED BIGINT range is 18446744073709551615

Let us take an example of TINYINT. If you will give beyond 127, then MySQL will give an error.

Let us first create a table. The query to create a table is as follows. Here, the ID is auto_incerement:

mysql> create table LimitOfAutoIncrement
   -> (
   -> Id TINYINT NOT NULL AUTO_INCREMENT,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.68 sec)

Insert only 127 records which is limit of auto_increment in TINYINT. The query is as follows:

mysql> insert into LimitOfAutoIncrement values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),();
Query OK, 127 rows affected (0.20 sec)
Records: 127 Duplicates: 0 Warnings: 0

Display all records from the table using select statement. The query is as follows:

mysql> select *from LimitOfAutoIncrement;

The following is the output:

+-----+
| Id  |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
|  11 |
|  12 |
|  13 |
|  14 |
|  15 |
|  16 |
|  17 |
|  18 |
|  19 |
|  20 |
|  21 |
|  22 |
|  23 |
|  24 |
|  25 |
|  26 |
|  27 |
|  28 |
|  29 |
|  30 |
|  31 |
|  32 |
|  33 |
|  34 |
|  35 |
|  36 |
|  37 |
|  38 |
|  39 |
|  40 |
|  41 |
|  42 |
|  43 |
|  44 |
|  45 |
|  46 |
|  47 |
|  48 |
|  49 |
|  50 |
|  51 |
|  52 |
|  53 |
|  54 |
|  55 |
|  56 |
|  57 |
|  58 |
|  59 |
|  60 |
|  61 |
|  62 |
|  63 |
|  64 |
|  65 |
|  66 |
|  67 |
|  68 |
|  69 |
|  70 |
|  71 |
|  72 |
|  73 |
|  74 |
|  75 |
|  76 |
|  77 |
|  78 |
|  79 |
|  80 |
|  81 |
|  82 |
|  83 |
|  84 |
|  85 |
|  86 |
|  87 |
|  88 |
|  89 |
|  90 |
|  91 |
|  92 |
|  93 |
|  94 |
|  95 |
|  96 |
|  97 |
|  98 |
|  99 |
| 100 |
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
| 106 |
| 107 |
| 108 |
| 109 |
| 110 |
| 111 |
| 112 |
| 113 |
| 114 |
| 115 |
| 116 |
| 117 |
| 118 |
| 119 |
| 120 |
| 121 |
| 122 |
| 123 |
| 124 |
| 125 |
| 126 |
| 127 |
+-----+
127 rows in set (0.00 sec)

Now you cannot insert record for auto_increment. If you will try then an error will generate:

mysql> insert into LimitOfAutoIncrement values();
ERROR 1062 (23000): Duplicate entry '127' for key 'PRIMARY'
raja
Published on 01-Feb-2019 12:09:38
Advertisements