Add an autoincrement column with a custom start value in MySQL

To add a new column to an already created table, use ALTER TABLE and ADD COLUMN. Use AUTO_INCREMENT to set auto increment custom value.

Let us first create a table −

mysql> create table DemoTable
   -> (
   -> StudentName varchar(20)
   -> );
Query OK, 0 rows affected (0.63 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('Robert');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values('Adam');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('Mike');
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-------------+
| StudentName |
+-------------+
| Robert      |
| Adam        |
| Mike        |
+-------------+
3 rows in set (0.00 sec)

Following is the query to add an autoincrement column with start value −

mysql> alter table DemoTable add column StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY,AUTO_INCREMENT=1000;
Query OK, 0 rows affected (1.83 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us check the table records once again −

mysql> select *from DemoTable;

This will produce the following output −

+-------------+-----------+
| StudentName | StudentId |
+-------------+-----------+
| Robert      |      1000 |
| Adam        |      1001 |
| Mike        |      1002 |
+-------------+-----------+
3 rows in set (0.00 sec)

AmitDiwan

Published on 13-Dec-2019 06:24:18

• Related Questions & Answers
• Add a temporary column with a value in MySQL?
• Increment column value ‘ADD’ with MySQL SET clause
• Selecting a value in custom order from another column in a MySQL table with a single query
• MySQL ORDER BY with custom field value
• Add user defined value to a column in a MySQL query?
• How to Reset MySQL AutoIncrement using a MAX value from another table?
• MySQL SELECT to sum a column value with previous value
• Create table query with manual AUTO_INCREMENT start value in MySQL?
• Order randomly in MySQL with a random value column?
• Add a new value to a column of data type enum in MySQL?
• Add to existing value in MySQL column using CONCAT function?
• Python - Add a new column with constant value to Pandas DataFrame
• Add a positive integer constraint to an integer column in MySQL?
• ALTER table by adding AUTOINCREMENT in MySQL?
• How to add a new column to an R data frame with largest value in each row?