
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
Alter a MySQL column to be AUTO_INCREMENT?
Let’s say we have a table and now there is a requirement to add AUTO_INCREMENT on column name. For that, use the MODIFY command.
Here, we will create a demo table first.
mysql> create table AddingAutoIncrement -> ( -> Id int, -> Name varchar(200), -> Primary key(Id) -> ); Query OK, 0 rows affected (0.47 sec)
We have created a table above and now let us alter the table to add AUTO_INCREMENT on column name ‘Id’. The syntax is as follows −
alter table yourTableNamet modify yourColumnName int AUTO_INCREMENT;
Apply the above syntax to add AUTO_INCREMENT. The query is as follows.
mysql> ALTER table AddingAutoIncrement modify Id int AUTO_INCREMENT; Query OK, 0 rows affected (1.19 sec) Records: 0 Duplicates: 0 Warnings: 0
Above, we have added “AUTO_INCREMENT” on column name ‘Id’. Let us check the same with the help of DESC command. The query is as follows −
mysql> desc AddingAutoIncrement;
Sample Output.
+-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | Id | int(11) | NO | PRI | NULL | auto_increment | | Name | varchar(200) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
Look at the output above and column name ‘Extra’. In the column name ‘Extra’, there is a keyword auto_increment. This itself says that we have added the keyword successfully.
Now, I am going to insert records and check whether the row is increment by one or not. The query is as follows −
mysql> insert into AddingAutoIncrement(Name) values('John'); Query OK, 1 row affected (0.20 sec) mysql> insert into AddingAutoIncrement(Name) values('Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into AddingAutoIncrement(Name) values('Bob'); Query OK, 1 row affected (0.10 sec)
Display all records with the help of SELECT statement.
mysql> select *from AddingAutoIncrement;
The following is the output.
+----+-------+ | Id | Name | +----+-------+ | 1 | John | | 2 | Smith | | 3 | Bob | +----+-------+ 3 rows in set (0.00 sec)
As you can see in the above output, row is incremented by 1.
- Related Articles
- Set auto increment initial value for MySQL table using ALTER command
- MySQL query to set my auto increment column ( id ) to zero or reset the value of auto increment field?
- How to handle fragmentation of auto increment ID column in MySQL?
- How to create a table with auto-increment column in MySQL using JDBC?
- How to add auto-increment to column in MySQL database using PhpMyAdmin?
- Set MySQL int column to auto increment by 1 beginning at 10000?
- Passing NULL to MySQL for auto increment?
- Set the MySQL primary keys auto increment to be unlimited (or incredibly huge)?
- How to change auto increment number in MySQL?
- Change the Auto Increment counter in MySQL?
- How to make MySQL table primary key auto increment?
- Add a column to a MySQL table which is the result of concatenation of text and value from another auto increment column?
- Set custom Auto Increment with ZEROFILL in MySQL
- How to get the next auto-increment id in MySQL?
- How to set initial value and auto increment in MySQL?
