Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Set auto increment initial value for MySQL table using ALTER command
To set auto increment initial value for MySQL table, use ALTER command. The first step would be
alter table yourTableName modify yourColumnName int NOT NULL AUTO_INCREMENT PRIMARY KEY,add index(yourColumnName);
The second step is as follows
alter table yourTableName AUTO_INCREMENT=yourStartingValue;
To understand the above syntax, let us create a table. The query to create a table is as follows
mysql> create table setAutoIncrementDemo -> ( -> UserId int, -> UserName varchar(20) -> ); Query OK, 0 rows affected (0.75 sec)
Now implement the above two steps to set auto increment initial value for MySQL table.
Step 1 -The query is as follows
mysql> alter table setAutoIncrementDemo modify UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY,add index(UserId); Query OK, 0 rows affected (1.51 sec) Records: 0 Duplicates: 0 Warnings: 0
Step 2 -The query is as follows
mysql> alter table setAutoIncrementDemo AUTO_INCREMENT=1000; Query OK, 0 rows affected (0.34 sec) Records: 0 Duplicates: 0 Warnings: 0
Insert some records in the table using insert command.
The query is as follows
mysql> INSERT INTO setAutoIncrementDemo(UserName) values('John');
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO setAutoIncrementDemo(UserName) values('Carol');
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO setAutoIncrementDemo(UserName) values('Sam');
Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement.
The query is as follows
mysql> select *from setAutoIncrementDemo;
The following is the output
+--------+----------+ | UserId | UserName | +--------+----------+ | 1000 | John | | 1001 | Carol | | 1002 | Sam | +--------+----------+ 3 rows in set (0.00 sec)
Updated on: 30-Jul-2019
509 Views
Advertisements