Set the MySQL primary keys auto increment to be unlimited (or incredibly huge)?

MySQLMySQLi Database

You can use BIGINT but this is not unlimited but you can use large number of primary keys auto increment using it. The syntax is as follows −

yourColumnName BIGINT NOT NULL AUTO_INCREMENT;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table LargeAutoIncrement
   -> (
   -> Id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY
   -> );
Query OK, 0 rows affected (0.78 sec)

Now in this table you can store large number like 9223372036854775807 i.e. for primary key auto increment.

Let us insert record from 9223372036854775805 in the table using insert command. The query is as follows. We will only insert the first value and rest as blank since auto increment will automatically insert values −

mysql> insert into LargeAutoIncrement values(9223372036854775805);
Query OK, 1 row affected (0.19 sec)
mysql> insert into LargeAutoIncrement values();
Query OK, 1 row affected (0.81 sec)
mysql> insert into LargeAutoIncrement values();
Query OK, 1 row affected (0.16 sec)

Now you can display all records from the table using select statement. The query is as follows −

mysql> select *from LargeAutoIncrement;

The following is the output with auto increment −

+---------------------+
| Id                  |
+---------------------+
| 9223372036854775805 |
| 9223372036854775806 |
| 9223372036854775807 |
+---------------------+
3 rows in set (0.00 sec)
raja
Published on 27-Feb-2019 11:38:05
Advertisements